What are the challenges you face when working across database platforms? Take the survey
Options

sql 2008 filtered indexes not detected in sql compare 8

lirogerliroger Posts: 2
I'm using the sql compare version 8.2.1.2. I have an index created as follows:

CREATE NONCLUSTERED INDEX [IX_ServiceRequest_WebApp_NoSWF] ON [dbo].[ServiceRequest] ([WebAppName]) INCLUDE ([LastObjReadByAgent], [LastObjReadByEndUser], [ServiceRequestId], [StatusId]) where [WebAppName] <> 'swfweb'

The where clause in this index was not detected by the tool, so it was not included in the sync script.

Has this been reported? Will this be fixed soon?

Thanks,
Roger

Comments

  • Options
    Eddie DEddie D Posts: 1,789 Rose Gold 5
    Thank you for your post into the forum.

    Unfortunately I cannot reproduce the reported problem you are experiencing.

    Therefore we have created a support call for you. The call reference is F0033905.

    So we can try to reproduce the reported problem, can you please create SQL Compare Snapshot files of each database and send to the resulting .snp files to us via e-mail, support@red-gate.com? Please also include the call reference number in the subject field on the e-mail.

    To create the snapshot files ->File Menu ->Create Snapshot.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Options
    Jason HannasJason Hannas Posts: 41 Bronze 3
    I'm having the same issue as the OP. Steps to reproduce are:
    CREATE TABLE Test &#40;
         ColA int NOT NULL,
         ColB int NOT NULL,
         IsObsolete bit NOT NULL
        &#41; ;
    CREATE UNIQUE NONCLUSTERED INDEX ixTest_ColAColB_UN ON Test &#40;ColA, ColB&#41;
    	WHERE ColB IS NOT NULL AND IsObsolete = 0 ;
    GO
    

    The resulting Compare output shows:
    -- Columns

    CREATE TABLE [dbo].[Test]
    (
    [ColA] [int] NOT NULL,
    [ColB] [int] NOT NULL,
    [IsObsolete] [bit] NOT NULL
    )
    GO
    -- Constraints and Indexes

    CREATE UNIQUE NONCLUSTERED INDEX [ixTest_ColAColB_UN] ON [dbo].[Test] ([ColA], [ColB])
    GO

    This will have a major impact on synchronizing our dev and test environments. Please advise when a fix might be expected. If it helps any, SQL Doc catches the WHERE clause without a problem.

    Thanks.
  • Options
    Eddie DEddie D Posts: 1,789 Rose Gold 5
    Please accept my apologies Jason for the delay in repying back to you.

    I still cannot reproduce this error. Using your example:
    CREATE TABLE Test &#40; 
         ColA int NOT NULL, 
         ColB int NOT NULL, 
         IsObsolete bit NOT NULL 
        &#41; ; 
    CREATE UNIQUE NONCLUSTERED INDEX ixTest_ColAColB_UN ON Test &#40;ColA, ColB&#41; 
       WHERE ColB IS NOT NULL AND IsObsolete = 0 ; 
    GO
    

    I created two new test SQL 2008 databases. In database 1, I created the table using your code above. In database 2, I created the same table but did not create the index.

    The synchronization process generates the following script:
    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
    GO
    IF EXISTS &#40;SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID&#40;'tempdb..#tmpErrors'&#41;&#41; DROP TABLE #tmpErrors
    GO
    CREATE TABLE #tmpErrors &#40;Error int&#41;
    GO
    SET XACT_ABORT ON
    GO
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    GO
    BEGIN TRANSACTION
    GO
    PRINT N'Creating index &#91;ixTest_ColAColB_UN&#93; on &#91;dbo&#93;.&#91;Test&#93;'
    GO
    CREATE UNIQUE NONCLUSTERED INDEX &#91;ixTest_ColAColB_UN&#93; ON &#91;dbo&#93;.&#91;Test&#93; &#40;&#91;ColA&#93;, &#91;ColB&#93;&#41; WHERE &#40;&#91;ColB&#93; IS NOT NULL AND &#91;IsObsolete&#93;=&#40;0&#41;&#41;
    GO
    IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
    GO
    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
    GO
    IF EXISTS &#40;SELECT * FROM #tmpErrors&#41; ROLLBACK TRANSACTION
    GO
    IF @@TRANCOUNT&gt;0 BEGIN
    PRINT 'The database update succeeded'
    COMMIT TRANSACTION
    END
    ELSE PRINT 'The database update failed'
    GO
    DROP TABLE #tmpErrors
    GO
    

    However I did notice something in the comparison results. Did you scroll the the SQL Differences view from left to right. The SQL Differences will display
    CREATE UNIQUE NONCLUSTERED INDEX &#91;ixTest_ColAColB_UN&#93; ON &#91;dbo&#93;.&#91;Test&#93; &#40;&#91;ColA&#93;, &#91;ColB&#93;
    
    If you move the scroll bar at the bottom of the view, from left to write it reveals the remain syntax
    N&#93; ON &#91;dbo&#93;.&#91;Test&#93; &#40;&#91;ColA&#93;, &#91;ColB&#93;&#41; WHERE &#40;&#91;ColB&#93; IS NOT NULL AND &#91;IsObsolete&#93;=&#40;0&#41;&#41;
    
    .

    Can you please confirm if moving the scroll bar from left to right reveals the missing filtered part of the index?

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Options
    Jason HannasJason Hannas Posts: 41 Bronze 3
    You are correct, the synchronization script does show the full where clause for the index in my example. However, the difference display and the comparison routine is where the problem lies.

    First, create the table and filtered index in Database1 and do nothing in Database 2. Run your compare and you'll see the display will be incorrect (and not because of scrolling), however the sync script will be correct.

    Next, create only the table in Database2 and run another compare. You'll find the same results--display is incorrect, sync script is correct.

    Finally, create the index without the filter in Database2. Run a compare. You'll see that the comparison process fails at this point, as it will show those tables as identical. Therefore, a synchronization script will also be incorrect, as that table will be excluded.
  • Options
    Eddie DEddie D Posts: 1,789 Rose Gold 5
    Thank you for your reply.

    Unfortunately I am still unable to reproduce the reported fault symptoms.

    What version of SQL Compare are you using? If you have not already done so, can you please download the latest cumulative patch and upgrade your current installtion of SQL Compare V8, using this LINK. I am using the latest cumulative patch of SQL Compare.

    If you are still experiencing the reported issue after appying the latest patch, can you please send screen shots of what you are experiencing along with the SQL script generated from the synchronization wizard. A support call has been created, please send the screen shots and script to support@red-gate.com and include the following call reference number in the subject field of the e-mail: F0033905.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Options
    Jason HannasJason Hannas Posts: 41 Bronze 3
    I just upgraded using your link and confirmed that the problem is fixed with this update. Like an idiot, I did that before documenting the issue with screen shots, so if you think they'll be useful, I guess I could try to downgrade and reproduce.

    The version I was on that exhibited the problem is: 8.1.0.360. Checking for updates doesn't show any available, but I do see the OP was on 8.2 already.

    Let me know--if you'd like the screen shots, I'll downgrade and put it together for you. If not, just know that that version has a problem and the latest does not.

    Thanks,
    Jason
  • Options
    Eddie DEddie D Posts: 1,789 Rose Gold 5
    Thank you for your reply and excellent news that the cumulative patch has resolved your problem.

    The cumlative patch for both SQL Compare has not yet been added to the Check For Updates mechanism. However this is something the development team are looking into, but I do not have a confirmed date for when this will happen.

    Please do not downgrade just to take the screen shots previously requested. As the cumulative patch resolves this particular issue, we will request any future customers to upgrade.

    Thank you for bringing this problem to our attention and for providing us with example code to reproduce the problem.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
Sign In or Register to comment.