Link to home
Start Free TrialLog in
Avatar of coperations07
coperations07Flag for United States of America

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?
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)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of coperations07

ASKER

Thanks! That got me going. I had to play with the quotes around the N and MRG, but nothing major. This is what ended up working for me there:      
 And pd2.prodhandcode = ''''N''''
 And pd1.OrderID = ' + @MRG + ' '')'
Did you look at the @SQL content prior to executing it? I don't think the last correction is really resulting some rows.
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.
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.
No prob. Thanks for the help!