SQL Server Oddness

I recently came across a rather odd bug in SQL Server 2000 which, although fixed in SP4, i had to find a work-around for as an immediate solution was required that couldn't wait for a scheduled server patching.

In my case it concerned a JOIN subselect which contained a JOIN using a user-defined scalar function as one of its predicates e.g.

...
LEFT JOIN (
   SELECT foo.col1, MIN(bar.col1)
   FROM foo
   INNER JOIN bar
      ON foo.col1 = bar.col2
      AND bar.col3 = dbo.fn_GetVal("derek")
   GROUP BY foo.col1 
)
...

which produced the rather unhelpful error:

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.

The work-around in this case was to move the predicate to the WHERE clause rather than the ON e.g.

...
LEFT JOIN (
   SELECT foo.col1, MIN(bar.col1)
   FROM foo
   INNER JOIN bar
      ON foo.col1 = bar.col2
   WHERE bar.col3 = dbo.fn_GetVal("derek")
   GROUP BY foo.col1 
)
...

• Knowledge Base Aricle