Unable to compare SQL Server 2005 database

tonyvanriet

I'm unable to perform a compare between two SQL Server 2005 databases (specifically SQL version 9.00.4060) using SQL Compare 11.3 or 10.4. However, I can successfully perform a compare on those same databases using SQL Compare 9.

With v11.3, when the compare begins "Reading object text", I get this exact exception.
"Cannot load the Backup Reader dll (see inner exception)!"
"The minimum version of SQL Server required for LiveDB Reading is SQL Server 2005"

With v10.4, at that same point I get this exception.
"Cannot load the Backup Reader dll (see inner exception)!"
"Unable to load SQLite.Interop.dll..."
I did not capture this error message precisely.

Also, I am able to compare two SQL Server 2008 databases using this installation of SQL Compare 11.3.

Are there known incompatibilities with SQL Server 2005? Is this is an issue with configuration of the compare project, or perhaps a configuration or dependency issue on the system that's running SQL Compare?

Mike U

Do you get the option to send in an automated error report? If so, the quickest way for us to be able to diagnose and fix this problem would be for you to send in the error report (including your email address) and then email support@red-gate.com to tell us which email address you used so that we can track the report down quickly.

Mike U

In the meantime though, what's going on at the point of the crash is that Compare is trying to decrypt encrypted object text. To do that, it needs to load System.Data.SQLite.dll from the SQL Compare installation directory, and SQLLite.Interop.dll from either the x86 or x64 sub-directory (depending on whether your OS is 64 bit or not). It's possible that those files have been deleted or corrupted (occasionally by a virus scanner), so it's worth trying a repair install to restore the files.

If you don't need encrypted object support, you can work around the problem by disabling the 'decrypt encrypted objects' project option.

tonyvanriet

Disabling decryption did get me past the error. Thanks Mike.

There's no sign of any issues with the DLLs you mentioned. I've installed the developer suite on numerous machines, different OSs, and I see this same problem. In some cases there was no virus scanner running. I can't imagine what it could be that's making this a systemic problem in my environment.

Anyway, I should be able to get by with the workaround, so thanks again.
I did submit an automated error report and just sent the email address in to support to link it with this forum post.

Mike U

You don't by any chance have the compatibility level set to SQL 2000 (COMPATIBILITY_LEVEL = 80) do you?

Mike U

OK, I've found the automated error report - for reference, our bug number is SC-7985. It's definitely caused by the server being in COMPATIBILITY_LEVEL 80 - we're incorrectly treating it as if it really is a SQL 2000 server, and the way encrypted stored procedures worked changed quite dramatically between 2000 and 2005.

I'll let you know when we fix the issue.

Anu D

Hi tonyvanriet,

Thanks for your patience.

I am very pleased to let you know that the issue reported in this forum SC-7985 is now fixed in our latest version of SQL Compare.

Click here to install the version and release notes

Please let me know if this helps.