|
|
Posted by Erland Sommarskog on 08/22/06 22:16
Martini (martini@online.ee) writes:
> I have quite disturbing situation where I am not happy about the way how
> SQL handles the query. Situation is related to using user function in
> INNER JOIN select. Although the problem occured in more complex
> situation the query can be simplified to following example with same
> results:
>...
> So the AND clause after INNER JOIN is an obvious mistake. That is not the
> problem, problem is how SQL reacts. On SQL 2000 SP4 the result for running
> the query is following error:
>
>
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 101. Database may not be activated yet or may
> be in transition.
>
> On SQL 2000 SP3 the result is even more dramatic - SQL service will
> consume 100% of all available processors and become unresponsive. Only
> solution is to restart either SQL service or the server.
>
> My question is if this self-destructive behavior of SQL server can be
> prevented by some configuration parameters or patches. I am a bit
> annoyed by the fact that developer can kill the server by little bit of
> poor programming that is syntactically acceptable for server.
Obviously this is a bug, but without a complete script that demonstrates
the problem, it's difficult to suggest workarounds. (Except as Alexander
says, avoid the UDF altogether.) It would also be interesting to see
if the problem appears on SQL 2005.
I did try to compose a repro from your post, but my query did not fail.
A complete repro script must either create tables itself, or refer
to known tables, such as system tables or tables in Northwind or pubs.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|