Re: Error Handling.

I'd recommend invoking the transactions and stored procedures in a
BEGIN TRANSACTION statement inside of a stored procedure, not from
client code. The SQLS engine will take care of managing locks if your
sprocs are accessing resources in the same order. Blocking and
deadlocking can occur if Tran1 locks Table1 and needs Table2 to
commit, and Tran2 locks Table2 and needs Table1 to commit. If both
Tran1 and Tran2 access Table1 and Table2 in the same order, then Tran2
will go into the wait queue until Tran1 finishes, which will likely be
milliseconds. Extending the transactional boundaries outside of the
server is less efficient and more prone to problems.

--Mary

On Tue, 25 Mar 2008 09:25:04 -0700, Rogers
<Rogers@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Thanks Mary,

Right now the developer is calling Begin transaction and calling the sps and
if any error occurs then it will rollback the transaction. Can you please
tell me if simultaneus access on the same stored procedure, should I do any
locking hint for that ?

Thanks

"Mary Chipman [MSFT]" wrote:

sorry, sent too soon.

You need to do both.

Error handling on the client prevents you from sending invalid
commands to the server, saving network bandwith and avoiding
triggering server-side errors, which are expensive. Treat all user
input as untrusted by validing input parameters, etc.

Error handling in your sprocs is necessary for performance, security
and data integrity. So you need to validate the data passed to the
server a second time -- you have to protect against an attack that
happens outside of the boundaries of your application.

--Mary

On Tue, 25 Mar 2008 07:48:01 -0700, Rogers
<Rogers@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I would appreciate if anyone can let me know which approach is the best for
doing error handling.

From APPLICATION or FROM Database.

Application calls all the stored procedure but application is doing error
handling, I am pretty much concern about locking and all that? can anyone
give me an idea about error handling from application side.

Thanks

.

Relevant Pages

  • Re: timeout exception during SqlTransaction.Commit() any workaround please help?
    ... As to single stored procedure. ... Why aren't you using SqlClient if you're going against a SQL Server? ... But at the very and when i am closing transaction i get an exception. ...
    (microsoft.public.dotnet.framework.adonet)
  • distributed transaction rollback issue.
    ... I have a stored procedure that completely handles the transaction and it does ... select/insert/updates on tables on a linked server. ...
    (microsoft.public.sqlserver.programming)
  • Re: Isolation Level, generating sequences
    ... Yes, it's safe, but you're making more calls to the server than you ... Create one stored procedure that implements an explicit ... transaction, and inside of the transaction perform all of the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Transactions and a linked server
    ... There is a property to allow remote procs in transactions. ... has to allow RPC requests from another server. ... > The VB app then calls a stored procedure on server 1 that> selects information from a table on the linked server> within another transaction. ...
    (microsoft.public.sqlserver.server)
  • Re: transaction with unknown nbr of commands
    ... then call your wrapper stored procedure, passing it all of the user ... explicit transaction with a BEGIN TRAN statement, ... >When I run a sqlcommand in a transaction, ...
    (microsoft.public.dotnet.framework.adonet)
Loading