Drop Function = denied 1

[buddafish]

Hello All,

I am simply attempting to rename or drop one of my UDF's in a database and am encountering the following error:

Msg 3729, Level 16, State 1, Line 2
Cannot DROP FUNCTION 'CONVERT_DATE_TO_PERIOD_FN' because it is being referenced by object 'DF__root_INTP__PRD_I__43D61337'.

I have no idea what object 'DF__root_INTP__...' is and can not locate any good info googling.

Any suggestions?

TIA
Scott

 

[SQLDenis]

looks like the function is used as a default in a column

try sp_depends FunctionName to find the table in questions

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

If you are using SQL 2000...

Open Query Analyzer
Show the Object Browser (Press F8 if you don't see it).
Drill down to the User defined function
Click the plus to the left of the function name.
Expand 'Dependencies'

This should show you all of the objects that are currently using the UDF.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[buddafish]

Thanks for the quick reply. sp_depends lists the following:

name: dbo.DF__root_INTP__PRD_I__43D61337
type: default (maybe c

not very useful to me! I will look for the object browser in 2005.

no table should be using this function. it is designed to support some formatting changes in other sprocs.

thanks again
scott

 

[SQLDenis]

what happens when you run this?

select table_name,column_name,* from information_schema.COLUMNS
where column_default like 'CONVERT_DATE_TO_PERIOD_FN%'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[buddafish]

Denis,

I return an empty table

 

[SQLDenis]

version 2

select name, object_name(parent_obj) as TableName,* from sysobjects
where xtype='d'
and name like 'DF__root_INTP__PRD_I__43D61337%'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[buddafish]

Well that brings back a nice row of info where i see an association with a table in the database.

I think i will attempt to drop the table, drop the function, re-create the table, then re-create the new function.

Thanks!

 

[gmmastros]

Denis beat me to it. Another way would have been...

Select object_name(id), * 
from   syscomments 
Where  Text Like '%[!]CONVERT_DATE_TO_PERIOD_FN[/!]%'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

you should be able to do

DROP DEFAULT 'DF__root_INTP__PRD_I__43D61337



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLDenis]

no tickmark

DROP DEFAULT DF__root_INTP__PRD_I__43D61337

instead of

DROP DEFAULT 'DF__root_INTP__PRD_I__43D61337

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[buddafish]

Yes,

I did drop the constraint (still not sure why my function became a constraint on the table), then recreated the function and I am back in business!

Again,

Thanks a million Denis and George

Scott

 

[ESquared]

This just supports my argument that all constraints should be explicitly named and not allowed to receive server-chosen names.

You can learn the name of the object using your default with this query:

select parent = p.name, parenttype = p.xtype, object = c.name
from sysobjects c
inner join sysobjects p on c.parent_obj = p.id
where c.name = 'DF__root_INTP__PRD_I__43D61337'