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

coperations07Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The server name is never in single quotes. It might be enclosed in double quotes. A select is like
select * from openquery(lnkdsrv, 'select * from Idontknow');

Open in new window

so your code needs to be
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
coperations07Author Commented:
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 + ' '')'
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Did you look at the @SQL content prior to executing it? I don't think the last correction is really resulting some rows.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

coperations07Author Commented:
I'm currently having an issue with a firewall blocking me from remote debugging... Is there another way to view @SQL content?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
coperations07Author Commented:
oh..it does execute fine and return expected results.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Sorry, just checked, and you were right - at least as long as @MRG contains a number and no string.
0
coperations07Author Commented:
No prob. Thanks for the help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.