SYMPTOMS
When you specify a JOIN condition in the FROM clause of a
subquery, and the JOIN condition references a user defined function, the SELECT
statement might fail, and you receive the following error message:
Server:
Msg 913, Level 16, State 8, Line 1
Could not find database ID <
database id>. Database may not be activated yet or may be in transition.
WORKAROUND
To work around the problem, rewrite the query so that the
JOIN condition that references the user defined function appears in the WHERE
clause of the subquery.
For example, if the user defined function is
defined as:
use Northwind
go
create function Func()
returns int as
begin
return (123)
end
go
The original query that causes the error is:
use Northwind
go
select
count(*) Test,
Orders.Employeeid
from
Orders left outer join
(select
count(*) Test1,
Employees.EmployeeId
from
Employees inner join EmployeeTerritories
on Employees.EmployeeID = EmployeeTerritories.EmployeeID
and datediff(d, getdate(), Employees.HireDate) <= dbo.Func()
group by
Employees.Employeeid
) as udfview
on Orders.Employeeid = udfview.Employeeid
group by
Orders.Employeeid
go
To avoid the problem, you can rewrite the query as:
use Northwind
go
select
count(*) Test,
Orders.Employeeid
from
Orders left outer join
(select
count(*) Test1,
Employees.EmployeeId
from
Employees inner join EmployeeTerritories
on Employees.EmployeeID = EmployeeTerritories.EmployeeID
where
datediff(d, getdate(), Employees.HireDate) <= dbo.Func()
group by
Employees.Employeeid
) as udfview
on Orders.Employeeid = udfview.Employeeid
group by
Orders.Employeeid
REFERENCES
For more information about how join conditions are specified
in a query, and the logical sequence of their execution, visit the following
Microsoft Web site:
Join
Fundamentals For additional
information about user-defined functions in SQL Server 2000, click the
following article number to view the article in the Microsoft Knowledge Base:
303343
SQL Server 2000 User-Defined Functions White Paper
For
additional information, click the following article numbers to view the
articles in the Microsoft Knowledge Base:
316541
FIX: Error 913 May Occur on a SELECT from a Linked Server That Uses a Check Constraint That References a User-Defined Function
299575 FIX: SELECT Joining Table with a Complex Subquery Having an IN clause in the JOIN Fails with AV