Archive

Archive for the ‘SQL’ Category

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

🙂

SQL query to split a string separated by Comma (or any special character)

Assume that you have a String with Comma separated value and wanted to split and get the collection in SQL.

My string looks ‘A,B,C,D, ‘ and I want output as

Value

A

B

C

D

Approach

The approach I am following here is

  • Declare a Temporary table
  • Split the string
  •  Insert the split values in to Temporary table

Query

DECLARE @MyValuevarchar(100)

DECLARE @posINT

DECLARE @lenINT

DECLARE @valuevarchar(8000)

SET @pos= 0

SET @len= 0

— Set the Comma separated value

SET @MyValue=‘A,B,C,D,’

— Declare Temporary Table to store split values

DECLARE @MyTempTbl TABLE (

[Value] [nvarchar](100)

)

WHILE CHARINDEX(‘,’,@MyValue,@pos+1)>0

BEGIN

SET @len=CHARINDEX(‘,’,@MyValue,@pos+1)@pos

SET @value=SUBSTRING(@MyValue,@pos,@len)

— Insert the splitter value in to Temporary Table

INSERT INTO @MyTempTbl Values(@value)

SET @pos=CHARINDEX(‘,’,@MyValue,@pos+@len)+1

END

— Get the resultset

SELECT * FROM @MyTempTbl

Output you get as below

String Split Result

String Split Result

Note

  • My requirement was to split the string and use in ‘IN’ condition, hence I created a Temporary table.
  • Also you can replace ‘,’ with any of the character

🙂

 

Categories: SQL Tags: ,

Auto generate new GUID for ‘uniqueidentifier’ column in SQL Table

June 30, 2014 1 comment

If you a have a column of type ‘uniqueidentifier’ in your SQL table, and you want to auto generate a new Guid every time when you insert a record, you can use the ‘Default Value or Binding’ property of ‘Column Properties’.

Set Default Value or Binding = newid()

uniqueidentifier Column

uniqueidentifier Column

🙂

Categories: SQL Tags: , ,

SQL Query to fetch top 1 record from each group

I have a SQL table ‘Market’ with 3 Groups (i.e., Fruits, Vegetable and Meat) and below is the data

SQL Table - Market

SQL Table – Market

 

My requirement is

  • From each group fetch the top 1 item by ‘Priority’ column (i.e., Priority = 1) like below
Name Group

Priority

Apple Fruits 1
Fish Meat 1
Potato Vegetable 1

The approach is to provide ‘Rank’ using ROW_NUMBER to records in each group and get the Top 1 record

Below is the Query

— Create a temporary table with ‘Rank’ column

;WITH TempTbl AS

(

SELECT *,

ROW_NUMBER() OVER (

PARTITION BY [Group]

ORDER BY [Priority] ASC

) AS Rank

FROM [dbo].[Market]

)

— Select the Rank = 1 records

SELECT *

FROM TempTbl

WHERE

Rank = 1

🙂

Categories: SQL Tags: , , ,

CRM data load – Finding long running queries

December 17, 2012 Leave a comment

The other day when we were doing performance testing on our CRM application by importing huge data, we got few “Generic SQL Errors”.

One common reason for the “Generic SQL Errors” is either SQL timeout because of long running queries or dead locks.

So to find the root cause of the issue we wanted to find the long running query’s.

Here is the useful article which will give you the “Longest running queries” on your SQL Server.

P.S. You might get “Division by 0 errors” error when you run the query. Keep trying the query you will get results for sure.

🙂

Getting logged in user’s GUID in CRM reports

We can get the logged in user GUID in the custom reports deployed in CRM using “fn_FindUserGuid()”  function

Below is the sample query for the same

DECLARE @currUserId UNIQUEIDENTIFIER

SELECT @currUserId=dbo.fn_FindUserGuid()

PRINT @currUserId

🙂

Database ‘Org_MSCRM’ cannot be started in this edition of SQL Server because it contains a partition function ‘AuditPFN’ – Error while restoring organization

June 16, 2012 1 comment

Hi,

I got below error, when I was trying to restore an organization database from SQL server enterprise edition to SQL server developer / Data center editions

Database ‘Org_MSCRM’ cannot be started in this edition of SQL Server because it contains a partition function ‘AuditPFN’. Only Enterprise edition of SQL Server supports partitioning.

Reason :-

  • When CRM 2011 is installed using a SQL Server Enterprise edition, a partition is automaticallycreated for the auditing functionality of CRM 2011.
  • The AuditBase table then uses partitioning (which is only available for SQL Server Enterprise).

Fix :-

  • Either upgrade current SQL version to Enterprise
  • OR
  • We have to run the script to recreate all the indexes on the Primary partition and then drops the partition
  • Refer below MSDN KB article

http://support.microsoft.com/kb/2567984

Below is the useful article on the same

Link

🙂

Intellisense feature in Sql server management studio 2008 R2 stopped working – Fix

Hi,

You might have lost the Intellisense in ‘Sql server management studio 2008 R2’ after you installed Visual studio 2010 Sp1.

This is a known issue and it got fixed after I installed “Service Pack 1” of Sql server from below link

Download

When you click the above link, from the page download & install “SQLManagementStudio_x64_ENU.exe” and restart the machine

SSMS SP1

SSMS SP1

There might be other reasons for this and it explained in-detail in below article

Link

Hope it helps 🙂

Case statement in Select clause

September 13, 2011 Leave a comment

Hi,

Below is a sample query that has “CASE statement”  in SELECT clause

SELECT EmpID,

CASE
WHEN Salary>=2000 THEN ‘High Salaried’
WHEN Salary<=1000 THEN ‘Medium’
ELSE ‘Below Bar’
END

‘New Column’, *

FROM

Employee

Below is the screenshot for reference

CASE in SELECT Clause

CASE in SELECT Clause

Hope it helps 🙂