Alter Function - took long time ...

Question

Alter Function - took long time ...

davidku

Ten Centuries

Points: 1256
More actions
April 19, 2015 at 7:34 pm

#317533

Hi,
I created a scalar user defined function and when I try to modify / alter the script, it just takes long time to execute.
So, I decided to delete it and re-create but it failed and saying I do not have permission.
I logon as SA and for testing sake, I set the permission to grant everything.
Is there anything I missed out ?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 1

davidku (4/19/2015)
Hi,
I created a scalar user defined function and when I try to modify / alter the script, it just takes long time to execute.
So, I decided to delete it and re-create but it failed and saying I do not have permission.
I logon as SA and for testing sake, I set the permission to grant everything.
Is there anything I missed out ?
Thanks.

Can you find it in the Object Explorer after a "refresh"? If so, try the right-click and delete thing from there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

davidku Ten Centuries Points: 1256 More actions · Answer 2

The function can been seen on the Object Explorer. I tried to delete but can't. Strange that I login as SA and said no permission.

It has some dependencies to other calling store procedures but my intention originally is to Alter so I can change the script inside the function. Since it is not do-able, I am trying to delete and re-create.

Still failed 🙁 also, the schema binding is FALSE.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 3

It might just be a bad error message. MS is sometimes pretty good at that. You might have to drop the proc that uses it (after saving its code, of course) but an ALTER should work on the function.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

davidku Ten Centuries Points: 1256 More actions · Answer 4

Let me do another test by changing the store procedure to point to a new function. Then delete the old one. Better approach than deleting all the store procedure and re-create.

ScottPletcher SSC Guru Points: 101091 More actions · Answer 5

There could also be some type of locking, and thus blocking, on the underlying system tables. I've seen this happen when, for example, people do a "SELECT ... INTO new_table_name" in a non-tempdb db. But use of INFORMATION_SCHEMA in queries can also block system tables.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

davidku Ten Centuries Points: 1256 More actions · Answer 6

Ok, problem solved. I rebooted the database server.

As you guys suggested, locked processes ... it is correct. Something locked that function. Thanks for all your suggestions.