Openquery and TSQL - 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

Transact-SQL (2000)

Openquery and TSQL

Author

Topic

daviddeldave
Starting Member

28 Posts

Posted - 2004-03-17 : 10:19:17

Does anyone know why the following SQL returns the error below it? It runs from Query analyser on a linked server.

declare @censusdate datetime
select @censusdate = '20040429'

select 
	A.DOPRSOUT Bookingnumber,
	A.MAXNO as MAXOFOPRSDATE
	from OPENQUERY(IBA_PAS,'SELECT DOPRSOUT,MAX(OPRSDATE) AS MAXNO
	FROM IBA.LIVE_OUTRSHAF 
	GROUP BY DOPRSOUT having to_date(max(OPRSDATE),''yyyyMMdd'') < '' + @CENSUS_DATE + ''') A

Error message returned is: Server: Msg 7330, Level 16, State 2, Line 4
Could not fetch a row from OLE DB provider 'MSDAORA'.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned 0x80040e07].

My email is david.mercer@bch.nhs.uk

David Mercer

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-17 : 10:56:50

Could this work?

declare @censusdate datetime
select @censusdate = '20040429'

select 
	A.DOPRSOUT Bookingnumber,
	A.MAXNO as MAXOFOPRSDATE
	from OPENQUERY(IBA_PAS,'SELECT DOPRSOUT,to_date(MAX(OPRSDATE),"yyyyMMdd") AS MAXNO
	FROM IBA.LIVE_OUTRSHAF 
	GROUP BY DOPRSOUT') A
	WHERE a.maxno < @censusdate

daviddeldave
Starting Member

28 Posts

Posted - 2004-03-17 : 11:41:50

Yes thanks!

David Mercer

daviddeldave
Starting Member

28 Posts

Posted - 2004-03-23 : 06:42:08

nope the where clause has to go inside the openquery function. This is what I have so far.

declare @censusdate char(8)
select @censusdate = '20040229'

select 
		ltrim(a.doprsout) bookingnumber,
		a.maxno as maxofoprsdate
		from openquery(iba_pas,'select a.doprsout,max(oprsdate) as maxno
		from iba.live_outrshaf a, iba.live_patcodes b
		where a.oprsreas = b.acode (+) and
		b.tcode = ''RR'' and b.tcindc1 <> ''H'' and a.oprsdate <=  '' + @censusstring) + ''
		group by doprsout') a

which gives the @censusstring with no single quotes and I want single quotes around it. When I put another one on either end (ie <= ''' + @censusstring) + ''') I get the following error message.

Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near '+'.

Been struggling on this for ages and would appreciate your help.

David

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-23 : 07:16:49

David,

One of those parenthesis is out of place.

<= ''' + @censusstring) + '''

Try

<= ''' + @censusdate + '''

daviddeldave
Starting Member

28 Posts

Posted - 2004-03-23 : 07:47:00

Fixed that problem so the new code says

declare @censusdate char(8)
select @censusdate = '20040229'

		select 
		ltrim(a.doprsout) bookingnumber,
		a.maxno as maxofoprsdate
		from openquery(iba_pas,'select a.doprsout,max(oprsdate) as maxno
		from iba.live_outrshaf a, iba.live_patcodes b
		where a.oprsreas = b.acode (+) and
		b.tcode = ''RR'' and b.tcindc1 <> ''H'' and a.oprsdate <= ''' + @censusdate + '''
		group by doprsout') a

but still get Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near '+'.

David Mercer

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-23 : 07:51:54

Couple of questions:

What is (+)

What happens if your run

declare @censusdate char(8)
select @censusdate = '20040229'

		select 
		ltrim(a.doprsout) bookingnumber,
		a.maxno as maxofoprsdate
		from openquery(iba_pas,'select a.doprsout,max(oprsdate) as maxno
		from iba.live_outrshaf a, iba.live_patcodes b
		where a.oprsreas = b.acode and
		b.tcode = ''RR'' and b.tcindc1 <> ''H'' group by doprsout') a

daviddeldave
Starting Member

28 Posts

Posted - 2004-03-23 : 07:56:00

Your query works fine when I run it. The (+) is the oracle equivalent to a left join. The openquery is reading from a linked server which uses oracle. The query I showed is part of a large Stored Procedure hence I need the parameter to be part of the openquery function. Thanks for your help.

David Mercer

daviddeldave
Starting Member

28 Posts

Posted - 2004-03-23 : 07:58:52

when I hard code the date in it works fine. This works.

		select 
		ltrim(a.doprsout) bookingnumber,
		a.maxno as maxofoprsdate
		from openquery(iba_pas,'select a.doprsout,max(oprsdate) as maxno
		from iba.live_outrshaf a, iba.live_patcodes b
		where a.oprsreas = b.acode (+) and oprsdate <= ''20040229'' and
		b.tcode = ''RR'' and b.tcindc1 <> ''H'' group by doprsout') a

David Mercer

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-23 : 08:15:15

Yea, I just remembered that OPENQUERY does not accept parameters: You will have to use Dynamic SQL to build up your statement and then execute it. The following link demonstrates this.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31536

speedadkt
Starting Member

4 Posts

Posted - 2004-04-07 : 12:18:56

quote:
Originally posted by ehorn

Yea, I just remembered that OPENQUERY does not accept parameters: You will have to use Dynamic SQL to build up your statement and then execute it. The following link demonstrates this.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31536

I have a similar issue with the following query syntax:

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 + '''''')'
)

I have to wonder if @customerEmail is being sent as the value rather than "test@123.com". Though I'm not getting any errors, I"m also not getting any results back.

speedadkt
Starting Member

4 Posts

Posted - 2004-04-07 : 15:27:17

Forget, I got it:

DECLARE @customerEmail char(50)

DECLARE @sqlStr char(1000)

SET @customerEmail = 'TEST@123.COM'

SET @sqlStr ='SELECT * FROM OPENQUERY(HP3000,''SELECT DISTINCT account_no FROM web_customers WHERE email1=''''' + RTRIM(CONVERT(varchar,@customerEmail)) + ''''''')'

EXEC (@sqlStr)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources