none
Parameter Name Corruption RRS feed

  • Question

  • For some unknown reason, which doesn't seem to be a memory problem, when executing a stored procedure on a SQL 2005 database using .net 1.1 SqlCommand.ExecuteNonQuery() one of the parameter names gets corrupted.

     

    The relevant parts of the error message as follows:

     

    Code Snippet

    System.Data.SqlClient.SqlException: @i_valuelng is not a parameter for procedure sp_insertAttributeValue.

    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)

    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

    at MyProgramXYZ.UpdateData(DataTable dataTable) in ....

     

    ...the rest is my code's stack trace...

     

     

    As you can see, the @i_valuel?ng parameter is corrupt and should read @i_valuelong.

     

    The SqlCommand object is created and parameters are populated once, and then for each data item, the ExecuteNonQuery() method is invoked. I ran a SQL Profiler and it shows the invalid statement sent to SQL server after several thousand calls before it.

     

    Since ExecuteNonQuery() is used heavily all over my code this error only happens for this stored procedure after an arbitrary number of calls.

     

    Also, when viewing the statement shown in SQL Profiler using a HEX viewer, it seems that the double-byte character is &H6F and &H20... and it should be &H6F and &H00

     

    I've googled/ms'd this one and can't find anyone who's had a similar problem.

     

    Any help is appreciated

     

    Thursday, February 14, 2008 12:36 PM

All replies

  • Hi,

     

    I was having pretty much the same problem when passing null instead of DBNull.Value for parameter values.

    Make sure you pass DBNull.Value for parameters that don't have a value.

     

    http://codebetter.com/blogs/peter.van.ooijen/archive/2004/04/12/11210.aspx

    Thursday, February 14, 2008 9:49 PM
  • Thanks for your post!

    There is a difference though between using DBNull.Value and null for the parameter value which is evident when running a SQL profiler trace. When DBNull.Value is used, each parameter is set to NULL, whereas when null is used, each parameter is set to default.

    e.g.

    When DBNull.Value the SQL statement is:

    exec sp_insertAttributeValue @i_instanceId = 1, @i_value_int=NULL, @i_value_string=NULL, @i_value_long=123;

    When null the SQL statement is:

    exec sp_insertAttributeValue @i_instanceId = 1, @i_value_int=default, @i_value_string=default, @i_value_long=123;

    Depending on how the stored procedure parameters are declared, this may be an undesirable behavior.

    CREATE PROC dbo.sp_insertAttributeValue
    (
    @i_instanceId int,
    @i_value_int int = 0,
    @i_value_string nvarchar(255) = null,
    @i_value_long bigint = 0
    )
    ...

    vs

    CREATE PROC dbo.sp_insertAttributeValue
    (
    @i_instanceId int,
    @i_value_int int,
    @i_value_string nvarchar(255),
    @i_value_long bigint
    )
    ...

    I've deployed the DBNull.Value version at the client so I'll update this post if the error happens again.



    Friday, February 15, 2008 9:17 AM
  • Humph... as promised, an update to this post... I deployed the DBNull.Value version at the client, and the error has occurred again.

    I'm running out of ideas now... Somehow, the SQL that is generated and sent is corrupted... and it doesn't appear to be a memory problem.

    System.Data.SqlClient.SqlException: @i_valuelng is not a parameter for procedure sp_insertAttributeValue.
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
      ... my code ...



    Monday, February 18, 2008 7:07 AM