blocks|key|1530250|text|尝试创建链接服务器(可以使用sp_addlinkedserver执行此操作)，然后使用OPENQUERY|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1530251|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms190479.aspx|1|http://msdn.microsoft.com/en-us/library/ms188427.aspx^0|E|I|0|17|9|1|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]|$A|R|B|S|1|T]]|C|$]]|$1|D|3|-4|5|6|7|U|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]|L|$5|G|H|I|C|$J|M]]]]

Try creating a linked server (which you can do with <a href="http://msdn.microsoft.com/en-us/library/ms190479.aspx" rel="nofollow noreferrer">sp_addlinkedserver</a>) and then using <a href="http://msdn.microsoft.com/en-us/library/ms188427.aspx" rel="nofollow noreferrer">OPENQUERY</a>

blocks|key|1530296|text|此外，请确保在编写涉及链接服务器的查询时，包括如下括号：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1530297|SELECT+*+FROM+[LinkedServer].[RemoteDatabase].[User].[Table]|code-block|syntax|javascript|1530298|我发现，至少在2000/2005年中，[]括号是必需的，至少在服务器名称周围是必需的。|1530299|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Also, make sure when you write the query involving the linked server, you include brackets like this:

<pre><code>SELECT * FROM [LinkedServer].[RemoteDatabase].[User].[Table]
</code></pre>

I've found that at least on 2000/2005 the [] brackets are necessary, at least around the server name.

blocks|key|1530362|text|如果我解释这个问题-是否可以在DDL中选择用于查询执行的服务器上下文-答案是否定的。只能使用USE以编程方式选择数据库上下文。(已经在外部预先选择了服务器上下文)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1530363|链接服务器和开放查询可以提供对DDL的访问，但需要在一定程度上重写代码以封装为字符串-这使得开发/调试变得困难。|1530364|或者，您也可以求助于外部驱动程序来拾取SQL文件，然后通过OPEN+QUERY发送到远程服务器。但是，在大多数情况下，您也可以在第一个位置直接连接到服务器来评估DDL。|1530365|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

If I were to paraphrase the question - is it possible to pick server context for query execution in the DDL - the answer is no. Only database context can be programmatically chosen with USE. (having already preselected the server context externally)

Linked server and OPEN QUERY can give access to the DDL but require somewhat a rewrite of your code to encapsulate as a string - making it difficult to develop/debug. 

Alternately you could resort to an external driver program to pickup SQL files to send to the remote server via OPEN QUERY. However in most cases you might as well have connected to the server directly in the 1st place to evaluate the DDL.

blocks|key|1530407|text|如果可能，请查看SSIS+(SQL+Server+Integration+Services)。我刚刚接触这个工具包，但已经在40%2B服务器上循环，并准备造成各种破坏;)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1530408|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If possible, check out SSIS (SQL Server Integration Services). I am just getting my feet wet with this toolkit, but already am looping over 40+ servers and preparing to wreak all kinds of havoc ;)

blocks|key|1745384|text|在SQL+Server+Management+Studio中，从“查询”菜单中打开SQLCMD模式。然后，在脚本的顶部键入以下命令|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1745385|:Connect+server_name[\instance_name]+[-l+timeout]+[-U+user_name+[-P+password]|code-block|syntax|javascript|1745386|如果要连接到多个服务器，请确保在连接之间插入GO；otherwise+your+T-SQL+won't+execute+on+the+server+you're+thinking+it+will.|offset|length|style|CODE|1745387|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlmatters.com/Articles/Changing%2520the%2520SQL%2520Server%2520connection%2520within%2520an%2520SSMS%2520Query%2520Windows%2520using%2520SQLCMD%2520Mode.aspx^0|0|0|M|2|P|21|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@$I|X|J|Y|K|L]]|9|@$I|Z|J|10|1|11]]|A|$]]|$1|M|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

In SQL Server Management Studio, turn on SQLCMD mode from the Query menu.
Then at the top of your script, type in the command below

<pre><code>:Connect server_name[\instance_name] [-l timeout] [-U user_name [-P password]
</code></pre>

If you are connecting to multiple servers, be sure to insert <code>GO</code> between connections; <a href="http://www.sqlmatters.com/Articles/Changing%20the%20SQL%20Server%20connection%20within%20an%20SSMS%20Query%20Windows%20using%20SQLCMD%20Mode.aspx" rel="noreferrer">otherwise your T-SQL won't execute on the server you're thinking it will.</a>

blocks|key|1745417|text|每当我们试图从另一个服务器检索任何数据时，我们都需要两个步骤。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1745418|第一步：|1745419|--+Server+one+scalar+variable
DECLARE+@SERVER+VARCHAR(MAX)
--Oracle+is+the+server+to+which+we+want+to+connect
EXEC+SP_ADDLINKEDSERVER+@SERVER='ORACLE'|code-block|syntax|javascript|1745420|第二步：|1745421|--DBO+is+the+owner+name+to+know+table+owner+name+execute+(SP_HELP+TABLENAME)++++
SELECT+*+INTO+DESTINATION_TABLE_NAME+
FROM+ORACLE.SOURCE_DATABASENAME.DBO.SOURCE_TABLE|1745422|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Whenever we are trying to retrieve any data from another server we need two steps.

First step:

<pre><code>-- Server one scalar variable
DECLARE @SERVER VARCHAR(MAX)
--Oracle is the server to which we want to connect
EXEC SP_ADDLINKEDSERVER @SERVER='ORACLE'
</code></pre>

Second step:

<pre><code>--DBO is the owner name to know table owner name execute (SP_HELP TABLENAME) 
SELECT * INTO DESTINATION_TABLE_NAME 
FROM ORACLE.SOURCE_DATABASENAME.DBO.SOURCE_TABLE
</code></pre>

blocks|key|1745448|text|如果您连接到多个服务器，您应该在切换服务器之前添加一个'GO‘，否则您的sql语句将在错误的服务器上运行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1745449|例如：|1745450|:CONNECT+SERVER1
Select+*+from+Table
GO
enter+code+here
:CONNECT+SERVER1
Select+*+from+Table
GO|code-block|syntax|javascript|1745451|http://www.sqlmatters.com/Articles/Changing%2520the%2520SQL%2520Server%2520connection%2520within%2520an%2520SSMS%2520Query%2520Windows%2520using%2520SQLCMD%2520Mode.aspx|offset|length|1745452|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlmatters.com/Articles/Changing%2520the%2520SQL%2520Server%2520connection%2520within%2520an%2520SSMS%2520Query%2520Windows%2520using%2520SQLCMD%2520Mode.aspx^0|0|0|0|0|41|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

If you are connecting to multiple servers you should add a 'GO' before switching servers, or your sql statements will run against the wrong server.

e.g.

<pre><code>:CONNECT SERVER1
Select * from Table
GO
enter code here
:CONNECT SERVER1
Select * from Table
GO
</code></pre>

<a href="http://www.sqlmatters.com/Articles/Changing%20the%20SQL%20Server%20connection%20within%20an%20SSMS%20Query%20Windows%20using%20SQLCMD%20Mode.aspx" rel="noreferrer">http://www.sqlmatters.com/Articles/Changing%20the%20SQL%20Server%20connection%20within%20an%20SSMS%20Query%20Windows%20using%20SQLCMD%20Mode.aspx</a>

blocks|key|1530525|text|在我的C盘上，我首先创建了一个txt文件来创建一个新表。您可以在此文本文件中使用您想要的任何内容|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1530526|在本例中，文本文件名为"Bedrijf.txt“|1530527|内容：|1530528|Print+'START(A)+create+table'

GO+1

If+not+EXISTS
(
++++SELECT+*
++++FROM+INFORMATION_SCHEMA.TABLES
++++WHERE+TABLE_NAME+=+'Bedrijf'
)
BEGIN
CREATE+TABLE+[dbo].[Bedrijf]+(
[IDBedrijf]+[varchar]+(38)+++NOT+NULL+,
[logo]+[varbinary]+(max)+NULL+,
[VolledigeHandelsnaam]+[varchar]+(100)++NULL+
)+ON+[PRIMARY]|code-block|syntax|javascript|1530529|保存它|1530530|然后我创建了另一个txt文件，文件名为"Bedrijf.bat“，扩展名为bat。它的内容：|1530531|OSQL.EXE++-U+Username+-P+Password+-S+IPaddress+-i+C:Bedrijf.txt+-o+C:Bedrijf.out+-d+myDatabaseName|1530532|保存它并在资源管理器中双击以执行|1530533|结果将保存在C盘上的txt文件中，文件名为"Bedrijf.out“。|1530534|它显示了|1530535|1>+2>+3>+START(A)+create+table|1530536|如果一切顺利|1530537|就这样|1530538|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|16|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|17|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|18|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|19|8|@]|9|@]|A|$]]|$1|O|3|P|5|H|7|1A|8|@]|9|@]|A|$I|J]]|$1|Q|3|R|5|6|7|1B|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1C|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1D|8|@]|9|@]|A|$]]|$1|W|3|X|5|H|7|1E|8|@]|9|@]|A|$I|J]]|$1|Y|3|Z|5|6|7|1F|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1G|8|@]|9|@]|A|$]]|$1|12|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]]|13|$]]

