weird error (database not activate or in transition)

[rravenn]

Here's a query that is part of some complex query with unions.

            SELECT Email,'Mng' as Role
            FROM resManager
                INNER JOIN d10task.dbo.UserGroups ug ON resManager.GroupID = ug.GroupID
                INNER JOIN resRequestResource ON resRequestResource.RequestID = @RequestID
                INNEr JOIN resResource ON resRequestResource.ResourceID = resResource.ResourceID [highlight]/*here*/[/highlight]
                INNER JOIN D10Task.dbo.Users Users ON ug.UserID = Users.UserID
            WHERE resManager.ActionID = 1 [highlight]AND (resResource.CatID = resManager.CatID
                    OR  (Inherit = 1 AND dbo.resCatIsDescendant(resManager.CatId,resResource.CatID) = 1))[/highlight]

It works normally the way it is written; however, when I move the highlighted code to join (highlighted place), I start getting the error about Dtabase id 112 being wrong cause it's not activated or is in transition.

Why is that?

 

[vongrunt]

resCatIsDescendant() is an UDF?

That's likely the cause of a problem...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[rravenn]

Yeah, it is. WHy is that the source of the problem tho?

 

[vongrunt]

Check KB834688 article from M$DN.

Btw. if UDF performs some SELECTs on DB tables performance will suffer in both cases (JOIN and WHERE).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[rravenn]

There's no other choice with ierarchical permsision. Well I could employ a lot of triggers to replicate them down the hierarchy and monitor it's changes but it just doesn't seem right.

Anyway, what's the actual problem with the error?

 

[vongrunt]

> There's no other choice with ierarchical permsision.

Well, not exactly... but that's another question if/when performance becomes unacceptable, right?

> Anyway, what's the actual problem with the error?

Purely internal thing... server/optimizer goes . With any of 3 conditions described in that KB article not present in a query, there are no problems. In your case join with UDF works after being written old-fashioned way (in WHERE clause).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)