Force Modification of a User Defined Function

Is there a way to force the modification of a User Defined Function upon which depend other objects.

This is the message:

Msg 3729, Level 16, State 3, Procedure FunctionName, Line 22

Cannot ALTER 'FunctionName' because it is being referenced by object 'OtherObject'.

I have 48 objects related to the function and each time I need modify the function is a big pain.

Any help will be much appreciated.

Best Regards

ggpnetwork

Sorry, but AFAIK there is no switch to do so. If you have a computede column are something else bound to that function you have to drop and recreate the referencing object / Column or something else.

HTH; Jens Suessmeyer.|||We have currently ran into this issue but found the following solution helpful.

Originally, we were adding a scalar function to a computed column in a table. We received the same error message whenever we attempted to modify the scalar function.

However, if we created a view of the same table and included the scalar function as a column, we were able to modify the scalar without problems. That is to say, we were able to modify the scalar function definition (not the view result) without problems.

Hope this helps,
Daniel

Force Modification of a User Defined Function

Is there a way to force the modification of a User Defined Function upon which depend other objects.

This is the message:

Msg 3729, Level 16, State 3, Procedure FunctionName, Line 22

Cannot ALTER 'FunctionName' because it is being referenced by object 'OtherObject'.

I have 48 objects related to the function and each time I need modify the function is a big pain.

Any help will be much appreciated.

Best Regards

ggpnetwork

Sorry, but AFAIK there is no switch to do so. If you have a computede column are something else bound to that function you have to drop and recreate the referencing object / Column or something else.

HTH; Jens Suessmeyer.|||We have currently ran into this issue but found the following solution helpful.

Originally, we were adding a scalar function to a computed column in a table. We received the same error message whenever we attempted to modify the scalar function.

However, if we created a view of the same table and included the scalar function as a column, we were able to modify the scalar without problems. That is to say, we were able to modify the scalar function definition (not the view result) without problems.

Hope this helps,
Daniel

For Travis Lowdermilk

I dont know if anyone mentioned this, but I would like to have the ability to disable objects in a Data Flow Task

Travis,

I've split this into a seperate thread because it isn't relevant to the previous thread.

What is the scenario for wanting to do this? I don't understand why this would be required or how it would even be possible.

e.g. If you have a data-flow with 3 components, components 2 & 3 would not be able to execute if component 1 were disabled. The unit of execution in SSIS is a task, not a component.

-Jamie

|||

Jamie -

I apologize for posting in the wrong topic.

The reason I think it would be cool to have the ability to disable a particular object in a Data Flow Task is:

Scenario:

Let say I have a Multicast that splits the data to into two different directions:

Direction 1: goes to a JOIN and then eventually to an OLE DB DELETE Transform

Direction 2: goes through a Lookup transform and then to an OLE DB UPDATE or INSERT.

I may want to test just Direction 2 exclusively (maybe I know Direction 1 isn't going to work yet <maybe there was a change on the db side that makes this path invalid now>)

It would be nice to disable the path on the Direction 1 side and just focus on Direction 2.

Or maybe I want to just see if the LOOKUP (Direction 1 path) is redirecting the correct number of rows but I don't want to execute the INSERT OLE DB at the end of the path.

I have already created an extensive OLE DB INSERT statement that I don't want to have to recreate. Disabling it would be nice.

I am no expert at this sort of stuff, so it is quite possible that my sense of design is flawed and therefor I have to rely on deleting/cut and pasting items in the Data Flow Task to have them be ignored during debugging.

Hope that makes sense :-)

|||

Yeah that does kinda make sense. I guess that rather than disabling a component it'd be more accurate to say "stop the flow at this point".

I think its a valid request. You should request it at the feedback center.

In the meantime you can achieve the same thing by putting a data viewer on the path that you're not interested in. That'll effectively halt the flow.

-Jamie

|||

Thats a great idea.

Thanks!