none
I get Error Number : 134 on SQL 2005 RRS feed

  • Question

  • When I execute the simple select query mentioned in below trace, the Adapter.Fill() does not return, when i saw in SQL profiler, it showed the the query and parameters properly.

    It did not throw any exception and application got hanged.

    Now suddenly the application started working, i want to know the reason why the below exception was coming.

    Error Number : 134
    Error: The variable name '@StartDate' has already been declared. Variable names must be unique within a query batch or stored procedure.
    Command In Error : SELECT DISTINCT ValuationDate from bo_HedgeValuations WHERE ValuationDate >= @StartDate AND ValuationDate <= @StopDate AND InsId IN(Select InsId from bo_HedgeMembership WHERE HedgeId = @HedgeId) and (HedgeId is null or HedgeId = 0 or HedgeId = @HedgeId ) and PricingScenarioId=1  ORDER BY ValuationDate
    Connection Details : Application Name = AdaptivOps;server = STARSPHLSVR15\SQL2005;database = BAYER_ADP_92_JF_24Apr2010;user id = sa;
    Stack Trace :    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
       at Adaptiv.Operations.Core.DataAccess.OpsDataAdapter.DoFill(DataSet dataSet, Int32 retryAttemptNumber)
       at Adaptiv.Operations.Core.DataAccess.OpsDataAdapter.DoFill(DataSet dataSet)
       at Adaptiv.Operations.Core.DataAccess.OpsDataAdapter.Fill(DataSet dataSet)

    Monday, May 3, 2010 7:58 AM

All replies

  • The problem most likely is that somewhere in your code, you are doing what the error states, you set two paramters with the same
    name for the command in question. For example, this will give the same exception:

          using (SqlConnection con = new SqlConnection(cs))
          {
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT CompanyName FROM Customers WHERE CustomerID = @cid";
            cmd.Parameters.Add("@cid", System.Data.SqlDbType.NVarChar).Value = "ALFKI";
            cmd.Parameters.Add("@cid", System.Data.SqlDbType.NVarChar).Value = "ALFKI";
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
              Console.WriteLine(rdr.GetString(0));
            }
            con.Close();
          }

    This posting is provided "AS IS" with no warranties.
    Monday, May 3, 2010 9:50 AM
  • Do you use any globally declared Command-type variable and add parameters to it? If you do then you need to add parameters to this Command only once, and make sure it does not happen multiple times unless the variable is diposed.
    Val Mazur (MVP) http://www.xporttools.net
    Monday, May 3, 2010 10:27 AM
    Moderator
  • Hi Val,

    I have declared the command locally inside the method. And i add parameters to it in that method itself so the command will be disposed as soon as the method scope ends.

    This is my code inside a private method

     

    StringBuilder sql = new StringBuilder();

     

    sql.Append("SELECT DISTINCT ValuationDate from bo_HedgeValuations WHERE ");

    sql.Append("ValuationDate >= @StartDate AND ValuationDate <= @StopDate AND ");

    sql.Append("InsId IN(Select InsId from bo_HedgeMembership WHERE HedgeId = @HedgeId) ");

    sql.Append(" ORDER BY ValuationDate");

     

    SqlCommand calCommand = new SqlCommand (sql.ToString());

     

    if (observationRule == "Period")

    calCommand.AddParameter("@StartDate", ParameterDirection.Input, DbType.DateTime, startDate);

    else

    calCommand.AddParameter("@StartDate", ParameterDirection.Input, DbType.DateTime, earliestCalanderDate);

     

    calCommand.AddParameter("@StopDate", ParameterDirection.Input, DbType.DateTime, stopDate);

    calCommand.AddParameter("@HedgeId", ParameterDirection.Input, DbType.Int32, hedgeId);

     

    When I give this command to Adapter and I call Adapter.Fill() it executes the query which i can see in profiler but it does not return back.

     

    It gives me error as below:-

    Error Number : 134
    Error: The variable name '@StartDate' has already been declared. Variable names must be unique within a query batch or stored procedure.

    Command In Error : SELECT DISTINCT ValuationDate from bo_HedgeValuations WHERE ValuationDate >= @StartDate AND ValuationDate <= @StopDate AND InsId IN(Select InsId from bo_HedgeMembership WHERE HedgeId = @HedgeId) and (HedgeId is null or HedgeId = 0 or HedgeId = @HedgeId ) and PricingScenarioId=1  ORDER BY ValuationDate

     

     

    Tuesday, May 4, 2010 6:21 AM
  • The problem most likely is that somewhere in your code, you are doing what the error states, you set two paramters with the same
    name for the command in question. For example, this will give the same exception:

       using (SqlConnection con = new SqlConnection(cs))
    
       {
    
        con.Open();
    
        SqlCommand cmd = con.CreateCommand();
    
        cmd.CommandText = "SELECT CompanyName FROM Customers WHERE CustomerID = @cid";
    
        cmd.Parameters.Add("@cid", System.Data.SqlDbType.NVarChar).Value = "ALFKI";
    
        cmd.Parameters.Add("@cid", System.Data.SqlDbType.NVarChar).Value = "ALFKI";
    
        SqlDataReader rdr = cmd.ExecuteReader();
    
        while (rdr.Read())
    
        {
    
         Console.WriteLine(rdr.GetString(0));
    
        }
    
        con.Close();
    
       }
    
    

    This posting is provided "AS IS" with no warranties.

    This would have given me Sqlexception saying duplicate parameters are added to the command, in my case, this does not happen. It executed the query properly but does not return from there.
    Tuesday, May 4, 2010 6:28 AM
  • Are you sure that this is the code that fails? If you compare SQL statement constructed in posted code and the actual SQL statement inside of the error message then you will see that they are slightly different. In error message SQL statement is longer. Do you execute any batches of SQL statements?
    Val Mazur (MVP) http://www.xporttools.net
    Tuesday, May 4, 2010 10:18 AM
    Moderator
  • Hi Val,

    Yes I am sure that this code is failing as i had debug the code and found the adaptre.fill() is not returning at all.

    The two statemets are different as one line code is  missing, mistakenly it got deleted in copy paste.

    We do not execute the batches of sql statements.

    Monday, May 17, 2010 4:47 AM