| Author |
Topic |
|
vince123
Starting Member
5 Posts |
 Posted - 2006-03-09 : 13:38:23
|
| Hi I wondered if anyone could help with the following problem, I am pulling my hair out with it and I am pretty sure its a no brainer :)everything in the db is currently owner dbo inc all stored procs.I have created a simple stored procedure:CREATE PROCEDURE p_deleteasdelete from dbo.Person where PersonId = 44select * from dbo.PersonGOThe procedure runs and a recordset is returned showing that indeed record 44 is not there. However it has not been deleted.Can anyone tell me why this might be?Best,Vince |
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2006-03-09 : 14:03:28
|
quote:
The procedure runs and a recordset is returned showing that indeed record 44 is not there. However it has not been deleted.
Why do you say it has not been deleted? It shows up in subsequent queries? |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-03-09 : 15:59:36
|
| At a guess, you've got a transaction open somewhere, and you've either not committed, or else you've rolled back. Start looking there...*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
vince123
Starting Member
5 Posts |
Posted - 2006-03-10 : 04:25:47
|
| Thanks to both of you for getting back to me.I have no transaction blocks left open, the snippit I posted is the sum total of the stored proc. I have checked that there is exec permissions set on the stored procedure and the owner for everything is dbo.Any other ideas?Vince |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-03-10 : 04:27:49
|
| i suspect there is a trigger involved that prevents you from committing this transaction--------------------keeping it simple... |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-03-10 : 04:38:23
|
| Good idea Jen.Also, Veince - are you sure that the calling code isn't managing the transaction. Are you running the SP from Query analyser?Next step: run in an environment where nothing else is running, and do a trace with SQL Profiler...*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
vince123
Starting Member
5 Posts |
Posted - 2006-03-10 : 06:41:26
|
| Many thanks everyone!Your heads up about the query Analyzer was the route to the answer.I found that calling the procedure from the qa was the problem.If I called the sp from one of my application tier components it works fine and commits the delete.Have a nice weekend.Vince |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-03-10 : 07:50:51
|
| Your QA session must have an automatic/implicit setting 'begin transaction'....search for it under "options". |
|
|
|
vince123
Starting Member
5 Posts |
Posted - 2006-03-10 : 11:49:48
|
| :)Theres a small checkbox in the corner of the debug window that says Automatic Rollback....all I can say is TFIF :)many thanks everyone!Vince |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-03-10 : 12:41:34
|
| ? TFIF*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
vince123
Starting Member
5 Posts |
Posted - 2006-03-10 : 13:13:24
|
| it means.. thank Friday its Friday! ;) |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-03-10 : 13:30:04
|
| ahh - variation on TGIF ... now I get it. Was thinking Thank F... its fixed.*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
|
re: cannot delete from an sproc