Re: transaction with unknown nbr of commands

If you really need all of those updates to commit/rollback as a single
unit of work, then you need to pass all of the information to your
sproc in input parameters. One technique I have used in the past is to
package up the values in delimited strings, and then parse the strings
into a temp table or table variable in the stored procedure code. This
is very labor-intensive in T-SQL, and works best if you only have to
deal with numbers, not strings, where you have the added hassle of
parsing special characters, delimiters, etc.

When you update from a DataSet/DataAdapter, you're doing one row at a
time, with each row being a separate transaction, and there is always
the possibility that some rows would commit and others not. You can
get error information back as to which rows did not commit, but you
can't undo the rows that have already committed.

--Mary

On Tue, 30 Aug 2005 13:37:03 -0700, Alex S Moore
<AlexSMoore@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>
>
>"Mary Chipman [MSFT]" wrote:
>
>> For maximum efficiency and scalability, you should implement your
>> explicit transaction inside of a stored procedure, not from client
>> code. The basic idea is you collect *all* input from the user first,
>> then call your wrapper stored procedure, passing it all of the user
>> input as input parameters. That stored procedure then initiates an
>> explicit transaction with a BEGIN TRAN statement, calling the other
>> stored procedures as required, passing each one the information it
>> needs. <snip>
>
>Thanks for the response Mary. I have used this technique when I could and
>have transactionos in some stored procedures. However, I do not see how to
>use this technique in the current situation. I probably was not clear.
>
>In this case, I have a base table with several repeating groups, seven
>actually. For the repeating groups, I can have as many rows as the user
>inputs for any or none of the tables. So, without some more research, I do
>not know how to get all of that user input to SQL Server from a single stored
>procedure. Does SQL Server 2000 have some capabilities that I should
>research? Or maybe you are talking about writing XML from the app and
>sending that single document to SQL Server. More info please?
>
>Fortunately, this particular situation was for two reference tables that do
>not change very often.
>
>With the day-to-day data capture, which is not coded just now, I hope to
>figure out enough to use datasets with multiple tables and their
>relationships. After the user builds a dataset, then the stored procedure
>commands will do the update with something like dataset.update, or whatever.
>But this also sounds like multiple trips from the application to SQL Server
>for one commit.
>
>Thoughts?
>
>Thanks, Alex
.

Relevant Pages

  • Re: SqlTransaction wont commit
    ... > to commit after each record is inserted but it doesn't seem to work. ... > need to commit each time because I sleep the thread after inserting 10 ... > //assign parameter code etc etc ... Show the contents of the stored procedure as well as the parameters being ...
    (microsoft.public.dotnet.framework.adonet)
  • Updating records with TSQL cursor and WHERE CURRENT OF
    ... stored procedure, out of laziness really, ... in a table (can't be done in transactional SQL as far as I can tell). ... cursor is still under the protection of commit and rollback... ...
    (microsoft.public.sqlserver.programming)
  • Re: Using a Transaction with a SqlDataSource
    ... I would set a Return Code on the Return statement of the Stored Procedure. ... If it returns zero, then do the commit, else Throw a New Exception, which will make the logic go to the Catch. ... That return of failure I would assume is something you're going to return using the Return code yourself, because if there is a problem in the Stored Procedure in executing, then an Exception is going to be thrown by SQL Server. ... I think your other problem the way you have it coded is the Finally is out of the scope of the transaction, with the commit or rollback. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Is this even possible?
    ... In the stored procedure, the data is inserted, then transaction is ... why is there no guarantee? ... Surely a commit means ...
    (microsoft.public.sqlserver.programming)
  • Re: ADO.NET Timout
    ... ADO classic supports async ops as ... How can I call a stored procedure to do its thing asynchronously. ... as a single unit of work, you can implement an explicit transaction. ... But what about SQL Server Does SQL Server keep on Executing the Stored ...
    (microsoft.public.dotnet.framework.adonet)