Re: transaction with unknown nbr of commands



"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: transaction with unknown nbr of commands
    ... into a temp table or table variable in the stored procedure code. ... the possibility that some rows would commit and others not. ... >> explicit transaction with a BEGIN TRAN statement, ... Does SQL Server 2000 have some capabilities that I should ...
    (microsoft.public.dotnet.framework.adonet)
  • 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)
  • Re: ADO.NET Timout
    ... How can I call a stored procedure to do its thing asynchronously. ... as a single unit of work, you can implement an explicit transaction. ... If I execute a Stored procedure that takes lets say 120 seconds then what ... But what about SQL Server Does SQL Server keep on Executing the Stored ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: How to get list of EventClasses in MSSQLServer2000
    ... statement inside a stored procedure has completed.') ... SQL Server statement or stored procedure.') ... Plan','Displays the plan tree of the Transact-SQL statement executed.') ... Login','Occurs when a SQL Server login is added or removed; ...
    (microsoft.public.sqlserver.security)
  • Re: Accessing FoxPro Free Table
    ... which the SQL Server service is running. ... account, ... > If you are creating a stored procedure and you want> to make sure that the procedure definition cannot be> viewed by other users, you can use the WITH ENCRYPTION> clause. ... The procedure definition is then stored in an> unreadable form. ...
    (microsoft.public.data.odbc)
Loading