error 63 ??

Executing
insert into [NewDB]..DataTable select * from DataTable
sporadically returns
(EOleException): Could not find database ID 63. Database may not be
activated yet or may be in transition
Gory details:
We are copying the contents of about 10 tables each time to another
identical database.
It fails, or doesn't, seemingly randomly, on any of the tables.
Copy the insert into QueryAnalyzer and it runs perfectly.
It will sometimes fail even if we only copy one table.
It can copy the first 6 tables, say, and fail with this error on the seventh
table.
It tends to go in streaks, it will fail all day, on different tables at
different times,
then go away completely only to come back hours later.
We've never seen this error when run from QA.
Commenting out a table it is failing on, typically lets it run correctly the
next time
and then it fails the following time on a different table.
A pause or a sleep between the table copies has no effect.
Once it starts to fail on a table it typically continues to fail on that
table until it is commented out.
We are testing a tool that creates a mimic DB, copies data to it.
Since this is test first, the DB is created fresh every time and destroyed
afterwards.
These tests have been running fine for months and just started displaying
this error this w.
--
TIA,
Brad.Brad,
Are you using DTS to copy the tables across? I wasn't sure what tool you
were using because you said "when we copy the query into Query Analyzer it
works fine." Does any of the target tables have a check constraint that use
s
a user defined function? Are you copying these across Linked servers?|||"Brad White" <bwhite at inebraska . com> wrote in message
news:O6Max7GbFHA.2996@.TK2MSFTNGP10.phx.gbl...
> Executing
> insert into [NewDB]..DataTable select * from DataTable
> sporadically returns
> (EOleException): Could not find database ID 63. Database may not be
> activated yet or may be in transition
>
Here is a typical table script for the new DB:
CREATE TABLE [DocTypeTestCriteria] (
[DocumentType] [smallint] NOT NULL ,
[TestOrder] [smallint] NOT NULL ,
[MICRType] [smallint] NOT NULL ,
[TestID] [smallint] NOT NULL ,
[TestValues] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RequireNext] [bit] NULL
) ON [PRIMARY]
I tried it with NULL instead of NOT NULL and it made no difference.
The main DB has referential integrity, etc. but the copy does not.
And here is the DB script: (path and DBname would be substituted before the
script is run)
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =
N'%DatabaseName%')
DROP DATABASE [%DatabaseName%]
GO
CREATE DATABASE [%DatabaseName%] ON (NAME = N'%DatabaseName%_Data',
FILENAME = N'%ArchivePath%\%DatabaseName%.mdf') LOG ON (NAME =
N'%DatabaseName%_Log', FILENAME = N'%ArchivePath%\%DatabaseName%_log.ldf')
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
exec sp_dboption N'%DatabaseName%', N'autoclose', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'bulkcopy', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'trunc. log', N'true'
GO
exec sp_dboption N'%DatabaseName%', N'torn page detection', N'true'
GO
exec sp_dboption N'%DatabaseName%', N'read only', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'dbo use', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'single', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'autoshrink', N'true'
GO
exec sp_dboption N'%DatabaseName%', N'ANSI null default', N'true'
GO
exec sp_dboption N'%DatabaseName%', N'recursive triggers', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'ANSI nulls', N'true'
GO
exec sp_dboption N'%DatabaseName%', N'concat null yields null', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'cursor close on commit', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'default to local cursor', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'quoted identifier', N'true'
GO
exec sp_dboption N'%DatabaseName%', N'ANSI warnings', N'false'
GO
exec sp_dboption N'%DatabaseName%', N'auto create statistics', N'true'
GO
exec sp_dboption N'%DatabaseName%', N'auto update statistics', N'true'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||"Cris_Benge" <CrisBenge@.discussions.microsoft.com> wrote in message
news:AE1DB944-B274-4B6E-A2D7-CCC2AB5F8950@.microsoft.com...
> Brad,
> Are you using DTS to copy the tables across?
No. Just the insert I specified.
Run from another executable through ADO.

> I wasn't sure what tool you
> were using because you said "when we copy the query into Query Analyzer it
> works fine."
I pull the actual query run from my log, copy it to QA and run it.

> Does any of the target tables have a check constraint that uses
> a user defined function?
No. Simple table with no constraints or refintegrity.

> Are you copying these across Linked servers?
Same server. Only different DB.
Could potentially be on a different partition, but right now, same drive.
Thanks for the response.
Brad.|||Stupid question - what service pack / SQL edition are you on? Can you show
me a sample of the offending INSERT statement?|||"Cris_Benge" <CrisBenge@.discussions.microsoft.com> wrote in message
news:600EF5C9-D0A9-4A89-B2E4-54A6490C68AA@.microsoft.com...
> Stupid question - what service pack / SQL edition are you on?
I don't see how that is a stupid question. 8:-)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
MDAC version, 2.8

> Can you show me a sample of the offending INSERT statement?
The format is
insert into [NewDB]..DataTable select * from DataTable
so an actual example, copied directly from the log, is
insert into [CurrentStatementArchive]..DocumentTypes select * from
DocumentTypes
We have 61 databases, this process creates 2 new, and the error complains
about
not finding the DB with ID 63, which would probably be the correct ID.
--
Thanks for sticking with me on this,
Brad White