Database not found when using no where clause in an inner query.
-
This Does not work
It produces the error
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 102. Database may not be activated yet or may be in transition.
Select UserID,Email,FirstName,LastName
from ( SELECT t1.UserID, t1.Email,t1.FirstName ,t1.LastName
FROM
Table1 t1
INNER JOIN
Table2 t2
ON
t1.UserID = t2.UserID
inner join
Table3 t3
on
t3.UserID = t1.UserID
and t1.Active = 1
and dbo.AFunction(t1.Email) <> 'resultclass' ) t4 Where UserID NOT IN (Select
UserID from Table5 Where ListID=-1 )
But when the "and dbo.AFunction" is replaced with "where dbo.AFunction" It works fine. As shown below.
Select UserID,Email,FirstName,LastName
from ( SELECT t1.UserID, t1.Email,t1.FirstName ,t1.LastName
FROM
Table1 t1
INNER JOIN
Table2 t2
ON
t1.UserID = t2.UserID
inner join
Table3 t3
on
t3.UserID = t1.UserID
and t1.Active = 1
where
dbo.AFunction(t1.Email) <> 'resultclass' ) t4 Where UserID NOT IN (Select
UserID from Table5 Where ListID=-1 )
This seems like a bug, is it?
Could this be some sort of query optimization feature that went awry?
Thanks
Jon Shern
-
A few questions.
1 - Is this the actual code you are using or a demo for our viewing pleasure?
2 - What's the code of the function?
3 - Was there a database in a non production state (restoring, deattached,offline) the first time you ran this baby?
-
This is not the actual code it is changed for your viewing pleasure
.The code of the function just strips an email address of its account so just the domain is left.
The database has been in production state the entire time.
I am really not experiencing any problems, once I change the and to a where it works.
But I can change it back at anytime and the same error happens.
Thanks
-
I we see the actual code of the proc??
-
I would rather not, it my companies code, not mine.
All I did was replace the table names and the function name.
Plus I am really not asking for a fix, I just posting this as a possible bug.
-
Sorry but we can't help you then. Contact M$, but that ain't gonna be free.
-
I just thought it would be a nice fyi for someone and maybe start a nice dialogue about the query optimizer, etc.
But thanks for trying to help.
-
This is a known problem, involving a UDF in the join clause of a sub-query.
http://support.microsoft.com/default.aspx?scid=kb;en-us;819264
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply