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

Question

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.

Answer 1

Try creating a linked server (which you can do with sp_addlinkedserver) and then using OPENQUERY

Answer 2

Update: for connecting to another sql server and executing sql statements, you have to use sqlcmd Utility. This is typically done in a batch file. You can combine this with xmp_cmdshell if you want to execute it within management studio.

---

one way is to configure a linked server. then you can append the linked server and the database name to the table name. (select * from linkedserver.database.dbo.TableName)

USE master
GO
EXEC sp_addlinkedserver 
    'SEATTLESales',
    N'SQL Server'
GO

Answer 3

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

SELECT * FROM [LinkedServer].[RemoteDatabase].[User].[Table]

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

Answer 4

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.

Answer 5

No one really understood your question. I was hoping there was a way of doing this myself. I use multiple instances of SQL Server and some scripts I write are for databases on one box, while others are for the other. When you switch gears to look something up and open up one of your scripts and try to run it against the wrong server is a pain. It would be nice if you could just say "connect to server X" at the top of the script instead of having to right click and change the server all the time.

Answer 6

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 ;)

Answer 7

Here's an option:

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

:Connect server_name[\instance_name] [-l timeout] [-U user_name [-P password]]