OpenQuery Syntax Using Variables

I have an openquery statement with a parameter embeded as a variable:

declare @.product varchar(3)

set @.product= 'ABC'

select * from openquery(SomeServer,'

SELECT Description, Size

FROM Products

WHERE

Group = ''XY'' AND

Code = ''' + @.product + '''')

When I run it I get the following message:

Msg 102, Level 15, State 1, Line 8

Incorrect syntax near '+'.

When I hard code the "Code" value, like so:

Code = ''ABC''')

...it works fine.

I am at a loss and would appreciate any help on this.

Thanks in advance

SQL Servant

Can you try SET QUOTED_IDENTIFIER OFF?

cheers,

Andrew

|||

I copied your code into Query Analyzer, same error too

It must be the quotation, do you want " or ' enclosing your @.product?

e.g. you want "Drink", or 'Drink'?

This is my code for some script that uses OpenQuery, I think I had to use EXEC to run it for the same problem you had (it won't take +)

Code Snippet

EXEC ('SELECT * FROM OPENQUERY(SERVER, ''SELECT * FROM TABLE WHERE Table_Id = ' + @.Table_Id_Str + ''')')

|||

Tried it... same error.

Thanks,

SQL Servant

Reply to --

Can you try SET QUOTED_IDENTIFIER OFF?

cheers,

Andrew

|||

The query sent to the server needs to have 'ABC' rather than "ABC".

I have used EXEC before and got the same error. Anyway, this query is part of an IF structure...

IF EXISTS (select * from openquery(...))

[do this ] ELSE [do that]

Thanks,

SQL Servant

Reply to -

I copied your code into Query Analyzer, same error too

It must be the quotation, do you want " or ' enclosing your @.product?

e.g. you want "Drink", or 'Drink'?

