在 sql 脚本中指定服务器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8494361/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
specify server in sql script
提问by amateur
I am writing a sql script for sql server 2008 where I place a use statement at the beginning that specifies the database the script should be run against:
我正在为 sql server 2008 编写一个 sql 脚本，其中我在开头放置了一个 use 语句，该语句指定应针对该脚本运行的数据库：
use [my_database]
As I have different environments where the same database exists, eg dev, qa, prod, is there any way I can specify in the script the environment the script is for, either by server name or ip address or by any other mechanism.
由于我有相同数据库存在的不同环境，例如 dev、qa、prod，有什么方法可以在脚本中指定脚本所在的环境，通过服务器名称或 IP 地址或通过任何其他机制。
回答by Filip De Vos
You can put the SQL Management Studio in SQLCMD modeand specify the server with the :CONNECT myserverstatement.
您可以将 SQL Management Studio 置于SQLCMD 模式并使用:CONNECT myserver语句指定服务器。
You can switch on command mode by clicking on the option in the pic below
您可以通过单击下图中的选项来打开命令模式
Your script would then look something like this
你的脚本看起来像这样
:CONNECT devserver
use [my_database]
SELECT * FROM my_table
You can even make the query window switch servers during execution
甚至可以让查询窗口在执行过程中切换服务器
:CONNECT devserver
use [my_database]
SELECT * FROM my_table
GO
:CONNECT uatserver
use [my_database]
SELECT * FROM my_table
To connect with a specific user and password you can specify this as follows
要连接特定的用户和密码，您可以按如下方式指定
:CONNECT devserver -U myUser -P myPassword
use [my_database]
SELECT * FROM my_table
There are actually a number of options you can specify which are well documented on msdn.
回答by JNK
That's a CONNECTIONsetting, not a parameter within the script.
这是一个CONNECTION设置，而不是脚本中的参数。
You can run the same script in different environments via batch file or powershell script if desired, or you could set up linked servers, but you can't just say
如果需要，您可以通过批处理文件或 powershell 脚本在不同环境中运行相同的脚本，或者您可以设置链接服务器，但您不能只说
USE SomeOtherServer
USE SomeOtherServer
There are security and networking implications as well.
还有安全和网络方面的影响。
回答by Jonathan Van Matre
Assuming you will run all of these scripts on a particular server - e.g. the Dev server - then you merely need to create a Linked Server to each of the other servers.
假设您将在特定服务器（例如开发服务器）上运行所有这些脚本，那么您只需要为每个其他服务器创建一个链接服务器。
Then, for example, you could run an identically named stored procedure on each of these servers thusly:
然后，例如，您可以在这些服务器中的每一个上运行相同命名的存储过程：
EXEC MyDatabase.dbo.mysp_DoSomething --Dev Server; no server prefix needed since that's where we are
EXEC QA.MyDatabase.dbo.mysp_DoSomething --QA Server
EXEC Prod.MyDatabase.dbo.mysp_DoSomething --Prod server
etc.
等等。
