March 28, 2012 at 2:00 pm
Possible, we will try that although anytime we submit SQL statements with compilation errors in them we get them logged in our log and we fixed all of those issues with other statements (this one never throws an error)... Thank you...
March 28, 2012 at 2:02 pm
eshulman (3/28/2012)
Possible, we will try that although anytime we submit SQL statements with compilation errors in them we get them logged in our log and we fixed all of those issues with other statements (this one never throws an error)... Thank you...
That's why I think it best to log ALL of them. It is possible you are missing some with your error checking. I have seen people relying on @@error and not completely understanding why they were not getting errors returned.
Jared
CE - Microsoft
March 28, 2012 at 2:03 pm
Not sure if my other question was answered or not, so to clarify I'll ask again. Each dynamic insert statement created, are they the same or can they be different from execution to execution? If they can be different, how are they different?
March 28, 2012 at 2:05 pm
The only difference between executions is the data content of fields, the statement is always the same.
March 28, 2012 at 2:11 pm
eshulman (3/28/2012)
The only difference between executions is the data content of fields, the statement is always the same.
Hmm... Then why is the INSERT INTO table (column, columnb, columnn) VALUES( portion of the statement not explicit? Your VB.NET code builds this statement... Why? You would only have to build it if it was not the same each time.
Jared
CE - Microsoft
March 28, 2012 at 2:16 pm
eshulman (3/28/2012)
The only difference between executions is the data content of fields, the statement is always the same.
May I ask the obvious then, why isn't there a stored procedure in the database to handle the insert and return the trip id assigned to the application? Seems to me it would greatly simplify the application code.
March 28, 2012 at 2:22 pm
That is a good question, will have to ask the developers...
March 28, 2012 at 2:36 pm
Its clear that the VB.NET code is assuming it will NOT be the same each time. So, (not being accusatory but asking a genuine question) how do you know for sure that the code generates the same insert statement every time when it is not explicit? I think the answer is that you cannot guarantee it...
Jared
CE - Microsoft
March 28, 2012 at 3:20 pm
We will print out the insert statement in the case where the insert fails and we prompt the operator to save again (we still are inside our code and can print out the saved INSERT statement) and we will see if there are any issues with it since we can take it and run it the database directly.
March 28, 2012 at 3:25 pm
eshulman (3/28/2012)
We will print out the insert statement in the case where the insert fails and we prompt the operator to save again (we still are inside our code and can print out the saved INSERT statement) and we will see if there are any issues with it since we can take it and run it the database directly.
My only concern is that you are assuming that the code correctly identifies that it failed. It seems from your issue that it is not doing that. I would record ALL insert statements to be sure.
Jared
CE - Microsoft
March 28, 2012 at 3:32 pm
Well, we know the insert has failed because we go to read the just inserted record and it is NOT there and we have an IDENTITY hole in the table. I dont know of a better way to know that insert has failed.
March 28, 2012 at 3:38 pm
eshulman (3/28/2012)
Well, we know the insert has failed because we go to read the just inserted record and it is NOT there and we have an IDENTITY hole in the table. I dont know of a better way to know that insert has failed.
TRY CATCH blocks that trap and report the error so you know what happened?
March 28, 2012 at 3:45 pm
Yes, we were planning on implementing those as well but the other one is much easier and is actually tied directly to the issue we are having - that is if the INSERT statement is the culprit.
March 28, 2012 at 3:52 pm
Just because I need to know, will that be part building the sql in the application or do you think you might start moving to using stored procedures to handle the CRUD access to the database?
March 28, 2012 at 3:55 pm
There are some issues with the VB.NET code.
-- Steps onto soapbox
I suggest implementing some .NET best practices. Enclose every connection in a Using block, don't use Catch clauses without an exception type specified, when re-throwing use "Throw" instead of "Throw ex", ditch Hungarian notation in favor of well-chosen English variable names, prefer throwing exceptions up the stack to the object that can truly handle the exception to return codes, try not to pass objects by reference, don't dispose DataSet objects (there's an obscure reason DataSet implements IDisposable). These are the issues that I noticed in your code sample.
-- Steps off soapbox
I attempted to repeat the issue that you are reporting with code structured more or less like what you posted. I couldn't exactly hit it, but I'm not surprised that I couldn't reproduce it. You report it only occurs with heavy activity, and I can't replicate that. Nor do I have a web service set up.
I did manage to submit a SQL string that would produce the error. The string looked like this:
"BEGIN TRANSACTION INSERT something (col) VALUES (0) SELECT @@IDENTITY ROLLBACK"
The insert statement is valid. Because there is no error raised, no exception is sent to the caller. The identity value is incremented and returned. There is no inserted row.
You mention that insert statements that don't compile throw an error. SQL Server compiles the batch before attempting to run it. A run-time error (constraint violation for example) will still throw an error, but at a later time in the execution. The identity value is incremented and returned even when a run-time error occurs. However, it should raise an error, and your error handler should catch that.
Note that the insert runs in a transaction. The web method TransactionOption is RequiresNew. So, if the insert and identity selection work, but SQL Server is unable to commit (possibly due to a network under high load), the transaction will roll back. What happens when the transaction started by a web method rolls back because of an error while attempting to commit? I'm not sure. But I'd start researching there.
In summary, the identity value being populated is only proof that SQL Server attempted to insert a row. It won't make the attempt if the SQL doesn't compile, but it will attempt if the values violate a constraint, and it certainly will populate it if the insert is later rolled back.
I have never encountered a situation in which SQL Server just "lost stuff." But I've seen plenty of code that "lost stuff." This is an application issue. The problem is somewhere in the handling of errors and transactions.
Viewing 15 posts - 31 through 45 (of 67 total)
You must be logged in to reply to this topic. Login to reply