SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How was the record deleted!!


How was the record deleted!!

Author
Message
SJanki
SJanki
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 225
Hi All
I am having a strange issue.
From .NET application 5 records are inserted in a Table A.
These records are inserted at datetime=2012-08-14 14:14:49.000

There is another functionality where another user , from the .net application, approves these records.
The code here is not touching table A at all.
It is only inserting a record in another table TableAudit.
In TableAudit the record is inserted at 2012-08-14 14:28:20.000

When the user displays the records what he inserted in Table A, 1 record is missing.
From SQL server, I check the Table A and it seems as if that record was never inserted. Its not there.
But according to the validations in the application this record must have been inserted ( i am sure because in the table the ids exist from 78869 to 78874 but 78871 is missing) , so I go and see the delete log.
It shows a record was deleted from this table at the same time when the record was being inserted in TableAudit

Such logic is no-where in the application to delete 1 record from Table A while inserting record in TableAudit.

Any idea how this can happen in SQL Server? How can I find more information on this deleted record.
Thanks!
rVadim
rVadim
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1125 Visits: 2306
Are there any triggers on those tables?
SJanki
SJanki
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 225
rVadim (8/14/2012)

Are there any triggers on those tables?



No triggers.
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28508 Visits: 39977
if a transaction rolls back or fails, the identity() columns still increment.
it sounds like an error occurred inserting the data that was not handled/reported by the .net application.

i've seen things form an app, like sticking zero in a foreign key column, or leaving values null for NOT NULL columns, for example.
those things return an error from SQL, but the app might jsut be assuming that the data was updated/inserted, without displaying any errors returned.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rVadim
rVadim
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1125 Visits: 2306
78869 to 78874 - that is 6 records you said you inserted 5 ?
SJanki
SJanki
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 225
rVadim (8/14/2012)

78869 to 78874 - that is 6 records you said you inserted 5 ?



I also add that 78871 was missing.
78869
78870
78872
78873
78874

Sorry for the confusion
SJanki
SJanki
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 225
Lowell (8/14/2012)
if a transaction rolls back or fails, the identity() columns still increment.
it sounds like an error occurred inserting the data that was not handled/reported by the .net application.

i've seen things form an app, like sticking zero in a foreign key column, or leaving values null for NOT NULL columns, for example.
those things return an error from SQL, but the app might jsut be assuming that the data was updated/inserted, without displaying any errors returned.


I used this to see if any deletions happened on that table , and it gave me 1 [Transaction ID]
So its like the record was inserted and then deleted?? isn't it?

DECLARE @TableName sysname
SET @TableName = 'dbo.Table A'
SELECT
[Transaction ID]
FROM
fn_dblog(NULL, NULL)
WHERE
AllocUnitName LIKE @TableName + '%'
AND
Operation = 'LOP_DELETE_ROWS'
Ness
Ness
SSC-Addicted
SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)

Group: General Forum Members
Points: 429 Visits: 974
You could always run a server side trace looking for the events and especially the TM: Rollback Tran Starting/Completed event to spot if the transaction was rolled back

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16480 Visits: 19557
SJanki (8/14/2012)
Hi All
I am having a strange issue.
From .NET application 5 records are inserted in a Table A.
These records are inserted at datetime=2012-08-14 14:14:49.000

There is another functionality where another user , from the .net application, approves these records.
The code here is not touching table A at all.
It is only inserting a record in another table TableAudit.
In TableAudit the record is inserted at 2012-08-14 14:28:20.000

When the user displays the records what he inserted in Table A, 1 record is missing.
From SQL server, I check the Table A and it seems as if that record was never inserted. Its not there.
But according to the validations in the application this record must have been inserted ( i am sure because in the table the ids exist from 78869 to 78874 but 78871 is missing) , so I go and see the delete log.
It shows a record was deleted from this table at the same time when the record was being inserted in TableAudit

Such logic is no-where in the application to delete 1 record from Table A while inserting record in TableAudit.

Any idea how this can happen in SQL Server? How can I find more information on this deleted record.
Thanks!


You've stated that 5 rows are inserted from the app - and 5 rows remain.
If you're inserting 5 rows one at a time, what prevents another process from inserting then deleting one row in the middle of those inserts? Where did the sixth row come from?
Why aren't you inserting the 5 rows in one statement?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search