February 28, 2007 at 1:33 pm
Hello Newgroup,
i've a design problem with 2 T-SQL stored procedures with transactions. I want to have a transaction within the first procedure. This one schould call an second procedure during the work to inc. a number in a custom seq. table. And the problem is the second/inner procedure should do the increment without a transaction! In a classic client server appliction I can use a second connection for that kind of problem. How can I do this wie T-SQL? Any ideas? Any other help for me? I hope ...
regards Frank
February 28, 2007 at 3:10 pm
Being a design problem, there might be a few other things that we would need to know to be of help to you.
Is the inner procedure dependant on the outer procedure doing some work prior to it being called?
I think what you have to do is look at the coupling between these two procedures and work out if the inner one can be extracted in any way. Also, you should confirm that the transaction, successful or not, will have an adverse affect on the process (I don't know the process so can't comment on the viability of the need to have the inner procedure running outside of a transaction).
Not very helpful I am sure, but reviewing the problem from a wider perspective may be useful.
February 28, 2007 at 11:43 pm
thx for reply here are some more informations
The outer procedure copies data from a foreign customer table in a import work table in my database (the foreign table could be in the same database and server or somewhere else). For inserting the date in my import work table the inner procedure needs to add sequence number in a seq. number table in my database. This table is also used by a client application at the same time. The client application inserts also data in my import work table but by reading textfiles and also inc. the seq. number form the seq. table. (the seq. Number from my seq. table is the PK of the import work table) The client apllication uses a second connection for inc. the number (and can do it so without a transation) The outer procedure uses a transation to be sure to get ALL current data from the foreign table into my import work table or not. If there is an error the outer procedure rollsback else its commits. But the seq. number MUST be inc. without a transaction because the client application needs to get the next free number whatever the outer procedure has done.
regards, Frank
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply