When you run an sp for the first time it generates a query plan.
Sounds like it is getting the database ID at this time and so failing when the database is dropped. It will be accessing the old database after it is renamed.
You can probably cure this by specifying 'with recompile' so that it resolves the objects on each execution.
If you are runing the query from query analyser then it must be caching the query plan somewhere.I do have included recompile option and as you said, it is caching in procedure cache and even after clearing cache also it is resulting same error. I have observed one thing is that, it is not happening with all tables which are included in my select statements in stored procedures. Another thing is after executing Job for 3 to 4 times only this problem is raising and for your information, both servers are having SQL7 SP2 and MDAC 2.5 SP1 (This configuration is forced to keep since most our major clients are running under same environment). Please let me know if you have any answer. Thanks for your kind help
Showing posts with label activated. Show all posts
Showing posts with label activated. Show all posts
Wednesday, March 7, 2012
Could not find database ID 102. Database may not be activated yet or may be in transition.
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/default...b;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
> ----
>
>
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/default...b;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
> ----
>
>
Labels:
activated,
application,
database,
error,
ihad,
microsoft,
mysql,
ocasionally,
oracle,
parts,
sending,
server,
sql,
time,
transition
Could not find database ID 102. Database may not be activated yet or may be in transition.
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/default.aspx?scid=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
> ----
>
>
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/default.aspx?scid=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
> ----
>
>
Labels:
activated,
application,
database,
error,
microsoft,
mysql,
ocasionally,
oracle,
parts,
sending,
server,
sql,
time,
transition
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
> ----
>
>
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
> ----
>
>
Subscribe to:
Posts (Atom)