I have an update statement that I'm running - for getting unit testing
results. For some reason this particular update statement returns an
error that I can't explain.
The error message is
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 102. Database may not be activated yet or may
be in transition.
The update statement is this
UPDATE
dbo.UnitTestResults
SET
ActualValue = (
SELECT
COUNT(*)
FROM
(select cur.ContractedSiteHistoryID as
Curr_ContractedSiteHistoryID,
prv.ContractedSiteHistoryID as Prev_ContractedSiteHistoryID,
cur.SiteSAK,
cur.ContractSAK,
cur.DEMLStatusCode as Curr_DEMLStatusCode,
cur.DEMLStatusReasonCode as Curr_DEMLStatusReasonCode,
prv.DEMLStatusCode as Prev_DEMLStatusCode,
prv.DEMLStatusReasonCode as Prev_DEMLStatusReasonCode,
cur.RecordEffectiveDate as Curr_RecordEffectiveDate,
cur.RecordExpiryDate as Curr_RecordExpiryDate,
prv.RecordEffectiveDate as Prev_RecordEffectiveDate,
prv.RecordExpiryDate as Prev_RecordExpiryDate,
cur.SourceServiceAccountNumber as Curr_ServiceAccountNumber,
prv.SourceServiceAccountNumber as Prev_ServiceAccountNumber,
cur.InvalidFlag as Curr_InvalidFlag,
prv.InvalidFlag as Prev_InvalidFlag,
cur.CustomerName
from dbo.ContractedSiteHistory cur
left outer join dbo.ContractedSiteHistory prv
on prv.SiteSAK = cur.SiteSAK
and prv.ContractSAK = cur.ContractSAK
and CAST(prv.RecordEffectiveDate as varchar(40)) + ':' +
CAST(prv.ContractedSiteHistoryID as varchar(12)) =
(
select max(cast(csh.RecordEffectiveDate as varchar(40)) + ':' +
cast(csh.ContractedSiteHistoryID as varchar(12)))
from ContractedSiteHistory csh
where csh.SiteSAK = cur.SiteSAK
and csh.ContractSAK = cur.ContractSAK
and (csh.RecordEffectiveDate < cur.RecordEffectiveDate)
or
(
csh.RecordEffectiveDate = cur.RecordEffectiveDate and
csh.ContractedSiteHistoryID < cur.ContractedSiteHistoryID
)
)
)) vt
WHERE
prev_DemlStatusCode IS NOT NULL
AND Curr_RecordEffectiveDate <
Prev_RecordEffectiveDate
)
WHERE
UnitTestID = 7
AND TestRunDate = DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
Simon WorthI think I've experienced this before. I'm just guessing here, but it may be
a
bug in SQL Server 2000. Anyway, the workaround that worked for me was to use
a local variable to store the data, and use the variable in the update.
In your case you'd need a table variable to store the results before you
issue an update. And judging by the query itself - maybe you should look at
the original business requirement and maybe change it.
BTW: does the subquery return any data if executed outside the update
statement?
ML|||yeah, the subquery works when executed on it's own. Just not in the
update statement.
I am going to split it out into a variable though, just for simplicity.
BTW - the subquery is actually a view, I just stuck the guts of the view
in there to show what it looked like. When I run it with the view name
in there I get an "internal SQL Server Error" message, when I put the
guts of the view in and run it, that's when I get the ID 102 detailed
message.
Simon Worth
ML wrote:
> I think I've experienced this before. I'm just guessing here, but it may b
e a
> bug in SQL Server 2000. Anyway, the workaround that worked for me was to u
se
> a local variable to store the data, and use the variable in the update.
> In your case you'd need a table variable to store the results before you
> issue an update. And judging by the query itself - maybe you should look a
t
> the original business requirement and maybe change it.
> BTW: does the subquery return any data if executed outside the update
> statement?
>
> ML|||Search this news group for "internal SQL error". I think we found another on
e
of those.
ML
