12

After installing a custom CLR object Sql Server Developer Tools (SSDT) VS2012 will not allow an update. The error is "Source schema drift detected. Press Compare to refresh. After refresh same thing happens.

Tried In settings, I set the object to just Stored Procedures. Settings ->General -> Block on possible data loss -> tried both on and off.

Eric Rohlfs
  • 1,811
  • 2
  • 19
  • 29

4 Answers4

8

This sort of loop can also be caused by a referenced SSDT project failing to build. The referenced project may be missing, unloaded, or have an error which prevents the compare from completing.

Mitch
  • 21,223
  • 6
  • 63
  • 86
  • Thanks, in my case VS was complaining about "Source schema drift" repeatedly and yet I was not changing it during the schema update - a couple problems in SSDT project were the cause (missing GO statements between CREATE INDEX) – steve Nov 20 '15 at 00:24
  • Thanks! All I was trying to do was edit an index on a table. Turns out I had an unfinished proc in the solution that I had forgotten about, and it was causing the project build to fail. – David Gunderson Oct 17 '16 at 19:54
3

This is not an answer but a clue to deal with this problem.

I was to update a colum from varchar[200] to varchar[MAX] and got this problem as well. So I logged in the server and tried to update the database manually via SQL Management Studio which was installed there, and I got this error: 

"Saving changes is not permitted. The changes you have made require the folloing tables to be drpped and re-created. You have either made changes to a table that can't be re-created or enable the option Prevent saving changes that require the table to be re-created."

Seems that re-creating table is something so dangerous that "block/unblock on possible data lose" cannot handle. So I think only if we can walk around this LOCAL warning, could we update the database REMOTELY.

But, why [200] to [max] leads to re-creating table? It does not make any sense. I tried [200] to [1000], and it did not work as well. This might be the key to this problem.

And, if you do the same update in Server explorer in VS, instead of SQL Management Studio, it works. Again, why?

cheny
  • 2,545
  • 1
  • 24
  • 30
3

This can happen when a db user "changes".

The following rather scary forum page recounts issues where foreign hackers were trying to brute-force access to the "sa" db user, with each attempt changing the sa-user's date timestamp (which is seen as a schema drift):

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5c22a7b4-7a82-4717-a118-2475bc62705b/schema-compareupdate-error-target-schema-drift-detected?forum=ssdt

Here is also mentioned that you can query the sa-user a few times, to see if this is happening to you:

SELECT * FROM sys.server_principals WHERE principal_id=1

I am currently experiencing the same issue (that the sa-user is being modified; I know nothing about hackers yet) and am yet to find a solution.

Edit - I turned on logging in Windows Firewall via properties > logging, and we setup a blocking rule on port 3071, which had a lot of unexplained traffic. Then the problem went away.

Protector one
  • 6,926
  • 5
  • 62
  • 86
  • I know its been a year but did you find anything? Happening to me too – Drammy Oct 31 '19 at 10:00
  • 1
    Thanks for reminding me! I've updated my answer with the solution that fixed it for me. – Protector one Nov 01 '19 at 11:58
  • Interesting, I've had the same problem but I just disabled the sa account. Its just a home dev server with nothing significant on it but I still don't like the thought of someone trying to force their way onto the box.. – Drammy Nov 01 '19 at 14:02
  • We was having this problem too, for failed login is usefull see Windows Event Viewer --> https://www.mssqltips.com/sqlservertip/1735/auditing-failed-logins-in-sql-server/ – Dani Dec 05 '19 at 14:25
0

I tried running VS as an administrator, it worked.

Eugene Honor
  • 1