Could not execute query against OLE DB provider 'MSDASQL'. [SQLSTATE 42000] (Error 7320)

SQLServerCentral is supported by Red Gate Software Ltd.

Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Could not execute query against OLE DB...

Could not execute query against OLE DB provider 'MSDASQL'. [SQLSTATE 42000]...

Rate Topic

Display Mode

Topic Options

Author

Message

Pat Henz

Posted Tuesday, July 07, 2009 8:00 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, February 05, 2010 9:09 AM
Points: 45, Visits: 59

Has anybody came across this error in SQL 2000? I have searched and haven't found much info on the following issue. Anybody have any information on this would be greatly appreciated.

This is in a stored procedure which runs in job that runs every fifteen minutes.

This is the error that I am getting:

Could not execute query against OLE DB provider 'MSDASQL'. [SQLSTATE 42000] (Error 7320) Transaction context in use by another session.] [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandText::Execute returned 0x80040e14]. [SQLSTATE 01000] (Error 7300). The step failed.

Here is the description of what is causing this error:

It is in a stored procedure within a Begin Tran on an Insert Statement with a Select statement which has a Case Statement inside the select then in the when another select statement going againt a view.

The piece of code causing the problem:
Begin Tran
insert into #TEMP
select PKG_TCK_NR, '
+ dbo.quotestring(@ID) + ' AS SER_NR,
case when CONVERT(smalldatetime, DT)
IN (Select top 2 srt_dt from dbo.VGetLast3BODRows )
THEN
CONVERT(smalldatetime, LD.OUB_SRT_DT)
Else ' + dbo.quotestring(@SRT_DT) + ' End as S_DT,
TS

======================================================
I found a workaround where I took the select statement out of the case statement and it worked fine. Also when I took the whole insert out of the Begin Tran it worked fine too.

Just asking if any has come across this?

Thanks.
Pat

Post #748552

Pat Henz

Posted Tuesday, July 14, 2009 9:17 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, February 05, 2010 9:09 AM
Points: 45, Visits: 59

Has anybody ever seen the above error I was getting?

Post #752772

« Prev Topic | Next Topic »

Permissions