OPENQUERY and dynamic SQL
-
-----OK SORTED IT - built the entire query as a string and EXEC it------
---------Thank you--------
Hi all,
Trying to achieve something like this;
CREATE PROCEDURE [dbo].[linked_Server_Test]
@ORDERID bigint, @errorcode char(2) OUTPUT
DECLARE @Accref varchar(50)
SELECT @Accref = CustomerID
FROM OPENQUERY(LINKEDSERVERNAME,
'
SELECT CustomerID
FROM dbo.SS_ORDER
WHERE O.OrderID = @ORDERID
'
)
The variable @ORDERID is defined as an input variable but I cant seem to construct a suitable string for the openquery command. I have tried the above (variable @ORDERID must be declared), tried concatting it into a string first then just doing OPENQUERY (linkedervername, @sqlstring) with the same result (variable @sqlstring must be declared).
I have also tried concatting the variable at the end like '... Where orderID =' + cast(@OrderID as varchat(50)), which gives a syntax error.
Any help greatly appreciated
Rolf.
-
OPENQUERY does not accept variables for its arguments
-
try this
CREATE PROCEDURE dbo.syp_DOLINKQUERY1
@QER Varchar(2000)
AS
-- query a linked tabel
declare @QER1 varchar(2000)
declare @LINK varchar (100)
declare @nsql nvarchar (4000)
set @link='MFG'
set @QER1= replace(@qer,char(39),char(39)+char(39))
set @qer='select * from openquery(' + @link + ',' + char(39) + @qer1 + char(39)+ ') '
set @nsql=cast (@qer as nvarchar (4000))
exec sp_executesql @nsql
return
GO
-----
exec dbo.syp_DOLINKQUERY1 'Select * from table where a = ..'
wilmos
-
Hi all,
Thanks for the responses, thought I had sorted it but no!
This doesnt work;
DECLARE
@AccRef bigint, @SQLString nvarchar(2500)
set @sqlstring =
'
SELECT @Accref = CustomerAccountID
FROM OPENQUERY(ITBUY,
''
SELECT O.CustomerAccountID
FROM dbo.SS_ORDER O
WHERE O.OrderID = 1428
''
)
'
exec sp_executesql @sqlstring
Though if I take out the variable it runs;
DECLARE
@AccRef bigint, @SQLString nvarchar(2500)
set @sqlstring =
'
SELECT CustomerAccountID
FROM OPENQUERY(ITBUY,
''
SELECT O.CustomerAccountID
FROM dbo.SS_ORDER O
WHERE O.OrderID = 1428
''
)
'
exec sp_executesql @sqlstring
The OrderId is also going to be dynamically added. nyone have any ideas how I can get this running as a dynamic query..?
Thanks
Rolf
-
Rolf,
As i mentioned earlier that OpenQuery wont accept variables, why dont you give a try creating a dynamic query with your OrderId getting concatinated to a string stored in a variable and than run with EXEC inside the procedure..
Give it a try and i am sure it would work out for you.
Regards,
Ashish
-
just like this
declare @s1 as varchar(2000)
set @ORDERID = 12345
set @s1 = 'SELECT O.CustomerAccountID FROM dbo.SS_ORDER O WHERE O.OrderID = ' + @ORDERID
execute dbo.syp_DOLINKQUERY1 @s1
just run the procedure dbo.syp_DOLINKQUERY1 with your SQL-Statement in the varchar @s1,
first change in that procedure ''set @link='MFG' '' to your linkedserver 'ITBUY'
wilmos
-
Thanks for all the replies,
I understand how to get the dynamic filter variable into the openquery command just by adding it to the execution string.
MY problem now is that it seems OPENQUERY will not allow anything along the lines of
Select @myvar = cola
FROM OPENQUERY(LINKEDSERVERNAME,
'
SELECT cola
FROM dba.table
WHERE OrderID = @ORDERID
'
)
If this is the case then I will have to put the results into a temp table then extract them from there. I wanted to check that its not just USER ERROR =)
Thanks again
Rolf
-
Hehe, this is getting very retrictive!
I have just tried to use the datatype TABLE to extract the rows but of course I am now in the same position as before as I have to use the EXEC command to get the dynamic orderID into the openquery but then I cant use @myTableVariable unless I declare it within the EXEC block, but then I Cant use it elsewhere!!
This is proving much more troublesome than I expected.
Have resorted to using a TEMP TABLE along with EXEC to parse and execute the OPENQUERY
Any other ideas..? Thanks..
Rolf
-
Listen rolf.
When you are creating a dynamic sql with the orderid and all other stuff getting concatinated, please do something like this and try, you can see in the below statement that if you linkedservers are added then you create a statement of this sort, like what mosbachwil also tried to show you.
set @s1 = 'SELECT O.CustomerAccountID FROM ' + @servername + '.databasename.dbo.' + @yourtablename + ' O WHERE O.OrderID = ' + @ORDERID
And now try doing this. This is a hint and now you have to do the rest and it would be quick enough for you to resolve.
Regards,
Ashish
-
Hi Ashish,
OK let me try and explain more fully what my problem is.
Firstly I used the 4 point;
Select linkedserver.schema.database.table
syntax to select data from a linked server. This works fine and I can use a syntax such as Select @myvar1 = col1 from linkedserver.schema.database.table Where etc etc. Unfortunately this query was very slow..soemthing to do with authenticating the linked server perhaps.
So I then tried the OPENQUERY method. On a straight Select * from OPENQUERY( etc..) It is much faster than the above method.
However OPENQUERY will not take variables so it is neccessary to crate a dynamic string and then execute that. Thats fine and works HOWEVER if I am trying to select the results of the OPENQUERY select STRAIGHT into local variables like;
Select @myvar1 = col1
From
OPENQUERY(...)
Then I CANNOT use a string and the EXEC command as the variabels @myvar1 etc will be in the wrong scope; I will receive the error 'You must define @myvar1'..I would have to declare those local variables within the EXEC command string but then of course they will not be available within the rest of the SP.
Then I tried using a table type variable but that suffers exactly the same problems as above. As a final resort I am using a temp table to hold the results of the OPENQUERY that is executed via a dynamic string as in;
INSERT INTO #temptable
SELECT col1 from
OPENQUERY (...)
So it now works but I have to create a temp table, and then extract the data from that table into my local variables which might be an uneccessary step.
I apologise if I am teaching grandma to suck eggs at any point but I just wanted to write a fuller explanation of the process.
Cheers
Rolf
-
Right Rolf,
I agree to your arguments and even i liked the way you created the temp table and the whole process. Now i see what exactly you were looking for. And as per your requirements, i see this as the best step taken. But please take a note working with #temp table that, creating a temp table would lock your tempdb untill the whole process or table is not getting created. Well, you definaltely had to look for another option other than openquery and i think this creating a new table concept is good. And again if you dont see any problem for getting your tempdb locked untill you #t table is getting created than go for this, or else you can simply create another physical table and do your process.
Regards,
Ashish.
-
CREATE PROCEDURE [dbo].[linked_Server_Test]
@ORDERID bigint, @errorcode char(2) OUTPUT
DECLARE @Accref varchar(50), @sql nvarchar(4000)
SET @sql ='SELECT @Accref = CustomerID
FROM OPENQUERY(LINKEDSERVERNAME,
''
SELECT CustomerID
FROM dbo.SS_ORDER
WHERE O.OrderID = ' + CAST(@ORDERID as varchar) + ''')'
EXECUTE sp_executesql @sql, N'@Accref varchar(50) OUTPUT', @Accref OUTPUT
Far away is close at hand in the images of elsewhere.
Anon. -
Dear David,
Hi thanks for the response, this looks like a different approach again. I couldnt make complete sense of the post though.
I'm not familiar with the syntax used to call the sp_executeSQL, is the N'@accref....' a typo..?
Thanks
Rolf
-
No it is not a typo (the highlight and underline is the forum's doing)
Look up syntax in BOL
sp_executesql [@stmt =] stmt
[
{, [@params =] N'@parameter_name data_type [,...n]' }
{, [@param1 =] 'value1' [,...n] }
]
sp_executesql allows you pass and receive parameters to/from dynamic sql
As for my post, take it one step at a time
SELECT CustomerID FROM OPENQUERY(LINKEDSERVERNAME, 'SELECT CustomerID FROM dbo.SS_ORDER')
WHERE OrderID = 1
This will retrieve the CustomerID of OrderID 1 but will retrieve all the rows from the linked server before selecting the one row you require
DECLARE @OrderID bigint
SET @OrderID = 1
SELECT CustomerID FROM OPENQUERY(LINKEDSERVERNAME, 'SELECT CustomerID FROM dbo.SS_ORDER')
WHERE OrderID = @OrderID
This will do the same but using a parameter but will again retrieve all the rows from the linked server before selecting the one row you require
So, you need to pass the OrderID to the linked server to optimize the retrieval but as already mentioned you cannot use variables with OPENQUERY, so we use dynamic sql
DECLARE @OrderID bigint, @sql nvarchar(4000)
SET @OrderID = 1
SET @sql = 'SELECT CustomerID FROM OPENQUERY(LINKEDSERVERNAME, ''SELECT CustomerID FROM dbo.SS_ORDER WHERE OrderID='+CAST(@OrderID as varchar)+''')'
EXECUTE sp_executesql @sql
Next you want to get the CustomerID into a variable, so supply a variable and use it in the query
SET @sql ='SELECT @Accref = CustomerID
FROM OPENQUERY(LINKEDSERVERNAME,
''
SELECT CustomerID
FROM dbo.SS_ORDER
WHERE O.OrderID = ' + CAST(@ORDERID as varchar) + ''')'
EXECUTE sp_executesql @sql, N'@Accref varchar(50) OUTPUT', @Accref OUTPUT
Far away is close at hand in the images of elsewhere.
Anon. -
There is one bit of information still missing in this post. What type of linked server are we dealing with here? is it an SQL linked server? if yes then it may be easier to use 4 part names:
SELECT CustomerID FROM LINKEDSERVERNAME.DBNAME.dbo.SS_ORDER O where O.OrderID = @ORDERID
* Noel
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply