can't alter function

In sqlserver 2000 I have a UDF which works fine but I want to make a
change to it. When I do an ALTER FUNCTION ... I get an error saying
that I can't alter the function because it is referenced by an object.
Is there any way around this? I reference the UDF in over 100 tables,
do I have to go to each table, remove the all references alter the
function then edit each 100 tables again? How clumsy can it be?

BarryBarry wrote:

Quote:

Originally Posted by

In sqlserver 2000 I have a UDF which works fine but I want to make a
change to it. When I do an ALTER FUNCTION ... I get an error saying
that I can't alter the function because it is referenced by an object.
Is there any way around this? I reference the UDF in over 100 tables,
do I have to go to each table, remove the all references alter the
function then edit each 100 tables again? How clumsy can it be?

Yep, that's it. You can generate a script for the drop/recreate of the
objects that reference the function, using the information from the
system tables. What kind of objects are we talking about
(defaults/check constraints/computed columns) ?

Razvan|||Defaults.

What would a script to look like to do this?

In Oracle I would ALTER TABLE XXX
Modify ( Column default null)

then write a script to fill in XXX from owner_tab_columns where column
exists

Thanks
Barry

Razvan Socol wrote:

Quote:

Originally Posted by

Barry wrote:

Quote:

Originally Posted by

In sqlserver 2000 I have a UDF which works fine but I want to make a
change to it. When I do an ALTER FUNCTION ... I get an error saying
that I can't alter the function because it is referenced by an object.
Is there any way around this? I reference the UDF in over 100 tables,
do I have to go to each table, remove the all references alter the
function then edit each 100 tables again? How clumsy can it be?

>
Yep, that's it. You can generate a script for the drop/recreate of the
objects that reference the function, using the information from the
system tables. What kind of objects are we talking about
(defaults/check constraints/computed columns) ?
>
Razvan

|||How does one reference a UDF in a table?

Jim|||I'm assigning it as a default The return from the function is my
default. I use it for User_id's

jim_geiss...@.countrywide.com wrote:

Quote:

Originally Posted by

How does one reference a UDF in a table?
>
Jim

|||Barry (bgt0990@.optonline.net) writes:

Quote:

Originally Posted by

What would a script to look like to do this?
>
In Oracle I would ALTER TABLE XXX
Modify ( Column default null)
>
then write a script to fill in XXX from owner_tab_columns where column
exists

ALTER TABLE tbl DROP CONSTRAINT <nameofconstraint>

Here is a query that will generate all necessary DROP commands:

SELECT 'ALTER TABLE ' + o.name + ' ALTER COLUMN ' + c.name +
' DROP CONSTRAINT ' + oc.name
FROM sysdepends d
JOIN sysobjects ofn ON d.depid = ofn.id
JOIN sysobjects oc ON d.id = oc.id
JOIN sysobjects o ON o.id = oc.parent_obj
JOIN syscolumns c ON o.id = c.id
AND c.cdefault = oc.id
WHERE ofn.name = '<yourfunction>'

You can also modify it to regenerate the command to restore the default.
Run that modified query, before you execute the result of the above. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi, Barry

Here is how you can generate a script to drop/recreate the defaults
that depend on a given function:

SELECT 'ALTER TABLE '+QUOTENAME(OBJECT_NAME(parent_obj))
+' DROP CONSTRAINT '+QUOTENAME(name)
FROM sysobjects WHERE xtype='D' AND id IN (
SELECT id FROM sysdepends
WHERE depid=OBJECT_ID('YourFunction')
)

SELECT 'ALTER TABLE '+QUOTENAME(OBJECT_NAME(parent_obj))
+' ADD CONSTRAINT '+QUOTENAME(o.name)
+' DEFAULT '+x.text
+' FOR '+QUOTENAME(c.name)
FROM sysobjects o INNER JOIN syscomments x ON o.id=x.id
INNER JOIN syscolumns c ON c.cdefault=o.id
WHERE o.xtype='D' AND o.id IN (
SELECT id FROM sysdepends
WHERE depid=OBJECT_ID('YourFunction')
)

There is a limitation regarding the size of the definition of the
default (x.text in the above query): if it's more than 4000 characters,
the above query won't work (because there would be multiple rows in
syscomments for the same id); but I'm sure nobody would create a
default with a definition longer than 100 characters to invoke a UDF,
so that should not be a problem.

Razvan

can't alter column to NOT NULL

If I run the following 3 statements (on sql server 2000):
create table foo (x nvarchar(128))
create unique index ix_foo on foo (x)
alter table foo alter column x nvarchar(128) not null
I get this error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'ix_foo' is dependent on column 'x'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN x failed because one or more objects access this
column.
Looking in the documentation it says "The altered column cannot be...Used in
an index, unless the column is a varchar, nvarchar, or varbinary data type,
the data type is not changed, and the new size is equal to or larger than
the old size."
Since the column is nvarchar, the data type is not changed, and the new size
is equal to the old size, this should be allowed.
Is the documentation just wrong or have I misunderstood something?
AndyAndy Fish wrote:

> Since the column is nvarchar, the data type is not changed, and the
> new size is equal to the old size, this should be allowed.
> Is the documentation just wrong or have I misunderstood something?
You need to drop the index, alter the column and then recreate the
index, that's the only way.
HTH,
Stijn Verrept.|||Disable the foreign key constraints / drop them. Alter the table (make
sure there are no NULL values in there). Activate recreate the foreign
key relationship.
HTH, Jens Suessmeyer.sql

Cannot transfer schemabound object - error

I am trying to alter the AdventureWorks database to transfer ownership from one schema to another...

Alter schema dbo transfer Person.CountryRegion

I get the following error..

"Cannot transfer a schemabound object."

Any help would be appreciated.Person.CountryRegion has foreign key references and an indexed view referencing it. So you need to resolve those before trying to transfer ownership. In this case, it looks like you have to drop the index on the view, perform the transfer and create the index back.