Drop function

Hey
I got this noob problem, I cannot drop a function.
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'NONUMBER')
DROP FUNCTION NONUMBER
This works fine on one of my other servers, first thing I though is
security. Since this code works on another server.
My permissions are the same, dbadmin , it's sa role. Both servers were
installed the same way so I can't see how I'm having this problem. So it's
not security my I think it can be those DB options where you specify with
sp_configure.
Ok, few minutes past I tried the sp_configure.
Still can't drop function. I'm sure it's just small setting.
Thanks in advance.
Mal> Hey
> I got this noob problem, I cannot drop a function.
> IF EXISTS (SELECT *
> FROM sysobjects
> WHERE name = N'NONUMBER')
> DROP FUNCTION NONUMBER
> This works fine on one of my other servers, first thing I though is
> security. Since this code works on another server.
> My permissions are the same, dbadmin , it's sa role. Both servers were
> installed the same way so I can't see how I'm having this problem. So
> it's not security my I think it can be those DB options where you
> specify with sp_configure.
> Ok, few minutes past I tried the sp_configure.
> Still can't drop function. I'm sure it's just small setting.
>
Do you get an error? If so, what error?
Or does nothing happen? Modify your script to this:
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'NONUMBER')
BEGIN
print 'Dropping function'
DROP FUNCTION NONUMBER
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'NONUMBER')
'Could not drop fucntion'
ELSE
'Function has been dropped'
END
ELSE
print 'Function Does Not Exist'
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Mal,
What is the error you are getting?

> IF EXISTS (SELECT *
> FROM sysobjects
> WHERE name = N'NONUMBER')
> DROP FUNCTION NONUMBER
The previous statement is checking for the existence of an object named
N'NONUMBER', but it is not checking that this object is a user defined
function. Check the diff with the following statement.
if exists (select * from information_schema.routines where routine_name =
N'nonumber' and routine_type = 'function)
...
AMB
"Mal" wrote:

> Hey
> I got this noob problem, I cannot drop a function.
> IF EXISTS (SELECT *
> FROM sysobjects
> WHERE name = N'NONUMBER')
> DROP FUNCTION NONUMBER
> This works fine on one of my other servers, first thing I though is
> security. Since this code works on another server.
> My permissions are the same, dbadmin , it's sa role. Both servers were
> installed the same way so I can't see how I'm having this problem. So it's
> not security my I think it can be those DB options where you specify with
> sp_configure.
> Ok, few minutes past I tried the sp_configure.
> Still can't drop function. I'm sure it's just small setting.
> Thanks in advance.
> Mal|||Sorry for not pasting ddl
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = 'NONUMBER')
begin
DROP FUNCTION NONUMBER
end
Code again, Error to follow
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'FUNCTION'.
Also tried grant access to my user to create function
UserName GroupName LoginName DefDBName UserID SUserID
-- -- -- -- -- --
dbo db_owner sa master 1 1
Grant create function to dbo
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'function'.
Same problem.
This is error desc. from BOL
170 15 Line %d: Incorrect syntax near '%.*ls'.
Thanks in advance .
"Alejandro Mesa" wrote:
> Mal,
> What is the error you are getting?
>
> The previous statement is checking for the existence of an object named
> N'NONUMBER', but it is not checking that this object is a user defined
> function. Check the diff with the following statement.
> if exists (select * from information_schema.routines where routine_name =
> N'nonumber' and routine_type = 'function)
> ...
>
> AMB
> "Mal" wrote:
>|||For starters, I would suggest two things. First, check the compatibility of
the database, using sp_dbcmptlevel; Only databases with level 80 or above
can do any function related DDLs. Second, check the owner of the fuction to
see if the DROP is executed by a user/ role having same or higher
previleges.
Anith|||Hey thanks for everyone's help
I did post a thanks but I used curseword in it so it got blocked I think.
Here what the results are.
sp_dbcmptlevel ->
Valid values of database compatibility level are 60, 65, or 70.
Thanks for help I'll have a look at compatibility in BOL and online .
Thanks
"Anith Sen" wrote:

> For starters, I would suggest two things. First, check the compatibility o
f
> the database, using sp_dbcmptlevel; Only databases with level 80 or above
> can do any function related DDLs. Second, check the owner of the fuction t
o
> see if the DROP is executed by a user/ role having same or higher
> previleges.
> --
> Anith
>
>