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))