Delete not working

Question

Delete not working

terry999

SSCertifiable

Points: 5033
More actions
August 23, 2013 at 3:57 am

#395353

Hi
I'm really stumped, on a client's machine I can see a problem which I cannot reproduce.
Have a table that should be deleted and then repopulated the delete is not working.
A 3rd party application imports data from a file into a table in 2005 DB. I cannot see the code.
table: is dbo.tblDataImporter
User: DImporter (SQL Authentication) default schema is dbo. User has rights to delete/insert/select on table
Using profiler I can see:
1. CREATE table tblDataImporter(filename varchar(255) null, ..... Batch started and completed
Next it does somethng weird
2. SELECT * FROM CREATE table tblDataImporter(filename varchar(255) null, .....
Again Batch started and completed
3. DELETE FROM tblDataImporter Batch started and completed
4. INSERT INTO tblDataImporter (FILENAME, ......) values ('myFile.txt',...)
The delete does not delete. The inserts do however.
If I log in using Enterprise manager as user DImporter and run the Delete it works.
I know both steps 1 and 2 FAIL silently (w.r.t the application). 1. becuase DImporter does not have create table rights and 2. because its just nonsensical query.
I have checked there is no table called DImporter.tblDataImporter. There are no triggers on the table. If batch completed then it cannot be query timeout?
Any ideas what I should investigate next time I'm connected to this machine. Trying to build a list of things to check when I next can access this machine.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Divine Flame SSCoach Points: 15943 More actions · Answer 1

You must be overlooking something over there.

I think you should recheck the access rights of the user. May be it is added in some application role that either gives it the right to create\drop the table OR\AND it denys the delete right to the user so the DELETE fails too.

Sujeet Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

What do you mean 'the delete does not delete'?

Fails with an error?

Hangs forever?

Completes but affects 0 rows?

Implodes the server and creates a black hole in the server room?

Something else?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HanShi SSC-Dedicated Points: 33506 More actions · Answer 3

GilaMonster (8/23/2013)
What do you mean 'the delete does not delete'?
Implodes the server and creates a black hole in the server room?

ROFL :rolleyes:

If that happened I would say the delete definitely did work!! A bit too well maybe...

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

terry999 SSCertifiable Points: 5033 More actions · Answer 4

at t=0

Table contains 50,000 rows

File contains 60,000 records

When I run the program and it finishes there are 110,000 recs. There should be 60,000 recs.

If I run the program again there should be 60,000 recs but there will be 170, 000 recs.

There must be an error but the app is not reporting the error. It just carries on.

Logging in as same user on that client machine and running delete through Enterprise server worked. The same app works on my DB.

I would think there is a difference between the DBs but I cannot figure what it is. I though permissions or it's trying to delete DImporter.tblDataImport rather than dbo.tblDataImport but there is only the dbo schema. It must be a permissions issue but why would it work on the same DB when using Enterprise manager.

HanShi SSC-Dedicated Points: 33506 More actions · Answer 5

terry999 (8/23/2013)Any ideas what I should investigate next time I'm connected to this machine. Trying to build a list of things to check when I next can access this machine.

Next time when you run profiler include the events "stmtStarted" and "stmtCompleted" from the "Stored Procedures" and "T-SQL" sections. Also include the columns "Error" and "Rowcounts". After the batch has run review the trace and see how many rows were affected by the delete statement and also look for possible ROLLBACK actions.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

terry999 SSCertifiable Points: 5033 More actions · Answer 6

Thanks HanShi

I was just using default options for profiler. I will test this locally so when I have my window on the problem machine I can set profiler up and hopefully get to the bottom of it.

Errors and Transaction roll back has me v.interested.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Rather than tracing the statement started and completed, maybe trace the User Error Message event.

p.s. It won't be trying to delete from a table in a schema that doesn't exist, unless you specify that schema name explicitly. Otherwise binding is first to the user's default schema and if no such table exists, to the table in dbo.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

terry999 SSCertifiable Points: 5033 More actions · Answer 8

Thanks people.

I've just played with the profiler on my machine (the app works on my DB) and traced the errors transaction and error event. There is no transaction around the delete but more importantly with the create table statement

I can see the error the application is ignoring "user Doesn't have create user rights"

Thanks, hopefully with these metrics traced I can get the error MSSQL is sending back to the app on the problematic machine.

Once again thanks again for taking the time to read my post "one machine don't work mine does and I cannot see a difference"