ADO.NET corrupting SQL parameters

Hi all.
We have persistant failures when trying to update values from the dataset into underlying database. Our web service captures the update generated by the desktop client and sends it through ADO.NET into the SQL 2000 service. Somewhere in the "pipeline" the SQL statement passed into SQL service is corrupted.

For example, here are snippets from the web server trace written just before the dataset.Update statement:

(printed from dataset.InsertCommand property, notice significant characters in bold)

INSERT INTO tblIDCallNumberGroups ....TruncateDeweyDecimalPlaces..', ...@TruncateDeweyDecimalPlaces tinyint....

here is the sample XML content printed from the same dataset:

.....<TruncateDeweyDecimalPlaces>3</TruncateDeweyDecimalPlaces>....

Notice that the SQL and the values are correct.

Now when the Dataset.Update executes, an exception is thrown (notice that name of the parameter was changed from @TruncateDeweyDecimalPlaces to @TruncateDeweyDpeimalPlaces and, thus, corrupted):

Exception occurred
@TruncateDeweyDpeimalPlaces is not a parameter for procedure .
  at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
  at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
  at MARCMechanic.Services.MechXPress.UpdateMechanicOptions(String strXMLChanges, Int32 nCleanupID, Int32 nStateID)

We think this corruption takes place somewhere AFTER the dataset.update execution and outside of our code, probably in the ADO.NET code.

When we capture the SQL string coming into the SQL Server using Profiler, the SQL is already corrupted like this:

exec sp_executesql N'INSERT INTO .... = 0, @TruncateDeweyDpeimalPlaces = 3, @fk..........

(notice that name of the parameter was garbled)

INFO: we are on SQL Server 2000 SP4, .NET 1.1; the characters corrupted are changing from execution to execution, sometimes they are non-printable characters; usually the same exact parameter (shown here) is corrupted in the same position. Sometimes different param is corrupted.

Many thanks for any ideas.

Answer this question

ADO.NET corrupting SQL parameters

Mark_van_Dark
Thanks.

- We have not tried it on ADO.NET 2.0. At this time we would not use it for the production code that experiences the problem anyway.

- We do not use the CommandBuilder.

- Only *one* param Name got corrupted in all the problem recurrences, to our knowledge. Almost always it is the same two characters in the same parameter (shown above). These are attached to several specific *jobs* that we run through our system.

More info: we use IIS 5.0 on the affected web server.

Here is a code snippet that shows basic flow of the failing SQL update/insert (if we open an official support case, we would be willing to provide entire code base affected then). daCleanups.Update(....) fails with the error shown above and the transaction rolls back.

// create and load dataset

// ...

sqlConnection1.Open();

SqlTransaction transaction =

sqlConnection1.BeginTransaction("UpdateOptionsTransaction");

try

{

// Add transaction to UpdateCommand, InsertCommand, and DeleteCommand for all our DataAdapters

daCleanups.UpdateCommand.Transaction = transaction;

// ...

// Write the SQL statements out to the trace log so we get an "input" into SQL

Context.Trace.Write("daCleanups UPDATE", daCleanups.UpdateCommand.CommandText);

// ...

// Call Update() on all our DataAdapters

daCleanups.Update(dsOptions1,

dsOptions1.tblCleanups.TableName);

// ...

transaction.Commit();

}

catch (Exception ex)

{

Context.Trace.Warn("WebMethod

UpdateOptions", "Exception occurred", ex);

transaction.Rollback();

throw ex;

}

finally

{

sqlConnection1.Close();

}
Tulika
If you are not using the CommandBuilder, then the work that Adapter.Update does is minimal. For e.g. if the Parameter.SourceColumn property was set, then the Adapter will get the Value from the Dataset and set it into the Parameter.Value. But it does not set the Parameter.Name property. The App should have set that. The Adapter.Update method should not be touching the paramter name or the command text in this case.

One suggestion I have is to check your RowUpdatingEvent Handlers. They could do something that might affect the Update execution. Other than that, with the amount of code and information above, I can't really think of much else.
cynny
I have a few follow-up questions.

(1) Have you tried with ADO.Net 2.0 Beta2 If so does this also fail with that version
(2) Is the Update command being generated by the CommandBuilder, or being constructed by your app
(3) Is it only the param Name that gets garbled, or any other part of the update statement getting affected

It would be very helpful if I could see the relevant app code.
ADO.NET corrupting SQL parameters

Answer this question