473,320 Members | 1,974 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Stored procedures and CTRL+ALT+DEL

We have a suppliers application that runs a stored procedure (one of
many). This stored procedure then calls various other ones etc... and
the final number of stored procedures run is 11.

Now, some of our users have been having problems and deciding for
themselves to CTRL+ALT+DEL the application. This is the first thing we
will stop.

However, we have had some error messages indicating that the previous
transaction is blocking them when they try it again. This leaves the
user blocking themself. Guess what they do next ? ******* ! :-)

I'm assuming that SQL is stuck at some point in the 11 SP's used.

When you cancel a query or stored procedure and SQL rolls back the
transaction, it may take a while to do this. Fair enough, but how does
it handle it if multiple transactions are to be rolled back ? For
example.

A calls B which calls C

A gets cancelled. What happens to B and C ? (assuming A has finished
the call to B but not finished the remainder of the SP i.e B and/or C
are still running).

Is there any way I can identify where it has failed (bit of a long
shot I know)?

Does SQL take the users permissions when running stored procedures
from within other stored procedures, or does it use their permissions
for the first one and SQL Server handles the remainder ? I know the
first one will use the users permissions, but does this carry on
indefinately ?

Any advice / help would be appreciated.

Thanks

Ryan

p.s. SQL 7 running on Windows 2000 Server / Windows 2000 PC's.
Jul 20 '05 #1
1 2257
Ryan (ry********@hotmail.com) writes:
When you cancel a query or stored procedure and SQL rolls back the
transaction, it may take a while to do this. Fair enough, but how does
it handle it if multiple transactions are to be rolled back ? For
example.

A calls B which calls C

A gets cancelled. What happens to B and C ? (assuming A has finished
the call to B but not finished the remainder of the SP i.e B and/or C
are still running).
When you have nested transaction, all is in fact one big transaction.
The inner BEGIN and COMMIT TRANSACTION only increase and decrease a
transaction counter. When you ROLLBACK, you rollback to the outermost
BEGIN TRANSACTION.
Is there any way I can identify where it has failed (bit of a long
shot I know)?
With some SELECT with NOLOCK and knowledge about the procedures, you
might be able to dig out something.

But if the problem really is long-running ROLLBACK, you should see
this with sp_who or sp_who2. Another possibility is that when the
users press CTRL-ALT-DEL and close the application, that there is
some DLL or whatever which is still alive, so there is no rollback.
Does SQL take the users permissions when running stored procedures
from within other stored procedures, or does it use their permissions
for the first one and SQL Server handles the remainder ? I know the
first one will use the users permissions, but does this carry on
indefinately ?


If user A calls a procedure dbo.a_sp which calls dbo.b_sp, the all the
way in the procedures, the permissions to the objects are those of
dbo. Unless, that is, there is no dynamic SQL in the procedures. For
dynamic SQL, the user's own permissions apply.
--
Erland Sommarskog, Stockholm, so****@algonet.se
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Pradeep M M | last post by:
Hi Folks, Can any of your folks tell me as to how can I capture the keys Ctrl+Alt+Del in C# or Vb.NET. Pradeep
1
by: Pedro Graca | last post by:
Well ... I've been using Windows for a long time before I switched to Linux. I'm very used to type CTRL-ALT-DEL everytime I'm leaving my computer or arriving to it. It's hapenned _twice_ (in the...
3
by: Stefan | last post by:
Hy, i have an app and i must disable this combination: ALT+F4; CTRL+ALT+DEL; CTRL+ESC;ALT+TAB like this: i find something on Internet and i can block ALT+F4 protected override...
4
by: | last post by:
I want to program a application for user login in windows.Now I has some question about disable the system hotkey(c+a+d,alt+tab,win key.etc.) and limit the mouse cursor in winform How can I do it?...
7
by: | last post by:
And how to lock the mouse? ths!
3
by: Kevin Bilbee | last post by:
I need to know the API call to disable the CTRL-ALT-DEL in a C# windows form. We have created a time clock application to run on a tablet PC to replace the windows shell, we are going to hang it...
2
by: Mark Callaway | last post by:
Hello I'm trying to make a little windows service which blocks access to some other program, in order to prevent my childrens to play with some of my games. It works perfectly, but the...
3
by: ranjithsubra | last post by:
Hi , i am a new member of this group,Learning vc++, 1.If i press Ctrl+Alt+Del, what happen Desktop window will be shoutdown, logof ,etc. 2. I need to Hide the KeyCombination CTRL+ALT+DEL, when...
10
by: thupham | last post by:
Dear all friend, I want disable Ctl+Alt+Del; Ctrl+Esc; Ctrl+tab, Alt+Tab, Start button, ctrl+Alt+Del, lock all keys on the keyboard. Have you ever do it in C#. Help me. Thanks for all reply.
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.