Using variables inside Openrowset
-
Hi
I hope someone can help me with the following query. I am trying to run an Openrowset query using variables.
To cut a long story short, I need this query to run against serveral linked servers to return a list of customers.
DECLARE @Servername VARCHAR (20)
DECLARE @login CHAR(2)
DECLARE @Password VARCHAR (20)
DECLARE @product CHAR (6)
DECLARE serverdbcursor cursor for
select datasource
from sysservers
where srvid > 0
open serverdbcursor
fetch next from serverdbcursor
into @Servername
while @@fetch_status = 0
begin
SET @login = 'user'
SET @Password = 'password'
SET @product = 'LMERC'
SET @Servername = RTRIM(@Servername)
SET @login = RTRIM(@Login)
SET @Password = RTRIM(@Password)
SET @product = RTRIM(@Product)
EXEC('SELECT * FROM OPENROWSET(''SQLOLEDB'', ''' + @ServerName + '''; ''' + @login + '''; ''' + @Password + ''',
''SELECT distinct tfn.Customer_id
FROM NNGretail.dbo.tfn_order tfn
WHERE tfn.product_code = ''' + @product + ''' '')')
fetch next from serverdbcursor
into @Servername
end
DEALLOCATE serverdbcursor
When this is run the following error occurs;
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'LMERC'.
I vave tried different syntax but drawing a blank as to what the solution is. If I replace the Where statement to:
WHERE tfn.customer_id = 23
It works perfectly.
Many thanks in advance for any help.
Paul Rowe
-
Using the PRINT command, thus:
PRINT 'SELECT * FROM OPENROWSET(''SQLOLEDB'', ''' + @ServerName + '''; ''' + @login + '''; ''' + @Password + ''',
''SELECT distinct tfn.Customer_id
FROM NNGretail.dbo.tfn_order tfn
WHERE tfn.product_code = ''' + @product + ''' + ' '')'
to see your dymanic SQL, the following is returned:
SELECT * FROM OPENROWSET('SQLOLEDB', 'myserver'; 'us'; 'password',
'SELECT distinct tfn.Customer_id
FROM NNGretail.dbo.tfn_order tfn
WHERE tfn.product_code = 'LMERC ' ')
As you can see an extra apostrophe (in Red) has been included by the way you have written the statement. If you make the following change to the WHERE clause:
WHERE tfn.product_code = ''' + @product + ' '')'
Your statement should
work.... Good luck (and don't forget to post the result!!)Ade
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply