CODEWITHSUNDEEP
asp.netsql-serversql-server-2008sql-server-2005codesmith
Imran Rizvi
Imran Rizvi

Reputation: 7438

Calling SP from asp.net gives error whereas it works fine from SSMS

I have two tables:

  1. Property
  2. Property_Localize

and a SP to delete record from Property:

  1. (Delete_SP)

I have a trigger that fires Instead Of deleting records from Property it deletes related records from Property_Localize table first and then delete records from Property table..

Property table's primary key is also used in some other table as foreign key.

Now we are trying to delete record from Delete_SP.

If the property table has associated record in any other table it throws exception this exception is handled in try..catch block , thus in any case SP returns some default value in output parameter and executes successfully.

This works absolutely fine if we run this SP from back end (SQL Server Management Studio).

But when we execute this SP from asp.net it gives following error:

"Uncommittable transaction is detected at the end of the batch. The transaction is rolled back."

Although it gives proper output parameter.

We also try to add try catch and explicit transaction block in trigger but it still gives same error.

Any help would be appreciated.

I forgot to mention , I am using CodeSmith's generated database layer.

Upvotes: 0

Views: 520

Answers (3)

Pankaj
Pankaj

Reputation: 10105

SQL Profiler

This will enable you to track down all phases of your query(s) during your request from ASP.Net to database.

Your Stored procedure should be like this..

Create Proc ProcedureName 
@UserName Varchar(50), 
@Password Varchar(50), 
@Email Varchar(50) 
As 
SET NOCOUNT ON 
SET XACT_ABORT ON 

Begin Try 
    Begin Tran 
        //Your Code
    Commit Tran  
End Try 

Begin Catch 
    Rollback Tran 
End Catch

Upvotes: 1

Nikki9696
Nikki9696

Reputation: 6348

Maybe this will help. [Bolding for emphasis mine]

http://msdn.microsoft.com/en-us/library/ms175976.aspx

Uncommittable Transactions and XACT_STATE

If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction. When a batch finishes, the Database Engine rolls back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application. This indicates that an uncommittable transaction was detected and rolled back.

For more information about uncommittable transactions and the XACT_STATE function, see XACT_STATE (Transact-SQL).

Upvotes: 0

Jive Boogie
Jive Boogie

Reputation: 1265

SSMS can have it's own configuration settings that is uses when executing the sproc that are not neccesarily the same as the settings that are used when the Stored Procedure is called from Ado.Net. By default the connection from Ado.Net should use the default Server Settings and if there is a diff between the two then that could account for the difference.

Upvotes: 0

Related Questions