SQL Compare fails to load a database
jr41mac
Posts: 2 New member
I am attempting to compare a development database containing modifications against an existing database in production.
Copies of the production database exists in several locations (the same schema model and version is used for mutiple country databases), and also as scripts on disk (which is what we lock down in TFS for source control).
SQL Compare consistently fails when loading the production database without an error message whatever location I use, database or script - the screen just "whites out". I have run DBCC CHECKDB on one of the databases which reported no issues. That was really an act of desperation and in a weird way I was hoping it would flag something.
It seems to fail when it is "Reading Columns". The databases contain some lengthy calculated columns (see below) and I wonder if this could be the cause.
It is not obvious if a SQL Compare log file already exists, but it would be very useful to locate the cause of the problem as most of my developments revolve around this database.
The SQL Compare being used is Version 11.5.7.1288 Professional (I upgraded to this version as I was having the same issue with the previous version I had installed).
Sample Calculated Column:
[hashbytes_md5] AS (case when [CurrentRecordYN]='Y' then CONVERT([varchar](50),hashbytes('md5',(((((((((((((((((((((((((((((((case when len(isnull([CatalogueProductPrice],''))<=(32) then isnull([CatalogueProductPrice],'') else CONVERT([varchar](50),hashbytes('md5',case when len([CatalogueProductPrice])<=(4000) then [CatalogueProductPrice] else left([CatalogueProductPrice],(4000)) end),(1)) end+'~')+case when len(isnull([Created],''))<=(32) then isnull([Created],'') else CONVERT([varchar](50),hashbytes('md5',case when len([Created])<=(4000) then [Created] else left([Created],(4000)) end),(1)) end)+'~')+isnull([From],''))+'~')+isnull([FromT],''))+'~')+case when len(isnull([InvoicePrice],''))<=(32) then isnull([InvoicePrice],'') else CONVERT([varchar](50),hashbytes('md5',case when len([InvoicePrice])<=(4000) then [InvoicePrice] else left([InvoicePrice],(4000)) end),(1)) end)+'~')+isnull([Moveable],''))+'~')+case when len(isnull([OfferedPrice],''))<=(32) then isnull([OfferedPrice],'') else CONVERT([varchar](50),hashbytes('md5',case when len([OfferedPrice])<=(4000) then [OfferedPrice] else left([OfferedPrice],(4000)) end),(1)) end)+'~')+case when len(isnull([Orderlines_ID],''))<=(32) then isnull([Orderlines_ID],'') else CONVERT([varchar](50),hashbytes('md5',case when len([Orderlines_ID])<=(4000) then [Orderlines_ID] else left([Orderlines_ID],(4000)) end),(1)) end)+'~')+case when len(isnull([Packages_ID],''))<=(32) then isnull([Packages_ID],'') else CONVERT([varchar](50),hashbytes('md5',case when len([Packages_ID])<=(4000) then [Packages_ID] else left([Packages_ID],(4000)) end),(1)) end)+'~')+case when len(isnull([ProductPrice],''))<=(32) then isnull([ProductPrice],'') else CONVERT([varchar](50),hashbytes('md5',case when len([ProductPrice])<=(4000) then [ProductPrice] else left([ProductPrice],(4000)) end),(1)) end)+'~')+case when len(isnull([Quantity],''))<=(32) then isnull([Quantity],'') else CONVERT([varchar](50),hashbytes('md5',case when len([Quantity])<=(4000) then [Quantity] else left([Quantity],(4000)) end),(1)) end)+'~')+case when len(isnull([SequenceNo],''))<=(32) then isnull([SequenceNo],'') else CONVERT([varchar](50),hashbytes('md5',case when len([SequenceNo])<=(4000) then [SequenceNo] else left([SequenceNo],(4000)) end),(1)) end)+'~')+case when len(isnull([Slots_ID],''))<=(32) then isnull([Slots_ID],'') else CONVERT([varchar](50),hashbytes('md5',case when len([Slots_ID])<=(4000) then [Slots_ID] else left([Slots_ID],(4000)) end),(1)) end)+'~')+isnull([Status],''))+'~')+case when len(isnull([TariffPrice],''))<=(32) then isnull([TariffPrice],'') else CONVERT([varchar](50),hashbytes('md5',case when len([TariffPrice])<=(4000) then [TariffPrice] else left([TariffPrice],(4000)) end),(1)) end)+'~')+isnull([Till],''))+'~')+isnull([TillT],'')),(1)) else '' end) PERSISTED
Copies of the production database exists in several locations (the same schema model and version is used for mutiple country databases), and also as scripts on disk (which is what we lock down in TFS for source control).
SQL Compare consistently fails when loading the production database without an error message whatever location I use, database or script - the screen just "whites out". I have run DBCC CHECKDB on one of the databases which reported no issues. That was really an act of desperation and in a weird way I was hoping it would flag something.
It seems to fail when it is "Reading Columns". The databases contain some lengthy calculated columns (see below) and I wonder if this could be the cause.
It is not obvious if a SQL Compare log file already exists, but it would be very useful to locate the cause of the problem as most of my developments revolve around this database.
The SQL Compare being used is Version 11.5.7.1288 Professional (I upgraded to this version as I was having the same issue with the previous version I had installed).
Sample Calculated Column:
[hashbytes_md5] AS (case when [CurrentRecordYN]='Y' then CONVERT([varchar](50),hashbytes('md5',(((((((((((((((((((((((((((((((case when len(isnull([CatalogueProductPrice],''))<=(32) then isnull([CatalogueProductPrice],'') else CONVERT([varchar](50),hashbytes('md5',case when len([CatalogueProductPrice])<=(4000) then [CatalogueProductPrice] else left([CatalogueProductPrice],(4000)) end),(1)) end+'~')+case when len(isnull([Created],''))<=(32) then isnull([Created],'') else CONVERT([varchar](50),hashbytes('md5',case when len([Created])<=(4000) then [Created] else left([Created],(4000)) end),(1)) end)+'~')+isnull([From],''))+'~')+isnull([FromT],''))+'~')+case when len(isnull([InvoicePrice],''))<=(32) then isnull([InvoicePrice],'') else CONVERT([varchar](50),hashbytes('md5',case when len([InvoicePrice])<=(4000) then [InvoicePrice] else left([InvoicePrice],(4000)) end),(1)) end)+'~')+isnull([Moveable],''))+'~')+case when len(isnull([OfferedPrice],''))<=(32) then isnull([OfferedPrice],'') else CONVERT([varchar](50),hashbytes('md5',case when len([OfferedPrice])<=(4000) then [OfferedPrice] else left([OfferedPrice],(4000)) end),(1)) end)+'~')+case when len(isnull([Orderlines_ID],''))<=(32) then isnull([Orderlines_ID],'') else CONVERT([varchar](50),hashbytes('md5',case when len([Orderlines_ID])<=(4000) then [Orderlines_ID] else left([Orderlines_ID],(4000)) end),(1)) end)+'~')+case when len(isnull([Packages_ID],''))<=(32) then isnull([Packages_ID],'') else CONVERT([varchar](50),hashbytes('md5',case when len([Packages_ID])<=(4000) then [Packages_ID] else left([Packages_ID],(4000)) end),(1)) end)+'~')+case when len(isnull([ProductPrice],''))<=(32) then isnull([ProductPrice],'') else CONVERT([varchar](50),hashbytes('md5',case when len([ProductPrice])<=(4000) then [ProductPrice] else left([ProductPrice],(4000)) end),(1)) end)+'~')+case when len(isnull([Quantity],''))<=(32) then isnull([Quantity],'') else CONVERT([varchar](50),hashbytes('md5',case when len([Quantity])<=(4000) then [Quantity] else left([Quantity],(4000)) end),(1)) end)+'~')+case when len(isnull([SequenceNo],''))<=(32) then isnull([SequenceNo],'') else CONVERT([varchar](50),hashbytes('md5',case when len([SequenceNo])<=(4000) then [SequenceNo] else left([SequenceNo],(4000)) end),(1)) end)+'~')+case when len(isnull([Slots_ID],''))<=(32) then isnull([Slots_ID],'') else CONVERT([varchar](50),hashbytes('md5',case when len([Slots_ID])<=(4000) then [Slots_ID] else left([Slots_ID],(4000)) end),(1)) end)+'~')+isnull([Status],''))+'~')+case when len(isnull([TariffPrice],''))<=(32) then isnull([TariffPrice],'') else CONVERT([varchar](50),hashbytes('md5',case when len([TariffPrice])<=(4000) then [TariffPrice] else left([TariffPrice],(4000)) end),(1)) end)+'~')+isnull([Till],''))+'~')+isnull([TillT],'')),(1)) else '' end) PERSISTED
John Rogerson
Comments
Thanks for your post!
I have logged a support ticket for you and will email you shortly.
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com