[sql-server] 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.

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

Try PowerShell Type like:

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

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.

:CONNECT SERVER1
Select * from Table
GO
enter code here
:CONNECT SERVER1
Select * from Table
GO

http://www.sqlmatters.com/Articles/Changing%20the%20SQL%20Server%20connection%20within%20an%20SSMS%20Query%20Windows%20using%20SQLCMD%20Mode.aspx

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

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.

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:

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]

save it

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

OSQL.EXE  -U Username -P Password -S IPaddress -i C:Bedrijf.txt -o C:Bedrijf.out -d myDatabaseName

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

1> 2> 3> START(A) create table

if all goes well

That's it

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

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.

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