Could not find database ID 102. Database may not be activated yet or may be in transit

Hi (and sorry for sending this again, as a new post this time)
I get this error ocasionally in different parts of our application, but I
had never been able to reproduce it (as it seems to intermitent) until now
when I had a situation where this error always happens.
I've reduced the test script as much as I could to send to the list. I seems
that SQL Server doesn't like when you try to create a VIEW which has a JOIN
using a UDF and a subquery (even if not correlated) in the SELECT clause.
Please see full sample script bellow that reproduces the problem. This is
the error I get when I run it:
Server: Msg 913, Level 16, State 8, Line 4
Could not find database ID 102. Database may not be activated yet or may
be in transition.
Thanks,
Dallara
----
USE master
go
IF EXISTS (SELECT name FROM master..sysdatabases WHERE name =
'Organisation')
DROP DATABASE Organisation
go
CREATE DATABASE Organisation
go
USE Organisation
go
CREATE TABLE Company
(
CompanyId int IDENTITY (1, 1) NOT NULL,
CompanyName varchar(50) NOT NULL,
IsActive char(1) NOT NULL DEFAULT 'Y',
CONSTRAINT PK_Company PRIMARY KEY (CompanyId)
)
CREATE TABLE Contact
(
ContactId int IDENTITY (1, 1) NOT NULL,
CompanyRef int NOT NULL,
FullName varchar(25) NOT NULL,
Phone varchar(25) NULL,
CONSTRAINT PK_Contact PRIMARY KEY (ContactId),
CONSTRAINT FK_Contact_Company FOREIGN KEY (CompanyRef) REFERENCES Company
(CompanyId)
)
go
CREATE FUNCTION DummyFunction()
RETURNS int
AS
BEGIN
RETURN 1
END
go
CREATE VIEW DummyView
AS
SELECT
Company.CompanyName,
Contact.FullName,
(SELECT count(*) FROM Company WHERE IsActive = 'Y') As
NumberOfActiveCompanies
FROM
Company
JOIN Contact ON ContactId = dbo.DummyFunction()
go
----This looks a lot like the bug reported in MSKB 819264
<http://support.microsoft.com/defaul...kb;en-us;819264>
In this case, you could move the condition to the WHERE clause and use a
CROSS JOIN:
ALTER VIEW DummyView
AS
SELECT
Company.CompanyName,
Contact.FullName,
(SELECT count(*) FROM Company WHERE IsActive = 'Y') As
NumberOfActiveCompanies
FROM
Company
CROSS JOIN Contact
WHERE ContactId = dbo.DummyFunction()
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Dallara" <someone@.microsoft.com> wrote in message
news:udhF3lgwEHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi (and sorry for sending this again, as a new post this time)
> I get this error ocasionally in different parts of our application, but I
> had never been able to reproduce it (as it seems to intermitent) until now
> when I had a situation where this error always happens.
> I've reduced the test script as much as I could to send to the list. I
> seems
> that SQL Server doesn't like when you try to create a VIEW which has a
> JOIN
> using a UDF and a subquery (even if not correlated) in the SELECT clause.
> Please see full sample script bellow that reproduces the problem. This is
> the error I get when I run it:
> Server: Msg 913, Level 16, State 8, Line 4
> Could not find database ID 102. Database may not be activated yet or may
> be in transition.
>
> Thanks,
> Dallara
>
> ----
> USE master
> go
> IF EXISTS (SELECT name FROM master..sysdatabases WHERE name =
> 'Organisation')
> DROP DATABASE Organisation
> go
> CREATE DATABASE Organisation
> go
> USE Organisation
> go
> CREATE TABLE Company
> (
> CompanyId int IDENTITY (1, 1) NOT NULL,
> CompanyName varchar(50) NOT NULL,
> IsActive char(1) NOT NULL DEFAULT 'Y',
> CONSTRAINT PK_Company PRIMARY KEY (CompanyId)
> )
> CREATE TABLE Contact
> (
> ContactId int IDENTITY (1, 1) NOT NULL,
> CompanyRef int NOT NULL,
> FullName varchar(25) NOT NULL,
> Phone varchar(25) NULL,
> CONSTRAINT PK_Contact PRIMARY KEY (ContactId),
> CONSTRAINT FK_Contact_Company FOREIGN KEY (CompanyRef) REFERENCES Company
> (CompanyId)
> )
> go
> CREATE FUNCTION DummyFunction()
> RETURNS int
> AS
> BEGIN
> RETURN 1
> END
> go
> CREATE VIEW DummyView
> AS
> SELECT
> Company.CompanyName,
> Contact.FullName,
> (SELECT count(*) FROM Company WHERE IsActive = 'Y') As
> NumberOfActiveCompanies
> FROM
> Company
> JOIN Contact ON ContactId = dbo.DummyFunction()
> go
> ----
>
>