Link to home
Create AccountLog in
Avatar of tiborsas
tiborsas

asked on

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

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))
ASKER CERTIFIED SOLUTION
Avatar of Marten Rune
Marten Rune
Flag of Sweden image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of tiborsas
tiborsas

ASKER

As we see now (not yet confirmed), the issue is in direct relationship with the .NET 2.0 runtime version (used by the CLR methods). What seems to behind the issue that the CreateParameter(Ö calls in the above code does not get the parameter names with @ - this is possible only with .NET 2.0 SP2 for the CLR methods, as we see. (The missing @ is not causing any issue with SQL 2008 or 2008 R2) Final confirmation (from QA) still pending. I'll add a comment once this is closed.
Thanks for the comment, martenrune.
As it was mentioned, the real problem was that the inadequate SQL parameter naming (omitting the @ character) is working only in .NET 2.0 SP2, so we had to update the SQL Servers (in the  cluster or even in case of the standalone SQL Server) to SP2.
As SQL Server 2008 installs with .NET 3.5, this was not an issue at all on SQL 2008 deployments.
Good feedback. Glad to hear it's working.
Seen some similar problems in the past.

//Marten