SSDT发布脚本无需更改即可重新创建过程 [英] SSDT publish script recreates procedures with no changes

问题描述

我有一个导入到.sqlproj中的数据库和一个.publish.xml发布配置文件，该配置文件将更改发布到远程数据库.

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.

这一切似乎都可以，但是，它生成的脚本始终包含相同的40多个函数和过程的ALTER FUNCTION和ALTER PROCEDURE语句(总共定义了1000个左右)，他们有没有改变.当我将ALTER语句与Script Function的-> ALTER进行比较以在SSMS中编写脚本时，它们是完全相同的.

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.

所以我的问题是:为什么VS认为这些是不同的，或者如果它们相同，为什么无论如何都要重新创建它们?

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

注意:

• 这些功能并不特殊-其中一些功能很简单，例如定义varchar，将其设置为值并返回它.
• 我尝试将ALTER脚本运行到数据库中，但是它们会继续生成.
• 我已经检查了SSMS中的属性(右键单击->属性)，但看不到始终重新创建的属性和没有重新创建的属性的任何明显区别.
• 我的发布个人资料是沼泽标准个人资料.

• 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.

谢谢

推荐答案

更新:

通过将.dacpac重命名为.zip并提取model.xml，我可以看到其中某些过程的<HeaderContents>中包含
(LF-换行符).

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.

这使我意识到，由于某种原因，我所有的.SQL文件都具有unix行尾(LF)而不是Windows行尾(CR LF).将所有文件转换为窗口行尾(使用记事本++ 使用)解决了该问题.

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.

原始:

好的，看起来其中大多数是由于脚本中包含换行符的字符串常量所致.将它们替换为手动定义的字符意味着不再需要重新部署它们. 即

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'

可以替换为

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...

这篇关于SSDT发布脚本无需更改即可重新创建过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！