none
Invalid object name for user-Defined table type parameter to stored procedure

    Question

  • I have a stored procedure defined with a single parameter whose type is a user-defined data type. On occasion when a call to this stored procedure is made I get an error "Invalid object name @ids". On a particular day I will this error only for a single stored procedure. We don't make any changes to the stored proc, or the C# code, but the next day everything works fine. I have attempted to pass different types of invalid data to the SP, like passing a null, or a table with the wrong data type for the columns, no data, etc. Using SSMS I have attempted to call this SP with different types of data for the parameters but nothing have done seems to generate this error.

    My network people assur me that nothing with user permissions is being modified overnight. The SQL admins have checked the sql and system logs and there are no other errors being thrown.

    In the SQL Profiler, I see 3 events, RPC:Starting, then SP:Starting, the RPC:Completed with a 1-Error in the error column.
    I am tracing sp, rpc and sql statements.

    The call to the stored proc is made by calling sp_procedure_params_rowset to get a list of parameters then building the stored procedure call. This is well tested code and always works as expected so I don't think how the stored proc call is built is what is causing this issue.

    Any idea what could cause this?

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[pc_ccs_check_ids]
    	@ids as dbo.single_id_clustered readonly
    AS
    BEGIN
    SET NOCOUNT ON
    			
    SELECT det.id, ch.[level]
    FROM @ids det	
    INNER JOIN change ch
        on det.id = ch.id
    END
    GO

    Thank you for any help you can provide.
    Tuesday, September 03, 2013 8:39 PM

Answers

  • Hi,

    Remove that "AS"  before Table-Valued parameter and try

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[pc_ccs_check_ids]
    	@ids  dbo.single_id_clustered readonly
    AS
    BEGIN
    SET NOCOUNT ON
    			
    SELECT det.id, ch.[level]
    FROM @ids det	
    INNER JOIN change ch
        on det.id = ch.id
    END
    GO

    Related reference link - http://technet.microsoft.com/en-us/library/bb510489.aspx


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.


    Friday, September 06, 2013 4:35 PM
    Moderator
  • The call to the stored proc is made by calling sp_procedure_params_rowset to get a list of parameters then building the stored procedure call. This is well tested code and always works as expected so I don't think how the stored proc call is built is what is causing this issue.

    So you're building the TSQL to call the stored procedure at run-time using an undocumented stored procedure?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, September 09, 2013 1:56 PM
  • I believe you posted the section for declaring local variables - either INSIDE a stored procedure or in a batch.  I'm fairly certain this hasn't changed significantly (except for supported datatypes).  For 2012, a procedure declaration is:

    --Transact-SQL Stored Procedure Syntax
    CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
        [ { @parameter [ type_schema_name. ] data_type }
            [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
        ] [ ,...n ]
    [ WITH <procedure_option> [ ,...n ] ]
    [ FOR REPLICATION ]
    AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
    [;]

    But that is likely a red herring.  I will guess that your problem is with the application code, not with the procedure declaration (unless something is dynamically altering the procedure).  As david pointed out, you're using an undocumented procedure; this is one of the risks of using such an approach. 

    Monday, September 09, 2013 2:50 PM
  • I agree with Scott.

    Your problem is almost certainly with your C# code and how you passed the parameters to the stored proc.  Somehow you are passing NULL or some other invalid data type to SQL Server.

    Monday, September 09, 2013 2:54 PM
    Moderator
  • Thank you for your help. I had never thought of using 'as' in the parameter declaration as a problem, just not necessary. I assumed ( I know assumption can be dangerous ), that the parameter declaration of variables and stored proc variable declaration would follow the same guidelines. I couldn't find anything in BOL that has an 'as' in the way I am using it so I posted what I could find.

    Thanks again for the help.

    Kalvin

    Monday, September 09, 2013 2:56 PM

All replies

  • Hello,

    Sorrry for delay.
    Did the issue reproduce when call the Stored Procedure in your application?
    The Error description "Invalid object name" means object is not found in database.
    Since the Stored Procedure is work, the error may return from other place. Did you sepcify other object or variable with the same name in the code?

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    Friday, September 06, 2013 8:02 AM
    Moderator
  • Hi,

    Remove that "AS"  before Table-Valued parameter and try

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[pc_ccs_check_ids]
    	@ids  dbo.single_id_clustered readonly
    AS
    BEGIN
    SET NOCOUNT ON
    			
    SELECT det.id, ch.[level]
    FROM @ids det	
    INNER JOIN change ch
        on det.id = ch.id
    END
    GO

    Related reference link - http://technet.microsoft.com/en-us/library/bb510489.aspx


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.


    Friday, September 06, 2013 4:35 PM
    Moderator
  • Thank you for the reply.

    The issue only occurred on 2 particular days, and only when called from the application. This is a production server and I can't change the code on the fly. When I moved the database to my dev environment everything works fine. And even in the production env the next day everything worked as expected. We don't rebuild or reboot the database servers overnight so i'm not sure why it all of a sudden started working again.

    Kalvin

    Monday, September 09, 2013 12:14 PM
  • Thank you for the reply. Can you explain why that 'as' would make a difference. I can't reproduce the problem at will I'm trying to find the solution with no real way to test it. I just need to be able to explain it and try to do something to avoid having this problem again in the future.

    Kalvin

    Monday, September 09, 2013 12:16 PM
  • "as" is generally used to denote an alias - it is not used in the definition of a variable or parameter.  If you look up the syntax in BOL (which you should get into the habit of doing), you will see the formal defintion of a parameter for a stored procedure is:

    [ { @parameter [ type_schema_name. ] data_type }
            [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
        ]

    Generally a syntax error of this nature will prevent the procedure from being created/altered in the database.  Given your comments, perhaps the issue is one of usage.  You haven't specified how the procedure is actually being called from your program (and you also did not identify which version of sql server you are using - which is something you should ALWAYS do when postin), but the problem could be some particular logic path that results in invalid syntax when executing the procedure - something the profiler should be able to tell you.  If you examine the profiler information in detail (or post it), there should be some more useful information buried in it. 

    Monday, September 09, 2013 1:08 PM
  • I am using 2008 R1.

    This is what BOL has for variable declaration:

    DECLARE 
    { 
        { @local_variable [AS] data_type  | [ = value ] }
      | { @cursor_variable_name CURSOR }
    } [,...n] 
    | { @table_variable_name [AS] <table_type_definition> }

    I couldn't find the same example for a parameter but it seems like the same would apply. I'm still not sure why the 'as' would change this one day but work the next day without error. The db doesn't get rebuilt or updated overnight, the code doesn't get changed but all of a sudden starts working. It seems like a potential issue could be a corrupted compile of the stored proc. 

    I don't have the profiler code that I can post, and I can't recreate the issue. I'm just trying to find a plausible answer to explain what happened.

    Kalvin

    Monday, September 09, 2013 1:48 PM
  • The call to the stored proc is made by calling sp_procedure_params_rowset to get a list of parameters then building the stored procedure call. This is well tested code and always works as expected so I don't think how the stored proc call is built is what is causing this issue.

    So you're building the TSQL to call the stored procedure at run-time using an undocumented stored procedure?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, September 09, 2013 1:56 PM
  • Yes, I agree that this is not idea but that's how the underlying sub-system was written years ago. We are in talks to decide how to best fix this. But since this problem only happens with this one stored proc and only on a couple of days I don't think that is the cause of the problem.

    Kalvin

    Monday, September 09, 2013 2:20 PM
  • I believe you posted the section for declaring local variables - either INSIDE a stored procedure or in a batch.  I'm fairly certain this hasn't changed significantly (except for supported datatypes).  For 2012, a procedure declaration is:

    --Transact-SQL Stored Procedure Syntax
    CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
        [ { @parameter [ type_schema_name. ] data_type }
            [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
        ] [ ,...n ]
    [ WITH <procedure_option> [ ,...n ] ]
    [ FOR REPLICATION ]
    AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
    [;]

    But that is likely a red herring.  I will guess that your problem is with the application code, not with the procedure declaration (unless something is dynamically altering the procedure).  As david pointed out, you're using an undocumented procedure; this is one of the risks of using such an approach. 

    Monday, September 09, 2013 2:50 PM
  • I agree with Scott.

    Your problem is almost certainly with your C# code and how you passed the parameters to the stored proc.  Somehow you are passing NULL or some other invalid data type to SQL Server.

    Monday, September 09, 2013 2:54 PM
    Moderator
  • Thank you for your help. I had never thought of using 'as' in the parameter declaration as a problem, just not necessary. I assumed ( I know assumption can be dangerous ), that the parameter declaration of variables and stored proc variable declaration would follow the same guidelines. I couldn't find anything in BOL that has an 'as' in the way I am using it so I posted what I could find.

    Thanks again for the help.

    Kalvin

    Monday, September 09, 2013 2:56 PM
  • Next time around you can use SQL Server Profiler to see what you are sending to the server for execution. It is a very handy tool in similar circumstances:

    http://www.sqlusa.com/bestpractices2005/auditlogin/

    http://www.sqlusa.com/bestpractices2005/reducesqlprofileroutput/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Saturday, September 21, 2013 12:48 PM
    Moderator