Running dynamic queries on linked server
I wonder, is it possible to make this work?
declare @SQL varchar(max),
@query varchar(max) ='select * from Table'
select @SQL='USE LinkedServer.DBName ' + @query'
exec (@SQL)
It works just fine if you use 'USE' to run queries on the same server, but not on Linked ones.
What I want is to create procedure which will run some dynamic queries on different servers and dbs, and I want to pass ServerName and DBName as a parameter to that sp.
NOTE: I don't want to use it this way:
declare @SQL varchar(max)
select @SQL='
select * from LinkedServer.DBName..Table'
exec (@SQL)
Since that select statement will be retrieved from table
sql-server tsql asked Nov 15 '18 at 21:08
Eric KlausEric Klaus
250415
add a comment |
I wonder, is it possible to make this work?
declare @SQL varchar(max),
@query varchar(max) ='select * from Table'
select @SQL='USE LinkedServer.DBName ' + @query'
exec (@SQL)
It works just fine if you use 'USE' to run queries on the same server, but not on Linked ones.
What I want is to create procedure which will run some dynamic queries on different servers and dbs, and I want to pass ServerName and DBName as a parameter to that sp.
NOTE: I don't want to use it this way:
declare @SQL varchar(max)
select @SQL='
select * from LinkedServer.DBName..Table'
exec (@SQL)
Since that select statement will be retrieved from table
sql-server tsql asked Nov 15 '18 at 21:08
Eric KlausEric Klaus
250415
1
Useopenqueryin your Dynamic SQL. and use NVARCHAR instead of VARCHAR
– Sami
Nov 15 '18 at 21:11
add a comment |
I wonder, is it possible to make this work?
declare @SQL varchar(max),
@query varchar(max) ='select * from Table'
select @SQL='USE LinkedServer.DBName ' + @query'
exec (@SQL)
It works just fine if you use 'USE' to run queries on the same server, but not on Linked ones.
What I want is to create procedure which will run some dynamic queries on different servers and dbs, and I want to pass ServerName and DBName as a parameter to that sp.
NOTE: I don't want to use it this way:
declare @SQL varchar(max)
select @SQL='
select * from LinkedServer.DBName..Table'
exec (@SQL)
Since that select statement will be retrieved from table
sql-server tsql asked Nov 15 '18 at 21:08
Eric KlausEric Klaus
250415
I wonder, is it possible to make this work?
declare @SQL varchar(max),
@query varchar(max) ='select * from Table'
select @SQL='USE LinkedServer.DBName ' + @query'
exec (@SQL)
It works just fine if you use 'USE' to run queries on the same server, but not on Linked ones.
What I want is to create procedure which will run some dynamic queries on different servers and dbs, and I want to pass ServerName and DBName as a parameter to that sp.
NOTE: I don't want to use it this way:
declare @SQL varchar(max)
select @SQL='
select * from LinkedServer.DBName..Table'
exec (@SQL)
Since that select statement will be retrieved from table
sql-server tsql sql-server tsql asked Nov 15 '18 at 21:08
Eric KlausEric Klaus
250415
asked Nov 15 '18 at 21:08
Eric KlausEric Klaus
250415
edited Nov 15 '18 at 21:21
Eric Klaus
asked Nov 15 '18 at 21:08
Eric KlausEric Klaus
250415
asked Nov 15 '18 at 21:08
Eric KlausEric Klaus
250415
asked Nov 15 '18 at 21:08
Eric KlausEric Klaus
250415
250415
1
Useopenqueryin your Dynamic SQL. and use NVARCHAR instead of VARCHAR
– Sami
Nov 15 '18 at 21:11
add a comment |
1
Useopenqueryin your Dynamic SQL. and use NVARCHAR instead of VARCHAR
– Sami
Nov 15 '18 at 21:11
1
1
Use
openquery in your Dynamic SQL. and use NVARCHAR instead of VARCHAR– Sami
Nov 15 '18 at 21:11
Use
openquery in your Dynamic SQL. and use NVARCHAR instead of VARCHAR– Sami
Nov 15 '18 at 21:11
add a comment |
1 Answer
1
active
oldest
votes
Sure, little-known trick to execute the remote database's sp_executesql:
DECLARE @LinkedServer sysname = N'LinkedServer',
@DatabaseName sysname = N'DBName';
-- above are input params
DECLARE @exec nvarchar(4000), @sql nvarchar(max);
SET @sql = N'SELECT * FROM dbo.Table;';
-- or SELECT @sql = DynamicSQL FROM dbo.SomeLocalTable WHERE...
SET @exec = QUOTENAME(@LinkedServer) + N'.'
+ QUOTENAME(@DatabaseName)
+ N'.sys.sp_executesql';
EXEC @exec @sql;
Don't use parentheses. EXEC('string') is evil, evil, evil.
answered Nov 15 '18 at 21:17
Aaron BertrandAaron Bertrand
211k27369407
such an elegant solution. Very cool. Thank you
– Eric Klaus
Nov 15 '18 at 21:23
Well I'll be a monkeys uncle... I have never seen this before.
– Dave Cullum
Nov 15 '18 at 21:25
Nice catch there ;)
– Sami
Nov 15 '18 at 21:28
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53327925%2frunning-dynamic-queries-on-linked-server%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sure, little-known trick to execute the remote database's sp_executesql:
DECLARE @LinkedServer sysname = N'LinkedServer',
@DatabaseName sysname = N'DBName';
-- above are input params
DECLARE @exec nvarchar(4000), @sql nvarchar(max);
SET @sql = N'SELECT * FROM dbo.Table;';
-- or SELECT @sql = DynamicSQL FROM dbo.SomeLocalTable WHERE...
SET @exec = QUOTENAME(@LinkedServer) + N'.'
+ QUOTENAME(@DatabaseName)
+ N'.sys.sp_executesql';
EXEC @exec @sql;
Don't use parentheses. EXEC('string') is evil, evil, evil.
answered Nov 15 '18 at 21:17
Aaron BertrandAaron Bertrand
211k27369407
such an elegant solution. Very cool. Thank you
– Eric Klaus
Nov 15 '18 at 21:23
Well I'll be a monkeys uncle... I have never seen this before.
– Dave Cullum
Nov 15 '18 at 21:25
Nice catch there ;)
– Sami
Nov 15 '18 at 21:28
add a comment |
Sure, little-known trick to execute the remote database's sp_executesql:
DECLARE @LinkedServer sysname = N'LinkedServer',
@DatabaseName sysname = N'DBName';
-- above are input params
DECLARE @exec nvarchar(4000), @sql nvarchar(max);
SET @sql = N'SELECT * FROM dbo.Table;';
-- or SELECT @sql = DynamicSQL FROM dbo.SomeLocalTable WHERE...
SET @exec = QUOTENAME(@LinkedServer) + N'.'
+ QUOTENAME(@DatabaseName)
+ N'.sys.sp_executesql';
EXEC @exec @sql;
Don't use parentheses. EXEC('string') is evil, evil, evil.
answered Nov 15 '18 at 21:17
Aaron BertrandAaron Bertrand
211k27369407
such an elegant solution. Very cool. Thank you
– Eric Klaus
Nov 15 '18 at 21:23
Well I'll be a monkeys uncle... I have never seen this before.
– Dave Cullum
Nov 15 '18 at 21:25
Nice catch there ;)
– Sami
Nov 15 '18 at 21:28
add a comment |
Sure, little-known trick to execute the remote database's sp_executesql:
DECLARE @LinkedServer sysname = N'LinkedServer',
@DatabaseName sysname = N'DBName';
-- above are input params
DECLARE @exec nvarchar(4000), @sql nvarchar(max);
SET @sql = N'SELECT * FROM dbo.Table;';
-- or SELECT @sql = DynamicSQL FROM dbo.SomeLocalTable WHERE...
SET @exec = QUOTENAME(@LinkedServer) + N'.'
+ QUOTENAME(@DatabaseName)
+ N'.sys.sp_executesql';
EXEC @exec @sql;
Don't use parentheses. EXEC('string') is evil, evil, evil.
answered Nov 15 '18 at 21:17
Aaron BertrandAaron Bertrand
211k27369407
Sure, little-known trick to execute the remote database's sp_executesql:
DECLARE @LinkedServer sysname = N'LinkedServer',
@DatabaseName sysname = N'DBName';
-- above are input params
DECLARE @exec nvarchar(4000), @sql nvarchar(max);
SET @sql = N'SELECT * FROM dbo.Table;';
-- or SELECT @sql = DynamicSQL FROM dbo.SomeLocalTable WHERE...
SET @exec = QUOTENAME(@LinkedServer) + N'.'
+ QUOTENAME(@DatabaseName)
+ N'.sys.sp_executesql';
EXEC @exec @sql;
Don't use parentheses. EXEC('string') is evil, evil, evil.
answered Nov 15 '18 at 21:17
Aaron BertrandAaron Bertrand
211k27369407
answered Nov 15 '18 at 21:17
Aaron BertrandAaron Bertrand
211k27369407
answered Nov 15 '18 at 21:17
Aaron BertrandAaron Bertrand
211k27369407
answered Nov 15 '18 at 21:17
Aaron BertrandAaron Bertrand
211k27369407
211k27369407
such an elegant solution. Very cool. Thank you
– Eric Klaus
Nov 15 '18 at 21:23
Well I'll be a monkeys uncle... I have never seen this before.
– Dave Cullum
Nov 15 '18 at 21:25
Nice catch there ;)
– Sami
Nov 15 '18 at 21:28
add a comment |
such an elegant solution. Very cool. Thank you
– Eric Klaus
Nov 15 '18 at 21:23
Well I'll be a monkeys uncle... I have never seen this before.
– Dave Cullum
Nov 15 '18 at 21:25
Nice catch there ;)
– Sami
Nov 15 '18 at 21:28
such an elegant solution. Very cool. Thank you
– Eric Klaus
Nov 15 '18 at 21:23
such an elegant solution. Very cool. Thank you
– Eric Klaus
Nov 15 '18 at 21:23
Well I'll be a monkeys uncle... I have never seen this before.
– Dave Cullum
Nov 15 '18 at 21:25
Well I'll be a monkeys uncle... I have never seen this before.
– Dave Cullum
Nov 15 '18 at 21:25
Nice catch there ;)
– Sami
Nov 15 '18 at 21:28
Nice catch there ;)
– Sami
Nov 15 '18 at 21:28
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53327925%2frunning-dynamic-queries-on-linked-server%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Use
openqueryin your Dynamic SQL. and use NVARCHAR instead of VARCHAR– Sami
Nov 15 '18 at 21:11