Why is SQL Compare flagging Extended Properties as out of sync?
Ozzie
Posts: 47 Bronze 5
in SQL Compare
I'm seeing an odd issue.
SQL Compare is flagging the lines below across 2 servers
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
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
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?
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
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?
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