SQL record deletion

Hi Guys,

I have a very weired thing:

i am deleting row from a table but its not deleting from table, what i mean is i use delete statement and its executed successfully, but somehow records are still sitting in table, how can i make sure that its been deleted permanently?

thank you
ammartahir1978Asked:
Who is Participating?
 
Éric MoreauSenior .Net ConsultantCommented:
what do you get if you try:
select *
from _audit
Where aud_date_time between '2012-08-17 00:00:00' and '2012-08-17 23:59:59'
And aud_trans_type =100
And receipt_no = 'xxxxxx'
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(1)  There is a delete trigger on the table that cancelled the delete
(2)  Refresh your table
(3)  You have read-only access to the table, and for some reason it gives the illusion that the delete was successful.
(4)  Gremlins

Try this for kicks and giggles

DELETE FROM YourTable

SELECT @@ROWCOUNT, 'rows were deleted'
0
 
B_DenisonCommented:
Did you do the delete in a transaction and then not commit the transaction?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Éric MoreauSenior .Net ConsultantCommented:
can we see your delete query?
0
 
ammartahir1978Author Commented:
Thank you for your comments guys

Query I am using is :

Delete from _audit
Where aud_date_time between '2012-08-17 00:00:00' and '2012-08-17 23:59:59'
And aud_trans_type =100
And receipt_no = 'xxxxxx'
0
 
ammartahir1978Author Commented:
if i delete a receipt number and then do a select statement it doesnt show that particular receipt but when i export all receipts from computer to server it is included there, so thats what i dont understand, even if i delete a particular receipt from a table while in edit mode it still comes through, so either there are some permission issue i think or something else, i dont get it.
0
 
B_DenisonCommented:
what happens if you do this:

Delete from [_audit]
Where aud_date_time between '2012-08-17 00:00:00' and '2012-08-17 23:59:59'
And aud_trans_type =100
And receipt_no = 'xxxxxx'
;
commit;
0
 
ZberteocCommented:
What do you mean "export", how are you doing that? Maybe you did the export before the delete? I don't know, but if the record doesn't show in the select statement then it should not exist in export output either.
0
 
ammartahir1978Author Commented:
this is what i get when i do deletion

Server: Msg 3902, Level 16, State 1, Line 5
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
0
 
ZberteocCommented:
In SQL server you cannot invoke COMMIT if there is no BEGIN TRAN. On the other hand by default any SQL code batch is treated as a single transaction so all the statements will either succeed or will all fail. If you want to have multiple transactions within the same script is if you use the GO statement to separate different sections of the code, which in this case is equivalent with commit, or you have them wrapped in BEGIN TRAN ... COMMIT.

So if you really want to check you shoul use GO instead of commit but as I said, that is implicit.:

Delete from [_audit]
Where aud_date_time between '2012-08-17 00:00:00' and '2012-08-17 23:59:59'
And aud_trans_type =100
And receipt_no = 'xxxxxx'
GO

You don't need ; in SQL code.
0
 
Anthony PerkinsCommented:
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
I think you just answered your question.  If you would like to post your entire script we can suggest corrections.
0
 
ammartahir1978Author Commented:
hi All,

this is what i am doing which i ave posted i am running these statements from Query analyser and thats all, i do get a message that row has been deleted, but then it doesnt remove it completely, so either SQL is caching it or there is something missing
0
 
Anthony PerkinsCommented:
this is what i am doing which i ave posted i am running these statements from Query analyser and thats all
And again if you cannot "post your entire script"  including the line that has the COMMIT we cannot be more precise than tell you there is an error in your script.  It does not get any plainer than that.
0
 
Scott PletcherSenior DBACommented:
You can try this, to be sure you are not getting date conversion errors based on current SQL settings:

Delete from [_audit]
Where aud_date_time between '20120817 00:00:00' and '20120817 23:59:59'
And aud_trans_type =100
And receipt_no = 'xxxxxx'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.