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


Delete not working


Delete not working

Author
Message
terry999
terry999
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 677
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
Divine Flame
Divine Flame
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2303 Visits: 2816
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88763 Visits: 45284
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
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4436 Visits: 3672
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
terry999
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 677
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
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4436 Visits: 3672
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
terry999
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 677
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88763 Visits: 45284
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
terry999
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 677
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"
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