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

sql-server tsql stored-procedures ssms
Guy picture Guy · Sep 24, 2008 · Viewed 183.3k times · Source

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

mwg2002 picture mwg2002 · Jun 30, 2010

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.

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Select columns from result set of stored procedure

I have a stored procedure that returns 80 columns, and 300 rows. I want to write a select that gets 2 of those columns. Something like SELECT col1, col2 FROM EXEC MyStoredProc 'param1', 'param2' When I used the above syntax I …

Read More
Function vs. Stored Procedure in SQL Server

I've been learning Functions and Stored Procedure for quite a while but I don't know why and when I should use a function or a stored procedure. They look same to me, maybe because I am kinda newbie about that. …

Read More