I’ve come across a few problems with SSDT and change tracking recently. Today’s issue was that I wanted to add a new external database reference to my project and then create a stored procedure that referenced a table in the external dacpac.

I created the dacpac from command line using the production database and added it as a reference to my project. So far so good. Then I created my proc, which greeted me with warning:

SQL71562: Procedure: [dbo].[MyProc] has an unresolved reference to object.

Now I KNOW that the object and column to exist.

I also had no IntelliSense for referencing any objects in my external database. I messed around a bit removing extended t-sql verification and adding it again. Using and not using a variable. Rebuilding the solution. Unloading/reloading, but nothing would work. I then remember that I had tables in the external database/dacpac that had change tracking enable, note that the table I was trying to reference did not have change tracking enabled. I’ve had issues in the past with change tracking being enabled. So I restored a backup of my production database to another server, dropped change tracking from all tables and disabled change tracking in database properties.

After recreating the dacpac from my non change tracked database I added it back to the project and what do you know? Everything works as expected.

Basically change tracking and SSDT do not play that nicely together, I’ve also opened a Connect issue on a similar problem where you can deploy a database with change tracking enabled from Visual Studio, but when you try from sqlpackage.exe you get an exception. You can see more details here: https://connect.microsoft.com/SQLServer/feedback/details/756827/sqlpackage-fails-to-deploy-when-referencing-external-change-tracking-table

MS have reproduced the problem, but not fixed the problem. I don’t agree with the workaround – you are better off not having the external reference and setting ignore on the error messages in my opinion.

I hope they sort these issues soon because I use change tracking a lot.

Tagged with →  

Leave a Reply

Your email address will not be published. Required fields are marked *