Archive

Posts Tagged ‘transaction’

Transaction count after EXECUTE indicates a mismatching number – SQL Error

December 8, 2014 Leave a comment

I was getting the below exception when I execute a stored procedure from my C# console application.

SQL Transaction Error

SQL Transaction Error

My Stored procedure executes in Transaction mode and wrapped in Try/Catch block (Refer below)

CREATE PROCEDURE [Name]

BEGIN TRY

BEGIN TRANSACTION;

— My SQL Script

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

IF XACT_STATE() <> 0

ROLLBACK TRANSACTION;

END CATCH

Reason –

  • We get this error if the transaction started and terminated suddenly without committed or rollback.
  • In my Stored Procedure I had a ‘return’ statement after ‘BEGIN TRANSACTION’ , so the stored procedure terminated neither calling COMMIT nor ROLLBACK.

Fix –

  • Put “SET XACT_ABORT, NOCOUNT ON” statement which suppresses the error.
  • So the modified Stored Procedure template is as below

CREATE PROCEDURE [Name]

AS

SET XACT_ABORT, NOCOUNT ON 

DECLARE @starttrancount int

BEGIN TRY

SELECT @starttrancount = @@TRANCOUNT

IF @starttrancount = 0

BEGIN TRANSACTION

      — Your SQL Script

IF @starttrancount = 0

COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF XACT_STATE() <> 0 AND @starttrancount = 0

ROLLBACK TRANSACTION

END CATCH

🙂

Advertisement
Categories: SQL Tags: , ,

Plug-ins in CRM 2011 – Useful points

July 7, 2012 2 comments

Plug-in stages

  • Pre validation
    • Registered Plug-in run before the form is validated
    • Useful if you want to implement business logic before the actual validation starts.  i.e., Changes made in plug-in won’t be saved if the validation of the main system plugins complain because the changes are outside the database transaction.
    • Ex – Some “delete” plug-ins. Deletion cascades happen prior to pre-operation, therefore if you need any information about the child records, the delete plugin must be pre-validation.
  • Pre -operation
    • After validation and before the values are saved to the database
  • Post operation
    • Plugin will run after the values have been inserted/changed on the database

Database Transactions in Plug-Ins

  • Plug-ins may or may not execute within the database transaction
  • You can check if the plug-in is executing in-transaction by reading the ‘IsInTransaction‘ property of IPluginExecutionContext
  • Stages 20 and 40 are part of the database transaction while stage 10 and 50 may be part of the transaction
  • If plugin throws an exception, every action done during the transaction will be rollback

Few more Points

  • Whether a plug-in executes synchronously or asynchronously, there is a 2 minute time limit imposed on the execution of a (message) request.
  • If the execution of your plug-in logic exceeds the time limit, a Timeout exception is thrown
  • If a plug-in needs more processing time than the 2 minute time limit, consider using a workflow or other background process
  • ‘Pre-operation’ operations that CRM will do will not be carried out in pre-validation stage.
    • If you are deleting a record that has many-to-many relationship records with another entity; these relationship records will still be available in pre-validation stage, but not in pre-operation stage.
  • “Target” entity (i.e., pluginContext.InputParameters[“Target”])
    • It’s the entity on which plug-in registered
    • It only contains “dirty” attributes. if you convert to early bound, the value of the unchanged attribute will be null

Useful MSDN article :)

Categories: CRM 2011, Plug-Ins Tags: , , , , ,