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("encryptbyke y(key_guid ('symKeyA' ), @f,1,@b),");
sb.AppendLine("encryptbyke y(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(MyHe lper.Creat eParameter ("id", DbType.Int32, ParameterDirection.InputOu tput, 0));
sqlCmd.Parameters.Add(MyHe lper.Creat eParameter ("a", DbType.Int32, ParameterDirection.Input, (int)opCode));
sqlCmd.Parameters.Add(MyHe lper.Creat eParameter ("b", DbType.String, ParameterDirection.Input, snum));
sqlCmd.Parameters.Add(MyHe lper.Creat eParameter ("c", DbType.String, ParameterDirection.Input, u));
sqlCmd.Parameters.Add(MyHe lper.Creat eParameter ("d", DbType.String, ParameterDirection.Input, data));
sqlCmd.Parameters.Add(MyHe lper.Creat eParameter ("e", DbType.String, ParameterDirection.Input, text));
sqlCmd.Parameters.Add(MyHe lper.Creat eParameter ("f", DbType.DateTime, ParameterDirection.Input, adate));
sqlCmd.Parameters.Add(MyHe lper.Creat eParameter ("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.SqlE xception: Incorrect syntax near 'id'.
Must declare the scalar variable "@a".
Must declare the scalar variable "@id".
System.Data.SqlClient.SqlE xception:
at System.Data.SqlClient.SqlC onnection. OnError(Sq lException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlI nternalCon nection.On Error(SqlE xception exception, Boolean breakConnection)
at System.Data.SqlClient.SqlI nternalCon nectionSmi .EventSink .ProcessMe ssagesAndT hrow(Boole an ignoreNonFatalMessages)
at Microsoft.SqlServer.Server .SmiEventS ink_Defaul t.ProcessM essagesAnd Throw(Bool ean ignoreNonFatalMessages)
at System.Data.SqlClient.SqlC ommand.Run ExecuteNon QuerySmi(B oolean sendToPipe)
at System.Data.SqlClient.SqlC ommand.Int ernalExecu teNonQuery (DbAsyncRe sult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlC ommand.Exe cuteNonQue ry()
at MyProject.HistoryRecord.Wr iteToDB(Sq lConnectio n 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))
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,
sb.AppendLine(" values (");
sb.AppendLine("@a,@b,@c,@d
sb.AppendLine("encryptbyke
sb.AppendLine("encryptbyke
sb.AppendLine(");");
sb.AppendLine("SELECT @id=scope_identity();");
sb.AppendLine("Close symmetric key symKeyA;");
sqlCmd.CommandText = sb.ToString();
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.Add(MyHe
sqlCmd.Parameters.Add(MyHe
sqlCmd.Parameters.Add(MyHe
sqlCmd.Parameters.Add(MyHe
sqlCmd.Parameters.Add(MyHe
sqlCmd.Parameters.Add(MyHe
sqlCmd.Parameters.Add(MyHe
sqlCmd.Parameters.Add(MyHe
sqlCmd.ExecuteNonQuery();
// Get the current identity value
lastID = (int)sqlCmd.Parameters["id
}
}
When executed, the following exception is thrown:
A .NET Framework error occurred during execution of user-defined routine or aggregate "AddRecord":
System.Data.SqlClient.SqlE
Must declare the scalar variable "@a".
Must declare the scalar variable "@id".
System.Data.SqlClient.SqlE
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlI
at System.Data.SqlClient.SqlI
at Microsoft.SqlServer.Server
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at MyProject.HistoryRecord.Wr
at StoredProcedures.AddRecord
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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.
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
Seen some similar problems in the past.
//Marten
ASKER
Thanks for the comment, martenrune.