Openquery - Oracle - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Openquery - Oracle

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-01-24 : 17:26:57

-- I am trying to execute the openquery like below.. bu tkeep getting an error Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1'. what am i doing wrong?

declare @startdate varchar(9)
declare @enddate varchar(9)
declare @qms_query varchar(8000)
declare @full_query varchar(100)

set @startdate = '1-Oct-06'
set @enddate = '30-Oct-06'

Set @qms_query = ' select * from ra.lab_result where LAB_RESULT_DATE BETWEEN '''+@startdate+ ''' and ''' + @enddate + ''' '

Set @full_query = 'SELECT * FROM OPENQUERY(ORACLE_RA, ''' + @qms_query + ''')'

exec (@full_query)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-24 : 18:00:48

declare @full_query varchar(1000)

Peter Larsson
Helsingborg, Sweden

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-01-24 : 18:29:48

thanks.. but i am getting the same error

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-01-24 : 18:51:14

this query
Set @qms_query = ' select * from ra.lab_result where LAB_RESULT_DATE BETWEEN ''''+@startdate+ '''' and '''' + @enddate + '''' '

print:

select * from ra.lab_result where LAB_RESULT_DATE BETWEEN '1-Oct-06' and '30-Oct-06'

how can I put double quotes around the date?

select * from ra.lab_result where LAB_RESULT_DATE BETWEEN ''1-Oct-06'' and ''30-Oct-06''

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-24 : 19:43:04

Add more single quotes!

declare @startdate varchar(9)
declare @enddate varchar(9)
declare @qms_query varchar(8000)
declare @full_query varchar(1000) 

set @startdate = '1-Oct-06'
set @enddate = '30-Oct-06'


Set @qms_query = ' select * from ra.lab_result where LAB_RESULT_DATE BETWEEN ''''' + @startdate + ''''' and ''''' + @enddate + ''''' '

Set @full_query = 'SELECT * FROM OPENQUERY(ORACLE_RA, ''' + @qms_query + ''')'

exec(@full_query)

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-01-25 : 04:04:14

We normally do this: (because we find it easier to build/read/debug the underlying query):

Set @qms_query = ' select * from ra.lab_result where LAB_RESULT_DATE BETWEEN ''' + @startdate + ''' and ''' + @enddate + ''' '

Set @full_query = 'SELECT * FROM OPENQUERY(ORACLE_RA, ''' + REPLACE(@qms_query, '''', '''''') + ''')'

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources