?
Solved

SQL record deletion

Posted on 2012-08-17
14
Medium Priority
?
690 Views
Last Modified: 2012-09-24
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
0
Comment
Question by:ammartahir1978
  • 4
  • 2
  • 2
  • +4
14 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38306772
(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
 
LVL 7

Expert Comment

by:B_Denison
ID: 38306788
Did you do the delete in a transaction and then not commit the transaction?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 38306824
can we see your delete query?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ammartahir1978
ID: 38306856
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
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 375 total points
ID: 38306864
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
 

Author Comment

by:ammartahir1978
ID: 38307123
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
 
LVL 7

Expert Comment

by:B_Denison
ID: 38307156
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 38307663
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
 

Author Comment

by:ammartahir1978
ID: 38307950
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
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 375 total points
ID: 38308621
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 375 total points
ID: 38308627
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
 

Author Comment

by:ammartahir1978
ID: 38308812
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38310114
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
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 375 total points
ID: 38312071
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question