linked query to 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 2000 Forums

SQL Server Development (2000)

linked query to Oracle

Author

Topic

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2003-12-30 : 12:38:53

I'm trying to run the following linked server query against an Oracle 9 database:

SET @labsql = N'INSERT INTO TBL_LAB_STAGING_RESULTS(
	PATIENT_CDR_ID, 
	CPI_NUMBER, 
	ORDER_TEST_CODE, 
	RESULT_TEST_CODE, 
	RESULT_VALUE, 
	RESULT_UNITS, 
	HI_LOW_AA_FLAG, 
	RESULT_TYPE, 
	LAST_UPDATED, 
	LIS_ACCN_NBR, 
	RESULT_STATUS, 
	TEXT)

SELECT * FROM OPENQUERY(CDRQ, 
''SELECT 
	cr.PATIENT_CDR_ID, 
	p.pat_extern_id_orig, 
	so.ORDER_TEST_CODE, 
	cr.RESULT_TEST_CODE,
	cr.RESULT_VALUE, 
	cr.RESULT_UNITS, 
	cr.HI_LOW_AA_FLAG, 
	cr.RESULT_TYPE, 
	cr.LAST_UPDATED, 
	so.LIS_ACCN_NBR,
	cr.RESULT_STATUS, 
	ct.TEXT
FROM SERVICE_ORDER so, CLINICAL_RESULT cr, patient_ids p, CLINICAL_RESULT_TEXT ct
WHERE p.patient_cdr_id = cr.patient_cdr_id
and p.patient_cdr_id = so.patient_cdr_id
and so.patient_cdr_id = cr.patient_cdr_id
and so.filler_app = cr.filler_app
and so.filler_app_order_nbr = cr.filler_app_order_nbr
and so.patient_cdr_id = ct.patient_cdr_id(+)
and so.filler_app = ct.filler_app(+)
and so.filler_app_order_nbr = ct.filler_app_order_nbr(+)
and cr.LAST_UPDATED > TO_DATE(''2003-12-30 12:00:00'', ''yyyy-mm-dd hh24:mi:ss'')
AND cr.RESULT_STATUS IN (''F'',''C'',''P'')
and p.pat_record_status = ''A'''')'

exec sp_executesql @labsql

The resulting code gives me the error Server: Msg 170, Level 15, State 1, Line 38
Line 38: Incorrect syntax near '2003'.

When I take the output of the code via a PRINT statement (the select part), it runs perfectly in WinSQL.

Any thoughts on this? I've tried adding 2 extra single quotes around all the quoted items but I get an Oracle error saying that the table isn't found.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-12-30 : 13:38:21

Scott,

I think you need ONE extra quote around the quoted items, like TO_DATE('''2003-12-30...

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2003-12-30 : 13:44:34

That doesn't work either - same syntax error.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-30 : 13:46:34

A shot in the dark here: Is it possible that TO_DATE is required around SELECT cr.LAST_UPDATED ?

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2003-12-30 : 13:49:58

I tried adding the TO_DATE around it, but no difference. The problem is a syntax error on the SQL side - it's not even hitting Oracle at this point.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-12-30 : 13:55:16

So, outside of the sp_executesql, can you just run the statement starting at SELECT * FROM OPENQUERY...?

If so, then have you tried eliminating the last three lines of the WHERE that contain all those quotation marks and start rebuilding one at a time?

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2003-12-30 : 14:02:14

quote:
Originally posted by AjarnMark

So, outside of the sp_executesql, can you just run the statement starting at SELECT * FROM OPENQUERY...?

If so, then have you tried eliminating the last three lines of the WHERE that contain all those quotation marks and start rebuilding one at a time?

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]

I solved it -


SET @labsql = N'INSERT INTO TBL_LAB_STAGING_RESULTS(
	PATIENT_CDR_ID, 
	CPI_NUMBER, 
	ORDER_TEST_CODE, 
	RESULT_TEST_CODE, 
	RESULT_VALUE, 
	RESULT_UNITS, 
	HI_LOW_AA_FLAG, 
	RESULT_TYPE, 
	LAST_UPDATED, 
	LIS_ACCN_NBR, 
	RESULT_STATUS, 
	TEXT)

SELECT * FROM OPENQUERY(CDRQ2, 
''SELECT 
	cr.PATIENT_CDR_ID, 
	p.pat_extern_id_orig, 
	so.ORDER_TEST_CODE, 
	cr.RESULT_TEST_CODE,
	cr.RESULT_VALUE, 
	cr.RESULT_UNITS, 
	cr.HI_LOW_AA_FLAG, 
	cr.RESULT_TYPE, 
	cr.LAST_UPDATED, 
	so.LIS_ACCN_NBR,
	cr.RESULT_STATUS, 
	ct.TEXT
FROM SERVICE_ORDER so, CLINICAL_RESULT cr, patient_ids p, CLINICAL_RESULT_TEXT ct
WHERE p.patient_cdr_id = cr.patient_cdr_id
and p.patient_cdr_id = so.patient_cdr_id
and so.patient_cdr_id = cr.patient_cdr_id
and so.filler_app = cr.filler_app
and so.filler_app_order_nbr = cr.filler_app_order_nbr
and so.patient_cdr_id = ct.patient_cdr_id(+)
and so.filler_app = ct.filler_app(+)
and so.filler_app_order_nbr = ct.filler_app_order_nbr(+)
and cr.LAST_UPDATED > TO_DATE(''''2003-12-30 14:00:00'''', ''''yyyy-mm-dd hh24:mi:ss'''')
AND cr.RESULT_STATUS IN (''''F'''',''''C'''',''''P'''')
and p.pat_record_status = ''''A'''''')'

exec sp_executesql @labsql

Need a bunch of quotes to get it to pass syntactically. Also helps if you use the correct linked server name.

Thanks to all for your input - Scott

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-30 : 14:04:57

The following link shows a similar situation and it appears that the poster got it working by adding additional tick around the date value. If you apply Ajarnmarks debugging method maybe this will help get the date params working..

http://dbforums.com/arch/68/2002/7/420179

EDIT: Oh I am late to the party..

Good news Scott. Glad you got that working.

speedadkt
Starting Member

4 Posts

Posted - 2004-04-07 : 12:15:20

quote:
Originally posted by ehorn

The following link shows a similar situation and it appears that the poster got it working by adding additional tick around the date value. If you apply Ajarnmarks debugging method maybe this will help get the date params working..

http://dbforums.com/arch/68/2002/7/420179

EDIT: Oh I am late to the party..

Good news Scott. Glad you got that working.

I have a similar issue with the following query:

DECLARE @customerEmail char(50)
SET @customerEmail = 'test@123.com'
EXEC('
SELECT *
FROM OPENQUERY (HP3000,
''SELECT DISTINCT account_no FROM web.web_customers WHERE email1 = '''' + @customerEmail + '''''')'
)

Though I'm not getting the errors anymore (thanks to this thread) I'm not getting the expected 1 result back. I get 0 records. I have to wonder if the query is sending @customerEmail as the string value rather than the variables actual value of "test@123.com".

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-07 : 19:22:05

This is where the color-formatting in QA really comes in handy. If you look at that, you'll see that your variable @customerEmail is in red, which is the color that literal text gets formatted. If you take out one quote on either side of @customerEmail, it looks like it gets formatted correctly. Try it and see if it works.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources