Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delete not working Expand / Collapse
Author
Message
Posted Friday, August 23, 2013 3:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 116, Visits: 442
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
Post #1487713
Posted Friday, August 23, 2013 4:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 1,318, Visits: 2,546
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
Post #1487717
Posted Friday, August 23, 2013 4:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #1487729
Posted Friday, August 23, 2013 4:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:58 PM
Points: 2,290, Visits: 2,752
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
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’! **
Post #1487735
Posted Friday, August 23, 2013 5:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 116, Visits: 442
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.
Post #1487746
Posted Friday, August 23, 2013 5:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:58 PM
Points: 2,290, Visits: 2,752
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’! **
Post #1487755
Posted Friday, August 23, 2013 5:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 116, Visits: 442
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.
Post #1487763
Posted Friday, August 23, 2013 5:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #1487764
Posted Friday, August 23, 2013 6:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 116, Visits: 442
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"
Post #1487776
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse