Why is SQL Compare flagging Extended Properties as out of sync?

OzzieOzzie Posts: 42 Bronze 5
I'm seeing an odd issue.

SQL Compare is flagging the lines below across 2 servers

EXEC sp_addextendedproperty N'MS_Description', N'This is a table of the Visit Status References.', 'SCHEMA', N'dbo', 'VIEW', N'vwVisitStatusReference', NULL, NULLGO

EXEC sp_addextendedproperty N'MS_Description', 'This is a table of the Visit Status References.', 'SCHEMA', N'dbo', 'VIEW', N'vwVisitStatusReference', NULL, NULLGO

because the first line has an N before 'This is a table of the Visit Status References.' to cast it as NVARCHAR?

Since this would appear to be generated by the tool, why would it cast the columns differently? It only happens on the first generated line - none of the other lines have the N prepended to them.

TIA,
Doug
Tagged:

Answers

  • Jessica RJessica R Posts: 1,031 Rose Gold 3

    Hi Doug,

    Thanks for your post! :)

    If the two statements are different, I think SQL Compare is correct to flag them as different, but to confirm, do you mean the 'N' in N'This is a table of the Visit Status References.' was added by SQL Compare from a previous compare and sync?

    What version of SQL Compare are you using?

    Thanks!

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • OzzieOzzie Posts: 42 Bronze 5

    Yes. Your product consistently flags the columns as different, even though the same script has been applied to both databases.

    We are comparing a SQL 2008R2 instance vs a SQL 2017 instance. The 2008 version is always prefixed with N, the 2017 version is not. Kinda doesn't make sense - since the meta data itself is NVARCHAR for both.

    The odder issue is that it is only for the table meta data, not the column meta data.

    I can give you examples and screen shots - it happens 100% of the time.

    Doug

  • Jessica RJessica R Posts: 1,031 Rose Gold 3

    Thanks Doug!

    Just to confirm, what version of SQL Compare are you using?

    Could you provide an example and screenshot so we can try to reproduce this locally?

    It may also be worth checking - how is the update script created? Is it via command line? Could you double check that the "Ignore extended properties" option isn't turned on?

    Thanks!

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • OzzieOzzie Posts: 42 Bronze 5
    Jessica,

    Oddly enough - debugging the SP which wrote the data - we were using VARCHAR for the description? Not sure why your product would prefix the N for SQL 2008 and not prefix the N for the SQL 2017 version - but the second I made the change on my end it compared correctly. Maybe SQL is storing it as SQLVARIANT?

    Anyway - resolved although odd from your products point of view,
    Doug
Sign In or Register to comment.