SSDT publish script recreates procedures with no c

问题:

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:

• The functions are not special - some of them are as simple as defining a varchar, setting it to a value and returning it.
• I've tried running the ALTER scripts into the database, but they continue to be generated.
• I've checked the properties (right click -> properties) in SSMS but can't see anything obviously different about those it always recreates and those it does not.
• My publish profile is the bog standard one.

Thanks

回答1:

Update:

By renaming the .dacpac as a .zip and extracting the model.xml I could see the <HeaderContents> of some of the procedures had 
 (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...