In a T-SQL script, connect to different SQL Server databases without manual connection and frequent switching of SQL Server
The following are network instances:
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 wocould 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.
The general idea is that if you need to copy the stored procedure in another SQL server database and the two databases are not under the same instance, You need to manually obtain the stored procedure, switch to the instance database to be created and execute create procedure. is there a way to directly generate this sp script from the original SQL server and directly use the code to connect to the database that needs to regenerate the sp and execute create procedure? This saves the trouble of manually switching SQL server instances. There are many answers, but I cannot explain them myself:
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]
Use: connect to the SQL server instance. In the query menu, start SQLCMD mode
Copy from:
Http://stackoverflow.com/questions/125457/what-is-the-t-sql-syntax-to-connect-to-another-sql-server