I ran a stored procedure on a database and it failed because sql server didn't recognize a fucntion that existed in the databse.
The error was: "invalid object ...<function-name>"
But the fuction exists!
I then run "sp_helptext <funtion-name>" and the function was there!
Then i ran procedure again and it worked fine!
it's as if sp_helptext "solved" the problem.
Why did this happen and how do i prevent this from happening again (as it caused a production error).
Thanks in advance,
This is strange!
May be you missed your schema part for the first time execution?
Please show us how did you execute?
create function dbo.fn_test() returns int as Begin return 1 End Select fn_test() --it will throw an error "'fn_test' is not a recognized function name." Select dbo.fn_test()
- Edited by Latheesh NKMVP Thursday, January 16, 2014 10:29 AM
Procedure was called over RPC and failed.
I then ran procedure in management studio and it failed returning the invalid object error message.
Then I just merely ran sp_helptext and all of a sudden everything was ok.
The function called by the stored proc which was not recognized is an inline table valued function.
I can also say that there was a new version update just recently - the stored procedure was not modified but the function was... There was also no sql server restart after the version update.
Can something explain what happened?
The explanation that is closest at hand is that someone was fooling around and dropped the function and then restored it. You could check in sys.objects if any of the columns create_date or modify_date match the time when you looked at this.
Rather than dropping/recreating someone may have renamed the function or moved it to a different schema for a while. I am not sure whether this affects modify_date.
Erland Sommarskog, SQL Server MVP, email@example.com
- Proposed as answer by Fanny LiuMicrosoft contingent staff, Moderator Monday, January 27, 2014 12:35 PM