This is my code for some script that uses OpenQuery, I think I had to use EXEC to run it for the same problem you had (it won't take +)

|||

I have fixed the problem...

Apparently it is to do with scope and stuff...

If I put the query in a EXEC command then it works. So, the code becomes this:

EXEC('

select * from openquery(SomeServer,''

SELECT Description, Size

FROM Products

WHERE

Group = ''''XY'''' AND

Code = ''''' + @.product + ''''''')

')

In fact, I have put the entire IF structure (that this query is a part of) inside an EXEC command.

Over and out,

SQL Servant

|||

Wasn't that the idea of what I posted? ;-P

I think I deserve a star, hee hee

Anyway, please mark this thread as Answered

Glad it worked out for you

OpenQuery and passing variables

Anyone,

Is this possible?

I am connecting to a TeraData server via MS SQL 8.0 using the OpenQuery
statement. I need to pass a list of ever-changing deal numbers My
list of numbers are stored as a table on MS SQL.

So what I want is this

Select * from OpenQuery(TeraSrvr, "
Select Col1
, Col2
, Col3
>From Teradata_Table_1
Where Deal_no in (Select Deal_no from SQLTable)
")

Now I know that wont work, but How can I pass 184 Deal Numbers from my
SQL server to this query before it is sent to the Teradata server to be
done? Do I have to keep re-doing an in statement each month?

Anyone can help?

Doug(douglascfast@.hotmail.com) writes:
> So what I want is this
> Select * from OpenQuery(TeraSrvr, "
> Select Col1
> , Col2
> , Col3
>>From Teradata_Table_1
> Where Deal_no in (Select Deal_no from SQLTable)
> ")
> Now I know that wont work, but How can I pass 184 Deal Numbers from my
> SQL server to this query before it is sent to the Teradata server to be
> done? Do I have to keep re-doing an in statement each month?

To do it with OPENQUERY you would have to use dynamic SQL to build
the SQL statement, and also to execute the OPENQUERY thing, as
OPENQUERY does not take parameters of any kind of whatsoever.

But cannot you not use a linked server instead:

SELECT t.*
FROM TeraSrvr.db.catalog.Teradata_Table_1 t
WHERE t.Deal_no in (Select s.Deal_no from SQLTable s)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for this, I will chekc on the link bit below. Can you help me
with how to do this in Dynamic SQL. I can't find how to get a list of
my numbers into a Var so I can pass them on and use them in the
openquery statement.

Thanks again

Doug

Erland Sommarskog wrote:
> (douglascfast@.hotmail.com) writes:
> > So what I want is this
> > Select * from OpenQuery(TeraSrvr, "
> > Select Col1
> > , Col2
> > , Col3
> >>From Teradata_Table_1
> > Where Deal_no in (Select Deal_no from SQLTable)
> > ")
> > Now I know that wont work, but How can I pass 184 Deal Numbers from my
> > SQL server to this query before it is sent to the Teradata server to be
> > done? Do I have to keep re-doing an in statement each month?
> To do it with OPENQUERY you would have to use dynamic SQL to build
> the SQL statement, and also to execute the OPENQUERY thing, as
> OPENQUERY does not take parameters of any kind of whatsoever.
> But cannot you not use a linked server instead:
> SELECT t.*
> FROM TeraSrvr.db.catalog.Teradata_Table_1 t
> WHERE t.Deal_no in (Select s.Deal_no from SQLTable s)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(douglascfast@.hotmail.com) writes:
> Thanks for this, I will chekc on the link bit below. Can you help me
> with how to do this in Dynamic SQL. I can't find how to get a list of
> my numbers into a Var so I can pass them on and use them in the
> openquery statement.

Alas, in SQL 2000 the only safe way is to run a loop over the table.
There are some shortcuts, but they unrely undefined behaviour, so I
advise against such use.

For dynamic SQL in general, I have a longer article on the topic on
my web site: http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

OpenQuery & Quotes Syntax

I'm trying to select records within a date range from a linked server. If I
don't include the WHERE part and it's variables, my sql retrieves all
records. I also can hard code the WHERE criteria like "loadID=500".
SQL returns an error on my below code saying"
Incorrect syntax near '+'
What am I doing wrong with my commas and plus syntax? It looks correct, but
doesn't like my plus syntax for some reason.
CODE ***********
declare @.dtStartDate datetime, @.dtEndDate datetime
set @.dtStartDate = GetDate()
set @.dtEndDate = '20060701'
SELECT * FROM OPENQUERY(myLinkServer, 'SELECT loadID, loadDate
FROM load
WHERE loadDate >= ''' + convert(char(8), @.dtStartDate, 112) +
'''' + ' AND '
+ '' + 'loadDate <= ''' + convert(char(8), @.dtEndDate, 112) +
'''')On my system that string works fine. Using the current date for the
variables I get:
SELECT loadID, loadDate FROM load WHERE loadDate >= '20060515' AND
loadDate <= '20060515'
Are you certain that this is the line with the error?
Try to avoid building strings as parameters like this. Instead, assign the
string to a variable, and pass the variable as the parameter. You can
output the variable in order to make sure it contains the correct string.
You can then execute this string seperately in Query Analyzer to simplify
troubleshooting.
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%23jm8YzCeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> I'm trying to select records within a date range from a linked server. If
I
> don't include the WHERE part and it's variables, my sql retrieves all
> records. I also can hard code the WHERE criteria like "loadID=500".
> SQL returns an error on my below code saying"
> Incorrect syntax near '+'
> What am I doing wrong with my commas and plus syntax? It looks correct,
but
> doesn't like my plus syntax for some reason.
>
> CODE ***********
> declare @.dtStartDate datetime, @.dtEndDate datetime
> set @.dtStartDate = GetDate()
> set @.dtEndDate = '20060701'
> SELECT * FROM OPENQUERY(myLinkServer, 'SELECT loadID, loadDate
> FROM load
> WHERE loadDate >= ''' + convert(char(8), @.dtStartDate, 112)
+
> '''' + ' AND '
> + '' + 'loadDate <= ''' + convert(char(8), @.dtEndDate, 112)
+
> '''')
>|||SELECT * FROM OPENQUERY(myLinkServer, 'SELECT loadID, loadDate
FROM
load
WHERE
loadDate >= convert(char(8), getdate(), 112) +
AND loadDate <= convert(char(8), ''20060701'', 112)')
Could u please try with this?

openquery

BOL says that 'OPENQUERY does not accept variables for its
arguments'. So, I tried using something like:
Declare @.qry varchar(1000)
Set @.qry = 'select * from OPENQUERY
(<linked_server_name>, ''<query>'')'
exec (@.qry)
Would this work? Thanks.Yes.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:018301c3ad3f$50936e10$a401280a@.phx.gbl...
> BOL says that 'OPENQUERY does not accept variables for its
> arguments'. So, I tried using something like:
> Declare @.qry varchar(1000)
> Set @.qry = 'select * from OPENQUERY
> (<linked_server_name>, ''<query>'')'
> exec (@.qry)
> Would this work? Thanks.|||That is called Dynamic SQL and should work if the final syntax comes out
correct.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:018301c3ad3f$50936e10$a401280a@.phx.gbl...
> BOL says that 'OPENQUERY does not accept variables for its
> arguments'. So, I tried using something like:
> Declare @.qry varchar(1000)
> Set @.qry = 'select * from OPENQUERY
> (<linked_server_name>, ''<query>'')'
> exec (@.qry)
> Would this work? Thanks.