on my C drive I first create a txt file to create a new table. You can use what ever you want in this text file

in this case the text file is called "Bedrijf.txt"

the content:

<pre><code>Print 'START(A) create table'

GO 1

If not EXISTS
(
 SELECT *
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_NAME = 'Bedrijf'
)
BEGIN
CREATE TABLE [dbo].[Bedrijf] (
[IDBedrijf] [varchar] (38) NOT NULL ,
[logo] [varbinary] (max) NULL ,
[VolledigeHandelsnaam] [varchar] (100) NULL 
) ON [PRIMARY]
</code></pre>

save it

then I create an other txt file with the name "Bedrijf.bat" and the extension bat.
It's content:

<pre><code>OSQL.EXE -U Username -P Password -S IPaddress -i C:Bedrijf.txt -o C:Bedrijf.out -d myDatabaseName
</code></pre>

save it and from explorer double click to execute

The results will be saved in a txt file on your C drive with the name "Bedrijf.out"

it shows

<pre><code>1&gt; 2&gt; 3&gt; START(A) create table
</code></pre>

if all goes well

That's it

blocks|key|1745478|text|尝试像这样的PowerShell类型：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1745479|$cn+=+new-object+system.data.SqlClient.SQLConnection("Data+Source=server1;Initial+Catalog=db1;User+ID=user1;Password=password1");
$cmd+=+new-object+system.data.sqlclient.sqlcommand("exec+Proc1",+$cn);
$cn.Open();
$cmd.CommandTimeout+=+0
$cmd.ExecuteNonQuery()
$cn.Close();|code-block|syntax|javascript|1745480|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try PowerShell Type like:

<pre><code>$cn = new-object system.data.SqlClient.SQLConnection("Data Source=server1;Initial Catalog=db1;User ID=user1;Password=password1");
$cmd = new-object system.data.sqlclient.sqlcommand("exec Proc1", $cn);
$cn.Open();
$cmd.CommandTimeout = 0
$cmd.ExecuteNonQuery()
$cn.Close();
</code></pre>

If I need to copy a stored procedure (SP) from one SQL Server to another I right click on the SP in SSMS and select Script Stored Procedure as > CREATE to > New Query Editor Window. I then change the connection by right clicking on that window and selecting Connection > Change Connection... and then selecting the new server and F5 to run the create on the new server.

So my question is "What is the T-SQL syntax to connect to another SQL Server?" so that I can just paste that in the top of the create script and F5 to run it and it would switch to the new server and run the create script.

While typing the question I realized that if I gave you the back ground to what I'm trying to do that you might come up with a faster and better way from me to accomplish this.

What is the T-SQL syntax to connect to another SQL Server?

SQL Server

数据库

服务器

如果需要将存储过程( SP )从一台SQL Server复制到另一台SQL Server，请在SSMS中右键单击该SP，然后选择将存储过程脚本编写为> CREATE to > New Query Editor Window。然后我通过右键单击该窗口并选择connection > change Connection......

问连接到另一个SQL Server的T-SQL语法是什么？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问连接到另一个SQL Server的T-SQL语法是什么？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问连接到另一个SQL Server的T-SQL语法是什么？
EN