CODEWITHSUNDEEP
sqlsql-server
Anonymous
Anonymous

Reputation: 43

Possible to create SQL Server stored procedure connecting to multiple servers?

My question has to do in regards to the ability of being able to create stored procedures that connect to multiple servers. If anyone's not familiar with it, there's a :CONNECT syntax in SQL that will switch where your query's being run from. For example: 

:CONNECT SERVERNAME

SELECT *
FROM Table

GO

This would run the query from where the table is stored as opposed to using linked servers (which cause serious performance issues). Does anyone know if it's possible (and how to achieve it) to create stored procedures that switch between servers? I keep getting various error messages when trying to achieve it. Here would be an example:

:CONNECT SERVERNAME

SELECT *
FROM Table

GO

:CONNECT SERVERNAME2

SELECT *
FROM Table

GO

This would connect to two different servers in the same query.

Thanks

UPDATE - 4.26.2018

All,

We've pretty much decided OPENQUERY is our best solution, at least for stored procedures. Unfortunately, we'll be limited by syntax but performance is MUCH better than using linked servers (which is what we're currently using). I appreciate everyone that's chimed in; Your input was invaluable. If you wish to add anything else, please feel free to do so.

Thanks

Upvotes: 0

Views: 2456

Answers (1)

Utrolig
Utrolig

Reputation: 251

Using Linked Servers in a 4-part naming convention will work. It can get ugly fast in regards to performance, so be careful. If you do use Linked Servers, I'd recommend against putting multiple servers in the same SQL statement (including the local server you're on). It isn't terribly efficient. It basically breaks up the query into local and remote queries, it then scrubs the data, and combines it locally before finishing. I've seen users execute queries with multiple servers and take down solid servers in the process.

Another option is the OPENQUERY() method. This still uses a Linked Server, but will send the query to the other server, to process the whole thing there and just ship the data back. This is typically faster and more efficient than the prior.

SELECT
     opn.Id,
     opn.ColumnName,
     opn.AnotherColumnName
FROM OPENQUERY([LinkedServerName],
     'SELECT
          tbl.Id,
          tbl.ColumnName,
          tbl.AnotherColumnName
     FROM DB.Schema.Object AS tbl
     WHERE tbl.ColumnName = ''SomeValue'''
) AS opn

This link gives some more good info: Click Here

SSIS is your best bet here. It's fast, good when using data from multiple servers, and not too difficult to learn (the basics anyway).

So, a few options I've listed...

  1. SSIS - Best option
  2. OPENQUERY() - Better option
  3. Linked Servers - "I guess it works" option

Upvotes: 1

Related Questions