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

    jon.shern@gmail.com

  • 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 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply