Local 2000 Account And UDF's - SQL Server Forums

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)

Local 2000 Account And UDF's

Author

Topic

allend2010
Starting Member

28 Posts

Posted - 2003-06-04 : 16:13:40

Hello:

I just installed the MSDE that comes with DOT Net so I could run a local instance of SQL Server but I just noticed that User Defined Functions are not available at all? Does anybody know a way I could get it set up.

Thanks in advance,
Allen D.

chadmat
The Chadinator

1974 Posts

Posted - 2003-06-04 : 16:18:33

What do you mean they are not available? What happens if you run a CREATE FUNCTION statement?

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-04 : 16:22:22

UDFs are available in MSDE 2000. Try running this stupid function:

CREATE FUNCTION udf_TestFunction (@Something INT)
RETURNS INT
AS
BEGIN
DECLARE @Something2 INT
SELECT @Something2 = 1 + @Something

RETURN @Something2
END

Then run this:
SELECT dbo.udf_TestFunction(4)

You should get 5 back.

Do you get an error?

Tara

allend2010
Starting Member

28 Posts

Posted - 2003-06-04 : 16:23:04

Basically, what happens is if i try to create a function with the query analyzer, I get error:

CREATE FUNCTION GetRate(@RateTable VARCHAR(20), @Zone INT, @Weight INT)
RETURNS MONEY
AS

BEGIN
DECLARE @dynSQL VARCHAR(1024)
DECLARE @Rate MONEY

SET @dynSQL = 'SELECT Zone' + @Zone + ' FROM ' + @RateTable + ' WHERE Weight = ' + @Weight + ';'
SET @Rate = EXECUTE (@dynSQL)

RETURN (@Rate)
END

GO

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'FUNCTION'.

When I look in Enterprise Manager under the databases I can see the usual optsions such as Tables, Views, Stored Procedures and User Defined Data Types but not User Defined Functions.

Thanks,
Allen D.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-06-04 : 16:30:24

Can you post the results of (run in the database you were trying to create the function)


declare @d sysname
set @d = db_name()
exec sp_dbcmptlevel @d

select @@version

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

allend2010
Starting Member

28 Posts

Posted - 2003-06-05 : 09:18:41

Thanks everyone for your help. It turns out that even though I had SQL 2000 installed for whatever reason the version of MSDE I had was for version 7 so I upgraded to 2000 version sp2 and it works fine now :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources