Dynamic Linked Server Query

Question

Is it possible to construct a dynamic query to for a linked server (and if so how)?

For example:

@linkedServer varchar(50)
@var1 varchar(10)
@var2 varchar(10)

select * 
  from openquery(@linkedServer, 
                 'select c1,c2 
                    from t1 
                   where p1 = @var1 
                     and p2= @var2')

SQLMenace · Accepted Answer · 2010-08-04 18:26:01Z

4

example

exec ('select * from openquery(' + @linkedServer + 
', ''select c1,c2 from t1 where p1 = '' + @var1 + ''and p2= '' + @var2 + ''')

make sure to read The Curse and Blessings of Dynamic SQL to protect against SQL injection

answered Aug 4, 2010 at 18:26

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

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

2 Comments

Wouter Vanherck Over a year ago

Isn't the closing bracket for openquery() missing?

cgitosh Over a year ago

12 years and 8 months later, I have a question. How do I create a view out of this query? I have a similar exec('select statement) at LinkedServer which works fine. Now I want to create a View which I will then use with Power BI. When I write the standard Create View Viewname AS exec('select statement) at LinkedServer.. I get an error because create view is expecting a select statement after AS... Kindly assist

KM. · Accepted Answer · 2010-08-04 18:26:32Z

2

see EXEC() at Linked Server section of The Curse and Blessings of Dynamic SQL by Erland Sommarskog

from that article:

A special feature added in SQL 2005 is that you can use EXEC() to run pass-through queries on a linked server. This could be another instance of SQL Server, but it could also be an Oracle server, an Access database, Active directory or whatever. The SQL could be a single query or a sequence of statements, and could it be composed dynamically or be entirely static. The syntax is simple, as seen by this example:

EXEC('SELECT COUNT(*) FROM ' + @db + '.dbo.sysobjects') AT SQL2K

SQL2K is here a linked server that has been defined with sp_addlinkedserver.

answered Aug 4, 2010 at 18:26

KM.

104k34 gold badges183 silver badges214 bronze badges

2 Comments

SQLMenace Over a year ago

doesn't work with a dynamically named linked server, this doesn't work...EXEC('SELECT COUNT(*) FROM dbo.sysobjects') AT @linkedServer

KM. Over a year ago

@SQLMenace, this will work EXEC ('EXEC(''SELECT COUNT(*) FROM ' + @db + '.dbo.sysobjects'') AT '+@linkedServer)

Collectives™ on Stack Overflow

Dynamic Linked Server Query

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related