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

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.

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]

If you are connecting to multiple servers, be sure to insert GO between connections; otherwise your T-SQL won't execute on the server you're thinking it will.

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.

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

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.

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

First step:

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

Second step:

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