Connecting to multiple sql servers

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Connecting to multiple sql servers

Connecting to multiple sql servers

Rate Topic

Display Mode

Topic Options

Author

Message

alex 64682

Posted Friday, January 04, 2013 10:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 08, 2013 2:58 AM
Points: 4, Visits: 6

Hi,

I'd like to connect to multiple databases on different servers all from the same query. So, is it possible to specify a connection string when using the "USE" keyword?

e.g.

USE Data Source=11.11.111.11,111;Initial Catalog=Blah;User ID=Blah
Select * from Blah;
USE Data Source=22.22.22.22,222;Initial Catalog=Blah;User ID=Blah
Select * from Blah;

Post #1402974

e4d4

Posted Friday, January 04, 2013 11:55 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:40 AM
Points: 160, Visits: 1,143

You could use linkedservers, openrowset http://msdn.microsoft.com/en-us/library/ms190312.aspx or openquery http://msdn.microsoft.com/en-us/library/ms188427.aspx

eg. openrowset

SELECT * FROM OPENROWSET('SQLOLEDB', 'Server=HOSTNAME;UID=DB_USER;PWD=StronPassword;','select * table');

Post #1403047

Khaavren

Posted Friday, January 04, 2013 12:06 PM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 12:30 PM
Points: 1, Visits: 195

I just finished looking into this! :)

You can use "SQLCMD Mode" in SSMS. To enable it go to the Query menu and select "SQLCMD Mode"

:connect <ServerName>\<InstanceName>
use [Database]
go
SELECT * FROM [dbo].[TABLE]

:connect <ServerName2>
use [Database2]
go
SELECT * FROM [dbo].[TABLE2]

Post #1403051

Greg Snidow

Posted Friday, January 04, 2013 1:20 PM

SSCommitted

Group: General Forum Members
Last Login: Today @ 9:52 AM
Points: 1,561, Visits: 2,326

alex, I think e4d4's suggestion is probably your best (and maybe only) option. Once you set up the linked servers, you can query against them like this...

SELECT a.<column_list>, b.<column_list>
  FROM Linkedserver1.catalog.schema.table a INNER JOIN
	   Linkedserver2.catalog.schema.table b
	ON some join criteria

I'm not sure if the catalog is required for linked servers to other SQL Servers, but I have them set up to some DB2 and Oracle databases without the catalog, and I am able to bring them into the same query just fine. One caveat, though, the queries take an extremely long time to run. Now, I don't have exposure to the remote catalogs, so I'm not sure if there might be some index info in there that would make them run faster if I could read them. In general, using OPENQUERY(Linkedserver,'your SQL') is much faster than using the 4 part naming convention if you are only hitting one remote.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Post #1403089

alex 64682

Posted Monday, January 07, 2013 4:23 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 08, 2013 2:58 AM
Points: 4, Visits: 6

Many thanks for all the responses. I haven't encountered Openrowset before but it looks like the best option. Not entirely sure where to place the remote IP Address, port number etc for the connection string though, don't suppose anyone has examples of this type of thing?

Post #1403531

John Mitchell-245523

Posted Monday, January 07, 2013 4:36 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 7:03 AM
Points: 4,443, Visits: 7,249

If this is an ad hoc query you wish to run against multiple servers, you might consider setting up a Central Management Server.

John

Post #1403538

dwain.c

Posted Tuesday, January 08, 2013 1:26 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 2,370, Visits: 3,250

alex 64682 (1/7/2013)

I believe that the IP address can be part of the HOSTNAME shown by e4d4 in his example below.

e4d4 (1/4/2013)

You could use linkedservers, openrowset http://msdn.microsoft.com/en-us/library/ms190312.aspx or openquery http://msdn.microsoft.com/en-us/library/ms188427.aspx

eg. openrowset

SELECT * FROM OPENROWSET('SQLOLEDB', 'Server=HOSTNAME;UID=DB_USER;PWD=StronPassword;','select * table');

Beware though, that the arguments passed to OPENROWSET must be string literals; no local variables are allowed.

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1404035

anthony.green

Posted Tuesday, January 08, 2013 1:31 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

John Mitchell-245523 (1/7/2013)

If this is an ad hoc query you wish to run against multiple servers, you might consider setting up a Central Management Server.

John

+1 to that

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1404039

« Prev Topic | Next Topic »

Permissions