We help IT Professionals succeed at work.
Get Started

Error while exceuting CLR stored procedure - only on *clustered* SQL Server 2005

541 Views
Last Modified: 2012-08-13
We're (and our customer) having a strange issue, showing up only if we use a SQL Server cluster. (SQL 2005 + SP3)
There is a CLR stored procedure (AddRecord), we open a new TransactionScope and inside it we're calling another CLR method (WriteToDB, see below), that has the following SQL commands executed inside: (and there is no permission etc problems with CLR, as other CLR SPs executed nicely)

public void WriteToDB(SqlConnection sqlCon)
{
. . .
using (SqlCommand sqlCmd = new SqlCommand())
{
    sqlCmd.Connection = sqlCon;
    sqlCmd.CommandType = CommandType.Text;
   
    StringBuilder sb = new StringBuilder();
    sb.AppendLine("Open symmetric key symKeyA decryption by password='*****';");
    sb.AppendLine("INSERT INTO [sch].[tableH] ");
    sb.AppendLine("(a,b,c,d,e,f,g)");
    sb.AppendLine(" values (");
    sb.AppendLine("@a,@b,@c,@d,@e,");
    sb.AppendLine("encryptbykey(key_guid('symKeyA'), @f,1,@b),");
    sb.AppendLine("encryptbykey(key_guid('symKeyA'), @g,1,@b)");
    sb.AppendLine(");");
    sb.AppendLine("SELECT @id=scope_identity();");
    sb.AppendLine("Close symmetric key symKeyA;");
    sqlCmd.CommandText = sb.ToString();

    sqlCmd.Parameters.Clear();
    sqlCmd.Parameters.Add(MyHelper.CreateParameter("id", DbType.Int32, ParameterDirection.InputOutput, 0));
    sqlCmd.Parameters.Add(MyHelper.CreateParameter("a", DbType.Int32, ParameterDirection.Input, (int)opCode));
    sqlCmd.Parameters.Add(MyHelper.CreateParameter("b", DbType.String, ParameterDirection.Input, snum));
    sqlCmd.Parameters.Add(MyHelper.CreateParameter("c", DbType.String, ParameterDirection.Input, u));
    sqlCmd.Parameters.Add(MyHelper.CreateParameter("d", DbType.String, ParameterDirection.Input, data));
    sqlCmd.Parameters.Add(MyHelper.CreateParameter("e", DbType.String, ParameterDirection.Input, text));
    sqlCmd.Parameters.Add(MyHelper.CreateParameter("f", DbType.DateTime, ParameterDirection.Input, adate));
    sqlCmd.Parameters.Add(MyHelper.CreateParameter("g", DbType.String, ParameterDirection.Input, sectoken.ToString()));
    sqlCmd.ExecuteNonQuery();

    // Get the current identity value
    lastID = (int)sqlCmd.Parameters["id"].Value;
}
}

When executed, the following exception is thrown:
A .NET Framework error occurred during execution of user-defined routine or aggregate "AddRecord":
System.Data.SqlClient.SqlException: Incorrect syntax near 'id'.
Must declare the scalar variable "@a".
Must declare the scalar variable "@id".
System.Data.SqlClient.SqlException:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
   at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at MyProject.HistoryRecord.WriteToDB(SqlConnection sqlCon)
   at StoredProcedures.AddRecord(Int32 x, SqlString s, SqlString u, SqlString y, Int32 a)

The stored proc call (AddRecord) executed w/o any issues in non-clustered environments. When the DB is on a SQL 2005 SP cluster (2 nodes, only 1 processor per node), the issue shows up (not an intermittent issue, reproduces consistently and constantly). When I executed the AddRecord SP from SSMS, the error is the same. When I execute the SQL commands that are inside of the WriteToDB method, it runs w/o problem even in the clustered environment.
We tried to use Profiler, but we did not get eny extra info about the root cause.

(For security reasons I changed the name of the different objects in the above code by hand, if I'd made a mistake, thats not the cause of the real problem, as, again it works fine in many environment, except two different clustered systems (both SQL 2005 SP3))
Comment
Watch Question
SQL Expert/Infrastructure Architect
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE