Error while comparing schemas of Database Project

Question

It was working fine earlier, when I was comparing schema of a database project in Visual studio 2012 with a remote database deployed on a network server, but I am unable to troubleshoot why it is failing now.

It works fine when I compare the schema with same database deployed on local PC. Therefore it seems that comparing tool works, but there seems to be problem with remote server. Any way I can troubleshoot the problem.

The error I am having comparing with remote server is below:

Error    52    Target is unavailable: Value cannot be null.Parameter name: catalogStamp    SqlSchemaCompare2    0    0   

Answer 1

Do you have remote access to the SQL DB enabled (is the TCP/IP service enabled) also if you are using SQL Server Login do your SQl Server Security options allow both Windows Authentication and SQL Server Logins ?

Answer 2

I came across this same issue recently, probably not the same cause as the original poster but in my case it was down to the compatibility level of the database I was connecting to.

The database I was connecting to was Azure based and Microsoft have recently updated these to default compatibility level 140 (the equivalent of SQL 2017). Previous instances were running on earlier levels and had worked straight from the off, running the below fixed the issue for me:

ALTER DATABASE [Database_Name] SET COMPATIBILITY_LEVEL = 120

Obviously be sure to check any side-effects before you make a compatibility level change on any production db!

As Thibault commented below, if you want to check existing compatibility levels you can use:

SELECT name, compatibility_level FROM sys.databases 

And if you want to see what versions the different compatibility levels relate to have a look here https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level

Answer 3

My DB was Azure SQL DB and was facing this issue, It got resolved for me by using Visual Studio 2017 instead of changing compatibility levels.

Answer 4

You must specify a login and password when connecting to the SQL server. If you use a login without a password that turns this error.