Post reply

Get a "Invalid object name ''dbo.fn_GetClosestZip'' error when executing function

  • September 14, 2005 at 6:13 pm

    #89039

    I am trying to execute the below function and i'm getting a

    Invalid object name 'dbo.fn_GetClosestZip'. error.

    I have tried to run sp_Helptext on the function name and it returns valid information.   I see the function in Enterprise manager.  I'm in the corrrect database when i run the select statement as well.  I checked the permissions on the function and have ASPNET, public users under it and gave them all "select" rights.  I have admin rights on the box. 

    Any ideas??

    select dbo.fn_GetClosestZip(98052)

  • September 14, 2005 at 6:57 pm

    #589980

    check the owner of the object

    Kindest Regards,

    Amit Lohia

  • September 14, 2005 at 7:45 pm

    #589987

    Grant the users Execute permission and see if that does it...it would also be good practice to use the fully qualified name starting with the db name...

    here's all the "permissions info" from BOL for reference...

    "You must have been granted CREATE FUNCTION permissions to create, alter, or drop user-defined functions. Users other than the owner must be granted EXECUTE permission on a function (if the function is scalar-valued) before they can use it in a Transact-SQL statement. If the function is table-valued, the user must have SELECT permissions on the function before referencing it. If a CREATE TABLE or ALTER TABLE statement references a user-defined function in a CHECK constraint, a DEFAULT clause, or a computed column, the table owner must also own the function. If the function is being schema-bound, you must have REFERENCE permission on tables, views, and functions referenced by the function.

    REFERENCE permissions can be granted through the GRANT statement to views and user-defined functions in addition to tables."







    **ASCII stupid question, get a stupid ANSI !!!**

  • September 15, 2005 at 10:27 am

    #590223

    If you have SELECT permissions for the function, it must be a table-valued function.  Then the correct syntax would be:

    SELECT * FROM ::dbo.fn_GetClosestZip(98052)

     

  • September 15, 2005 at 10:57 am

    #590243

    NICE! thanks all for your help!... Scott the select statement worked...

    select * from dbo.fn_GetClosestZip(98052)  

  • September 15, 2005 at 12:06 pm

    #590286

    I can't believe we all (almost) missed that one .

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

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