-6

"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.I dont want to use SP_addLinkedserver i.e. i dont want to link the server....I just want to Connect to that server..

7
  • have you had a chance to read: mssqltips.com/sqlservertip/2311/… Apr 17, 2013 at 6:52
  • @AliRazeghi i dont want to use SQLCMD mode or addlinkedserver...i m creating a sp which will fire from central server and collect the data from multiple server...i just want query.. Apr 17, 2013 at 7:08
  • Hi, it's not so much to create a linked server, but to directly connect to another server in SSMS, I could test this but it seems possible. Apr 17, 2013 at 7:12
  • 2
    SSMS won't do what you want to do. USE [dbname] will specify a database name on the same server, but it won't change the connection to a different server. You could do something with powershell or batch files and sqlcmd.exe if you want an automated way to roll the same script out to a bunch of servers. Otherwise, you can only do this with SSMS by changing the connection manually. If you need to do this from within a sproc you've got no choice but to use linked servers. Apr 17, 2013 at 7:55
  • 2
    The only way you can do this is one of the ways you have said you dont want to do it. Use SQLCMD mode in SSMS and the :connect command in your script. Apr 17, 2013 at 8:09

1 Answer 1

3

You can kind of make up a linked server on the fly:

SELECT name
FROM OPENROWSET
(
  'SQLNCLI', 
  'Server=Server_Name\Instance_Name;UID=Username;PWD=Password;',
  'SELECT name FROM master.sys.databases;'
); 

If you want Windows auth, just change the second line to:

  'Server=Server_Name\Instance_Name;Trusted_Connection=yes;',

First you need to set up the source server correctly:

EXEC sp_configure 'show advanced', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'ad hoc distributed queries', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO

But you can't parameterize this etc., making it much less useful than a linked server or SQLCMD mode. So I suggest if all three of these options are not desirable for you, stop trying to catch fish with a toothpick, and do this outside of the database - PowerShell, etc. T-SQL was not really designed to function as an enterprise-wide information gatherer.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.