none
Function was temporarily not recognized for unknown reason

    Question

  • Very Strange!

    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,

    Dror

    Thursday, January 16, 2014 10:20 AM

All replies

  • 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()

    Thursday, January 16, 2014 10:25 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?

    Thanks,

    Dror

    Thursday, January 16, 2014 11:12 AM
  • 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, esquel@sommarskog.se
    Thursday, January 16, 2014 12:18 PM