SQL: Executing a query to dynamic remote server

Question

I want to be able to execute remote queries based on the results of a local query.

For instance:

DECLARE @REMOTESERVER VARCHAR(10)

Select TOP 1 @REMOTESERVER = RemoteServer from TABLE

--Execute the next query on a remote server from the value I retrieved above

Select * from tblCustomers

What RDBMS are you using?

sgeddes
– sgeddes

2013-01-10 18:50:59 +00:00
Commented Jan 10, 2013 at 18:50 — sgeddes
– sgeddes, Commented Jan 10, 2013 at 18:50

Community · Accepted Answer · 2017-05-23 12:12:34Z

1

What RDBMS are you using? Some will not support a pure sql way of doing this. Others, like SQL Server, might support this scenario. Is the remote server accessible via a linked server that you can access. You could then use dynamic sql to create your sql string. Something like this should work in SQL Server:

SET @Sql = 'SELECT * FROM [' + @RemoteServer + '].dbname.schema.tblCustomers'
EXEC @Sql

Here is a post about linked servers: https://stackoverflow.com/a/4091984/1073631

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Jan 10, 2013 at 18:50

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

paul Over a year ago

Yes, that worked thanks! The only thing I had to do differently was wrap the exec variable as such: EXEC(@Sql) Thanks!

Collectives™ on Stack Overflow

SQL: Executing a query to dynamic remote server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related