OPENROWSET used twice - 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)

OPENROWSET used twice

Author

Topic

SoftFox
Starting Member

42 Posts

Posted - 2008-02-14 : 10:09:49

I am using OPENROWSET to select fields from the result set of a stored proc twice, e.g.:

SELECT * FROM
(
SELECT * FROM
OPENROWSET('SQLOLEDB','Data Source=;Trusted_Connection=yes;',
'execute [headway DEVELOPMENT CONTROL].dbo.[PCReportPS2_Question2Selection_too]')
) AS R1

LEFT OUTER JOIN

(
SELECT * FROM
OPENROWSET('SQLOLEDB','Data Source=;Trusted_Connection=yes;',
'execute [headway DEVELOPMENT CONTROL].dbo.[PCReportPS2_Question2Selection_too]')
) AS R2
ON R1.ApplicationNumber = R2.ApplicationNumber

I get the following error when running this:

"[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute returned 0x80040e21: execute [headway DEVELOPMENT CONTROL].dbo.[PCReportPS2_Question2Selection_too][PROPID=DBPROP_SERVERCURSOR VALUE=True STATUS=DBPROPSTATUS_CONFLICTING]].
Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'SQLOLEDB'. The provider could not support a required property. The provider indicates that conflicts occurred with other properties or requirements."

It works so long as the stored proc (here called PCReportPS2_Question2Selection_too) does not have any other operations in it besides the SELECT .. statement. however i need to set some local variables in the proc before the SELECT statement, e.g.

CREATE PROC PCReportPS2_Question2Selection_too

DECLARE @FromDate DATETIME
SELECT @FromDate = GETDATE

SELECT .....

georgev
Posting Yak Master

122 Posts

Posted - 2008-02-15 : 08:32:14

Simplest solution is to write yourself a brand new, shiny, stored procedure that does the combination of work for you. It will be more efficient than using the OpenRowset() method too!

George
<3Engaged!

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-15 : 12:53:32

Is the error you posted with or without the local variables?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

SoftFox
Starting Member

42 Posts

Posted - 2008-02-18 : 09:38:38

the error is with the variables. it occurs when there is more than one command in ths SP so it is where the variables are being assigned values rather than the fact that there are variables in the SP. the same happens if there is another SELECT statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 11:56:21

[code]SELECT *
FROM OPENROWSET('SQLOLEDB', 'Data Source=;Trusted_Connection=yes;', 'exec [headway DEVELOPMENT CONTROL].dbo.[PCReportPS2_Question2Selection_too]') AS R1
LEFT JOIN OPENROWSET('SQLOLEDB', 'Data Source=;Trusted_Connection=yes;', 'exec [headway DEVELOPMENT CONTROL].dbo.[PCReportPS2_Question2Selection_too]') AS R2 ON R2.ApplicationNumber = R1.ApplicationNumber[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources