ODBC and Variables

[sandra64]

I'm linking into an ingres database and whilst I got the openquery to work (with the help of someone on this site). I am now trying to do a like query where the wild card is a percentage sign. I've tried various things but can't get it to work. I'd appreciate any ideas, I've enclosed the query. The query below works for equals but I want to say where forename like @fname with a % on the end i.e Al%

declare @sname char(24)
declare @fname char(18)
declare @SQL nvarchar(2000)
set @sname = 'Evans'
set @fname = 'Al'

SET @SQL = 'insert into tt_appsp(surname,forename)SELECT surname,forename FROM OPENQUERY(??????,''select surname,forename from ????? where surname = '''''+@sname+''''' and forename = '''''+@fname+''''' '')'
EXEC sp_executesql @SQL

thanks

 

[tlbroadbent]

I'm not familiar with Ingres so I can't say for sure what the syntax should be. However, you need to keep in mind that OpenQuery runs the query on the linked server so the SQL statement must conform to the Ingres syntax. You may need to use "*" rather than "%" for the wildcard. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[sandra64]

Terry,

thanks for getting back to me. The syntax in ingres is % for the wildcard, however, it's where to put the % in the openquery that I'm having the problem with.
I've tried the folling i.e. and forename like

SET @SQL = 'insert into tt_appsp(surname,forename)SELECT surname,forename FROM OPENQUERY(????,''select surname,forename from person where surname = '''''+@sname+''''' and forename like '''''+@fname+%''''' '')'
EXEC sp_executesql @SQL


SET @SQL = 'insert into tt_appsp(surname,forename)SELECT surname,forename FROM OPENQUERY(????,''select surname,forename from person where surname = '''''+@sname+''''' and forename like '''''+@fname+'+%+'''' '')'
EXEC sp_executesql @SQL

Thanks for your help, I'll carry on trying...

 

[tlbroadbent]

Try it this way. This should append the wildcard to the @fname value.

SET @SQL = 'insert into tt_appsp(surname,forename) SELECT surname,forename FROM OPENQUERY(????, ''select surname,forename from person where surname = '''''+@sname+''''' and forename like '''''+@fname+'%'''' '')'

EXEC sp_executesql @SQL
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[sandra64]

Sorry about this Terry,

I did as you suggested and whilst it didn't complain about the syntax and gave the message The command(s) completed successfully. It did not put anything in the table. I'll have another look to see if I'm missing something but I don't think so... thanks for all your help