OPENQUERY and variables

[sandra64]

I am trying to send an openquery statement to an ingres database. My code is :-


SELECT * FROM OPENQUERY(CCODDEV,'select surname,forename from person where emp_no ='+@eno+'')

where @eno is declared up above it. It's complaining about the + sign (Incorrect syntax near +) and I can't see why.

Any suggestions????

cheers
Sandra

 

[MeanGreen]

Just a guess here but why do you have the '' at the end of the statement? Are you trying to enclose your variable within quotes? If so I think you need it like this:

SELECT * FROM OPENQUERY(CCODDEV,'select surname,forename from person where emp_no ='''+@eno+'')

Again, this is just a guess.

Hope this helps.

 

[osjohnm]

Hi

Firstly, MeanGreen, when you query a linked server that is not a sql server you have to use either OPENQUERY or OPENROWSET and with OPENQUERY the actual select statement is seen as a string and is enclosed within single quotes.

Secondly, Sandra64, have you tried just leaving the @eno in the statement without the + or extra quotes?

SELECT * FROM OPENQUERY(CCODDEV,'select surname,forename from person where emp_no =@eno')

I double checked BOL and here it mentions that it doesn't support variables for its arguments.

Remarks
OPENQUERY does not accept variables for its arguments.

But since there is some sql in there you might get away with it. I'm at home at the moment but will attempt this against one of my Oracle linked servers in the morning.

Hope this helps?

John

 

[osjohnm]

Hi Sandra64

I'm back, you can do this by using sp_executesql to execute the dynamice sql and actually have the openquery as part of the sql string.

If this is in a stored proc then...

CREATE PROC TestOpenQuery
@eno nvarchar(20)
AS
DECLARE @SQL nvarchar(200)

SET @SQL = 'SELECT * FROM OPENQUERY(CCODDEV,''select surname,forename from person where emp_no = '''''+@eno+''''''')'
EXEC sp_executesql @SQL
GO

It seems that you have to use nvarchar, ntext or nchar with sp_executesql

Hope that helps you

John

 

[sandra64]

Meangreen and osjohnm,

Thanks for answering my question. John the sp_executesql worked, thanks very much

Sandra