coperations07
asked on
openquery with variable server
Hi,
I need to run the same query on multiple linked servers. I've never passed the server name as a variable, but I'm trying to now so I don't have to paste the same query over and over. I can't seem to get the single quotes right. I've tried up to 10 single quotes on each side of the server variable, but it always comes back with an error. 5 quotes on each side looks right, but I get a msg: Incorrect syntax near ''. So I go up to 7 quotes on each side and I get a msg: Incorrect syntax near ''ServerName''. Is this possible to do? What am I missing?
I need to run the same query on multiple linked servers. I've never passed the server name as a variable, but I'm trying to now so I don't have to paste the same query over and over. I can't seem to get the single quotes right. I've tried up to 10 single quotes on each side of the server variable, but it always comes back with an error. 5 quotes on each side looks right, but I get a msg: Incorrect syntax near ''. So I go up to 7 quotes on each side and I get a msg: Incorrect syntax near ''ServerName''. Is this possible to do? What am I missing?
declare @SRV varchar(20)
set @SRV = '[USOSCS301\INST1]'
--Check each server/database until the merge/order is found. Get the nonsortable records, then get the confirmeddrops.
SET @SQL = 'SELECT * FROM OPENQUERY(''''' + @SRV + ''''',''
Select pd1.OrderID,pd1.SKU,pd1.ConfirmedDrops,pd2.Sequence,pd1.LabelField5,pd1.CartonID,pd1.WaveID,pd1.DropLocation,pd1.SorterID,pd2.Brand
From AG_SORTER0.dbo.productdistribution pd1 with (nolock)
Inner Join AG_SORTER0.dbo.productdiscription pd2 with (nolock)
On pd1.SKU = pd2.Product_Id And pd1.LabelField3 = pd2.Brand And pd1.WaveID = pd2.WaveID
Where pd1.ConfirmedDrops = 0
And pd2.prodhandcode = ''''N''''
And pd1.OrderID = ''''' + @MRG + ''''''')'
INSERT INTO @Results
EXEC (@SQL)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you look at the @SQL content prior to executing it? I don't think the last correction is really resulting some rows.
ASKER
I'm currently having an issue with a firewall blocking me from remote debugging... Is there another way to view @SQL content?
You should just need an arbitrary MSSQL server instance, and have to declare and set the var (@MRG) you are using manually. If I'm correct, you should see issues even without having to execute the generated SQL.
ASKER
oh..it does execute fine and return expected results.
Sorry, just checked, and you were right - at least as long as @MRG contains a number and no string.
ASKER
No prob. Thanks for the help!
ASKER
And pd2.prodhandcode = ''''N''''
And pd1.OrderID = ' + @MRG + ' '')'