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.
-
I am not sure but try and change tinyint to int16 because tinyint maps to SByte in .NET FCL (framework class library) and it is not CLR compliant. I would also do an explicit convert instead of trying to set precision and scale which can only be set in DECIMAL and NUMERIC in SQL Server. If you need to format numbers you need to look for custom formatting in strings and formatting in FCL (framework class library). Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
Can you post the code .net snippit that you are using?
Steve
-
Thanks for trying to help.
But notice that I am talking about corrupting the SQL parameter *name*, not the value in it. I do not think the corruption has to do with the int types. Instead, *something* mingles up the parameter name and the SQL server returnes an error saying that such parameter does not exist.
I am making sense? Am I off here?
-
Steve, here is a snippet to look at. thanks
// 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();
}
-
Do you have the paramater @TruncateDeweyDecimalPlaces specified more than one place in your code?
I am assuming that you might be doing something like this.
string sql = "Select something from somewhere where condition=@TruncateDeweyDecimalPlaces";
SqlCommand cmd = New SqlCommand(sql,CnnObj)
cmd.Paramaters.Add("@TruncateDeweyDecimalPlaces",...)
Steve
-
Hey, Steve.
This specific param name maps to a field name in a database table. It became a param because the DataSet sets it up this way. The only code we have there is one autogenerated when you define and create a .NET dataset.
The actual issue is that that it could be ANY place in the SQL string that is injected with illegal chars. We beleive that the corruption is rather attached to a specific place in the SQL string that just happened to be in a middled of this parameter name. We do not think that this specific param name is somehow at fault. In our tests we also had other param names corrupted, but this is the one affected as of now.
Unfortunatelly, the place of corruption may jump around as well as illegal chars may change too. For example, before the weekend we had some unprintable chars injected into a different place
...........................@Original_fkMaterial??
go
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
We did install SQL Server SP4 over the weekend. It did not fix this problem.
-
Sorry
I don't have any magic suggestions as to why this might be occurring. The only thing that I would do at this point is try to get to the least common denominator and trap the the issue in the smallest code set possible.
Keep us up to date if you find the solution.
Steve
-
Thanks, Steve, for trying to come up with some ideas.
Basically, we are hoping someone would have a precedent of this kind. Our trouble is that we can not even replicate the problem on the test environment. Makes you think this is a specific machine setup issue. But we could not find a specific issue there either so far.
-
Hello Gregory,
We had a similar problem (twice) on one of our clients site. One of the parameter name was "corrupted".
We are unable to reproduce it either.
It was on an Update statement in a call to System.Data.SqlClient.SqlCommand.ExecuteNonQuery().
Not sure if the problem is in our code or in ADO.NET.
still searching...
Carl
-
Hey, Carl. Post it here if you find something.
If we can not get our answers from forums today, we are opening support case with MS. We'll see what they can do for us.
-
Hello Gregory,
For sure, if I find something I'll let you (this forum) knows.
Best regards,
Carl
-
Gregory;
I assume when you say "..printed from dataset.InsertCommand..." you mean "...printed from dataAdapter.InsertCommand...". That is the SQL that will get run.
Also, check that Parameters collection for the DataAdapter, that the SourceColumn and ParameterName are correct. You can (sometimes even intentionally) have different names here. It sounds like the misspelling is probably occurring there.
Hope this helps.
Mark -
Yes, I meant DataAdapter.InsertCommand. Thanks.
Still it seems that our problem is not attached to a specific param. It is rather a more or less specific place in the SQL string where two characters are being replaced by garbage by *something*.
-
Hello Gregory,
Have you found something new about that problem?
Thank's
Carl
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply