SQL Server 2000 UDFs

[dhqube]

I am trying to write a User Defined Function in SQL Server 2000.

Unfortunately my copy doesnt seem to want to support them.
I have opened the on line help and pasted in the following example

CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters.
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END

If I run this I get the following error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FUNCTION'.
Server: Msg 137, Level 15, State 1, Line 8
Must declare the variable '@CubeLength'.

I have tried following Terry Broadbents advice and qualifying the name of the function with the user and even the database but no luck.

I am using SQL 2000 version 8.00.194

If anybody has any ideas, or could possibly cut the example above and run it on their owm machine and see what happens I would be very grateful, as I have a business requirement for which a UDF would be perfect - if only I could get the damn things to work.

 

[JamesLean]

Check that your database is not running at a lower compatibility level. Run this statement:

sp_dbcmptlevel '<db_name>'

This should return a value of 80 if running at 2000 level. If it doesn't, use that same SP to change it to 80:

sp_dbcmptlevel '<db_name>', 80

--James

 

[dhqube]

James - thanks for that.

I tried doing it and it was set to 70.

So tried to set it to 80 as you suggested and got the following:


Server: Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 90
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]

Valid values of database compatibility level are 60, 65, or 70.

 

[JamesLean]

It sounds like your server is only running SQL 7. Remember you may have SQL 2000 client tools installed on your local machine but the server may still be version 7. --James

 

[dhqube]

James - you are quite right - we have 4 servers here
and some silly b*gger has left one as SQL7, even though all the clients have been upgraded to SQL2000.

I tried the same function (the one from help) on the SQL2000 server and it worked fine (no dbo. or database name needed).

Thanks for your help on this. Just goes to show that the on-line help is part of the client, not the server.

 

[JamesLean]

Yes, if you installed BOL as part of the 2000 client tools, then that's what you'll see through QA help.

I have the same situation as you (2000 client tools but 7 server). I have kept both versions of BOL installed on my machine. I use 2000 BOL for most things but if I find something that doesn't seem to be working correctly I can look it up in 7 BOL to double-check it hasn't changed since then.

You may find this useful now you know you have a different server version! --James

 

[RedRocket]

I cut and paste the same code and works fine.
I have SQL Version 8.00.194 (SQL 2000 Standard Edition) running on a SQL 2000 box.

I used the function as such:

select dbo.CubicVolume(2,3,3) to get answer of 18.

Our SQL Server is case and accent sensitive also so small typos is often the cause.

Do you have EXEC permission?

Good luck.