Archive
Transaction count after EXECUTE indicates a mismatching number – SQL Error
I was getting the below exception when I execute a stored procedure from my C# console application.
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
🙂
Different ways of referencing web resources – CRM
If you have a HTML file added as a Web resource in CRM and it need to refer below web resources
- new_jquery
- new_dummy
Below are 2 ways to achieve the same
Standard Way
One way is to follow below convention to refer the web resource files in your HTML file
<script src=”../webresources/{webresourcename}”></script>
Referencing by folders
Assume you maintain files in folder structure, in your Visual studio solution which looks as below
- Jscript
- Util
- jquery.js
- helper.js
- Account
- account_form.js
- account_ribbon.js
- Util
- CSS
- dummy.css
Below is a more readable way to refer CRM web resources in HTML file.
Below are steps to achieve same.
Step 1
Add web resources with name as “solution prefix_/{webresourcename}”, as below.
- new_/Jscript/Util/jquery
- new_/CSS/dummy
Web resource with folder naming
Step 2
When you follow above naming convention, web resources can be referenced like how we do it in Asp.net projects, as below
<script src=”Jscript/Util/jquery” type=”text/javascript”></script>
Refer MSDN Link
Rajeev Pentyala - Microsoft Power Platform 