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.