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)

I 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:
Show quoteHide quote

Search this news group for "internal SQL error". I think we found another one
of those.


ML

See also http://support.microsoft.com/kb/834688

Calculated fields with Days/Dates
Automating database changes
Dangers of update statement
[OT] SQL Humor
estimate size of table based on number of rows