visual-studio-2015sql-server-data-toolssqlproj

SSDT publish script recreates procedures with no changes


I have a database imported into a .sqlproj, and a .publish.xml publish profile which publishes changes to a remote DB.

My understanding is that when I right click -> Publish -> Generate Script, it should calculate the diff between the local definitions and the remote DB, and generate a script to bring the remote DB in line.

This all seems to work OK, however, the script it generates always contains ALTER FUNCTION and ALTER PROCEDURE statements for the same 40 or so functions and procedures (out of a total of around 1000 defined), whether they have changed or not. When I compare the ALTER statements with the Script Function as -> ALTER to script in SSMS, they are exactly the same.

So my question is: Why does VS think these are different, or why would it recreate them anyway if they are the same?

Notes:

Thanks


Solution

  • Update:

    By renaming the .dacpac as a .zip and extracting the model.xml I could see the <HeaderContents> of some of the procedures had &#xA; (LF - the Line Feed character) in them.

    This made me realise that for some reason all my .SQL files had unix line endings (LF) instead of windows line endings (CR LF). Converting all the files to window line endings (using notepad++) solved the problem.

    Original:

    OK, it looks like most of them are due to string constants in the scripts containing new line characters. Replacing them with their manually defined characters means they're not longer picked up for redeployment. i.e.

    SET @doesnt_work = 
    'FOO
    BAR'
    

    can be replaced with

    SET @works = 'FOO'  + CHAR(13) + CHAR(10) + 'BAR'
    

    Note: This is more of a workaround than a solution, and hopefully someone can suggest a better way to do this...