Visual Studio数据库项目:在创建SQL Server登录名之前检查它是否存在
当我为SQL 2012创建Visual Studio数据库项目并将其与现有数据库同步(使用比较架构")时,我还将同步SQL Server登录名. Visual Studio为登录生成以下脚本:
When I create a Visual Studio database project for SQL 2012 and synchronise it with an existing database (using Compare Schema), I also synchronise a SQL server login. Visual Studio generates the following script for the login:
CREATE LOGIN [my_user] WITH PASSWORD = 'somesecurepass'
当我尝试在存在此登录名的服务器上发布生成的SQL时,sqlcmd向我显示错误:
When I try to publish the generated SQL on a server where this login exists, sqlcmd shows me an error:
The server principal my_user already exists.
当我查看由Visual Studio生成的sql脚本时,我看到许多对象都包装在IF EXISTS语句中,但是CREATE LOGIN却没有包装!
When I look at the sql script generated by Visual Studio, I see that many objects are wrapped in IF EXISTS statements, but CREATE LOGIN is not wrapped!
我尝试将其手动包装在项目中的SQL脚本中,但随后该项目无法构建,并且出现了指向IF的错误:
I tried to wrap it manually in the SQL script in the project, but then the project does not build and there is an error pointing to IF:
SQL70001: This statement is not recognized in this context.
现在,如何强制Visual Studio使用IF EXISTS检查生成登录创建脚本,同时又不失去同步能力?
Now how do I force Visual Studio to generate the login creation script with the IF EXISTS check and also do not lose the synchronisation abilities?
将脚本文件(* .sql)的生成操作"属性更改为无".这样就解决了问题.
Change the Build Action property of the script file (*.sql) to None. This solves the problem.
通过在解决方案资源管理器中右键单击sql文件,然后单击属性来访问生成操作属性.或者,在解决方案资源管理器中突出显示该文件,然后按"F4".
The build action property is accessed by right-clicking the sql file in solution explorer, then clicking properties. Alternatively, highlight the file in solution explorer and press 'F4'.