?
Solved

Pass text to OpenQuery function

Posted on 2010-04-05
3
Medium Priority
?
1,121 Views
Last Modified: 2012-05-09
Trying to query a linked server using a parameter. Can't seem to pass a string to the OpenQuery function
For example:

Select @TSQL = 'select product_id_i, prod_stl_days_i from product where product_id_i=259287'

Select * From OpenQuery([MUNIPRIME],  'select product_id_i, prod_stl_days_i from product where product_id_i=259287' ) A
Select * From OpenQuery([MUNIPRIME], @TSQL ) A

The first select will work. The second select using the same string will give the error
Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near '@TSQL'.

Can someone tell me what's going on?
0
Comment
Question by:stopher2475
3 Comments
 
LVL 30

Accepted Solution

by:
Reza Rad earned 1600 total points
ID: 29833292
try this:

SET @sql_str = N'select * from OPENQUERY([MUNIPRIME], ''' + REPLACE(@TSQL, '''', '''''') + ''')'

PRINT @sql_str

EXEC (@sql_str)
0
 
LVL 72

Assisted Solution

by:Qlemo
Qlemo earned 400 total points
ID: 29833701
The Book Onlines states clearly that you cannot use any variables for OpenQuery arguments. You need to use dynamic SQL, as shown above.
0
 
LVL 2

Author Closing Comment

by:stopher2475
ID: 31711095
Thanks for the help. Found some other people with the same issue. They were pointed to here:
http://www.sommarskog.se/dynamic_sql.html#OPENQUERY
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

592 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question