Weird error, I wonder if it's a bug

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Weird error, I wonder if it's a bug

Author

Topic

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2003-05-12 : 14:31:58

Ok, try this. First create a simple UDF:


USE Northwind
go
CREATE FUNCTION f_Test()
RETURNS INT
AS
BEGIN
  RETURN 1
END
go

Then run the following code, which works just fine (it's a little nonsensical, but it illustrates my point):


SELECT TOP 5
  CompanyName, OrderID
FROM Customers AS C
JOIN Orders AS O
  ON O.CustomerID = C.CustomerID
  AND dbo.f_Test() = 1

Now, make that whole thing a derived table:


SELECT *
FROM
(
SELECT TOP 5
  CompanyName, OrderID
FROM Customers AS C
JOIN Orders AS O
  ON O.CustomerID = C.CustomerID
  AND dbo.f_Test() = 1
) AS d

I get a nice error: "Could not find database ID 100. Database may not be activated yet or may be in transition." Weird. Plus, I can't find any database with ID=100. I'm running SQL Server 2000, sp2.

It looks having the UDF in a JOIN clause in the derived table causes the error, because if I move it from the JOIN to the WHERE, it works again:


SELECT *
FROM
(
SELECT TOP 5
  CompanyName, OrderID
FROM Customers AS C
JOIN Orders AS O
  ON O.CustomerID = C.CustomerID
WHERE dbo.f_Test() = 1
) AS d

So, if it's a bug, do I get a prize from Microsoft?

3P

==================================================
Tolerance is the last virtue of an immoral society. -- G.K. Chesterton

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-12 : 14:58:54

Great post ... sounds like a bug. At least it's pretty easy to work around and shouldn't cause much trouble.

I've never seen it.

know what's weird? put any UDF -- even one that doesn't exist -- and you get that same error:

SELECT *
FROM
(
SELECT TOP 5
CompanyName, OrderID
FROM Customers AS C
JOIN Orders AS O
ON O.CustomerID = C.CustomerID
AND dbo.Put_Anything_Here() = 1
) AS d

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-12 : 15:21:56

There are a lot of bugs to do with functions. Best to use them just for simple things.
At least this one gives an error rather than the incorrect data.

select * from(
select a.*
from a join b
on a.i = b.i
and asd.asd.fn() = 1
)
as a

also fails

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

chadmat
The Chadinator

1974 Posts

Posted - 2003-05-12 : 16:10:21

quote:
So, if it's a bug, do I get a prize from Microsoft?

Actually,

You do get a prize, worth $245 (At least I think that's still the price)....

A free support case!

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2003-05-12 : 16:34:01

quote:

Actually,

You do get a prize, worth $245 (At least I think that's still the price)....

A free support case!

Wow, and who says Microsoft isn't generous!

quote:

There are a lot of bugs to do with functions. Best to use them just for simple things.

Got that right. Now if I could only convice the other developers around here to follow suit. Many of them come from object-oriented (or pseudo object-oriented) languages and as soon as UDFs became available they went absolutely nuts. I feel like I'm preaching to a brick wall. We've got UDFs that SELECT and JOIN to thousands of records, and then those UDFs are used in other UDFs or stuck in the where clause of a query that itself returns thousands of records, and no one can figure out why their procedures are suddenly starting to bog down or they are getting nesting errors.

Thanks for the help, everyone.

3P

==================================================
Tolerance is the last virtue of an immoral society. -- G.K. Chesterton

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-12 : 16:43:57

Maybe I should add UDFs to my signature.
But they can make life a lot easier.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-12 : 17:25:22

quote:

You do get a prize, worth $245 (At least I think that's still the price)....

A free support case!

Can you trade in your strange and easily avoided bugs for support on something useful?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-12 : 17:31:57

UDF's are great. seriously, if you don't use them you are missing out. just don't overuse them. they are quite efficient.

It's pretty simple in my opinion:

Views = return data w/o needing parameters

Stored procs = perform an action on data, or return data but you need to do some complex work to get it, using parameters

UDFS = return data with parameters, w/o doing anything to the database objects

Just don't go crazy and try to make everything a UDF. If you have transactional systems like HR systems where every table is a date based table with history, getting all data for a point-in-time can be quite troublesome. UDF's make this really easy, because you can just write 1 per table and then join 'em all up and write nice reports. whereas in a stored procedure scenerio, you must use temp tables or cut and paste long SQL from various places into 1 big stored proc.

Nigel -- definitely never equate using a UDF with using a CURSOR !! My goodness !!! how upsetting.

- Jeff

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-05-12 : 19:19:46

No prize I'm afraid - it's already on the books

BUG: Error Message: "Could not find database ID..." Occurs When a User Defined Function is Referenced in the JOIN Condition of a Subquery
[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;819264[/url]

Don't let that disuade you though, if something new is posted here I can pass it up the chain and get it logged if it's a bug......so keep looking, it all helps

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

chadmat
The Chadinator

1974 Posts

Posted - 2003-05-13 : 01:12:23

quote:

Got that right. Now if I could only convice the other developers around here to follow suit. Many of them come from object-oriented (or pseudo object-oriented) languages and as soon as UDFs became available they went absolutely nuts. I feel like I'm preaching to a brick wall. We've got UDFs that SELECT and JOIN to thousands of records, and then those UDFs are used in other UDFs or stuck in the where clause of a query that itself returns thousands of records, and no one can figure out why their procedures are suddenly starting to bog down or they are getting nesting errors.

Wait until Yukon, then you will be able to write REAL UDTs in C# or any other CLR language.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2003-05-13 : 07:40:37

quote:

No prize I'm afraid - it's already on the books

BUG: Error Message: "Could not find database ID..." Occurs When a User Defined Function is Referenced in the JOIN Condition of a Subquery
[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;819264[/url]

Thanks Jasper! I was looking for this, but missed it. I was searching on the actual phrase of my error, "Could not find database ID 100", while the actualy support entry reads "Could not find database ID < database id>".

3P

==================================================
Tolerance is the last virtue of an immoral society. -- G.K. Chesterton

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources