Microsoft KB Archive/819264

= BUG: Error Message: &quot;Could not find database ID...&quot; Occurs When a User Defined Function is Referenced in the JOIN Condition of a Subquery =

Article ID: 819264

Article Last Modified on 5/16/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



BUG #: 361162 (SQL Server 8.0)



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 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

