Can't replace user function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can't replace user function

Author

Topic

ryan.gillies
Starting Member

27 Posts

Posted - 2012-08-07 : 11:32:36

Hi everyone

I've created a function in my database on SQL Server Express 2008, and I'd like to make a change to the way it calculates.

I've dropped it and re-created it with my revised script, but when I use it in a query the server continues to produce the old function's results.

Do I need to clear some sort of cache or remove a reference to it elsewhere?

Thanks, Ryan.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 11:40:53

first try running sp_help 'functionname'

and see if you can find new code, if not it means changes have not yet been applied

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ryan.gillies
Starting Member

27 Posts

Posted - 2012-08-07 : 11:51:32

I've done as suggested and I could see that the Created_datetime is the date the original was created.

I dropped the function, ran sp_help, then created the function and re-ran sp_help - it's showing the new creation date which I thought would resolve it, but alas the same problem still exists.

I've even set the function to return a simple int value of 1, and its still pulling the old calculated values, so its not a problem with my function's calculation. Any thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 11:56:48

where are you calling function from? is it scalar of table valued?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ryan.gillies
Starting Member

27 Posts

Posted - 2012-08-07 : 12:01:43

Its a scalar function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 12:14:20

if you're calling from procedure try removing procedure cached plan like


DECLARE @Handle varbinary(64)

SELECT @Handle = plan_handle
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'%Your procedure name%';


DBCC FREEPROCCACHE (@Handle);
GO

and then running it again
and if its from view try

sp_refreshview 'viewname'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-07 : 12:17:23

I hesitate to suggest this...

Check the database you created the function on..... Did you create a new version in Master for example?

I've made that mistake myself.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

ryan.gillies
Starting Member

27 Posts

Posted - 2012-08-07 : 12:51:28

After all of that despair and frustration, somehow an early RETURN performing the old calculation had actually snuck its way into the beginning of my function and was throwing it out.... just goes to show you should triple check your work before asking for help.

Thanks for the assistance anyway!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 12:57:20

np..you're welcome

it happens to all

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-08 : 04:06:33

and on the flip side -- performance must have been great!

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources