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

User · Answer

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

User · Answer

Try creating a linked server  which you can do with sp addlinkedserver  and then using OPENQUERY

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt  2 gt  3 gt  START A  create table   if all goes well  That s it

User · Answer

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

[sql-server] What is the T-SQL syntax to connect to another SQL Server?

Examples related to sql-server

Examples related to tsql

Examples related to stored-procedures

Examples related to ssms