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