Advertisements

Archive

Archive for March, 2015

Standardize TRY CATCH block across all Stored Procedures – MSDN TSQL forum

March 31, 2015 Leave a comment

–> Question:

I am trying to develop best try catch block to be standardized in all of the SPs. Below is what I have right now developed which is giving me proper formatted output, would like to add more details or remove unwanted items from it.

Thanks for all your help!

I am using 2008 R2 Version, would be migrating to 2012 soon.

BEGIN CATCH
  IF @@TRANCOUNT > 0
  BEGIN
     ROLLBACK TRANSACTION
  END

DECLARE @ErrorMsg varchar(2100);

SET  @ErrorMsg =  ' DBName        = ' + DB_NAME() + CHAR(10)
+ ' ErrorMessage  = ' + LTRIM(CONVERT(VARCHAR(2047), LEFT(Error_Message(),2044))) + CHAR(10) 
+ ' Procedure     = ' + OBJECT_NAME(@@PROCID) + CHAR(10) + ' ErrorNumber   = ' + 
LTRIM(CONVERT(VARCHAR(9),Error_Number()))  + CHAR(10) 
+ ' ErrorState    = ' + LTRIM(CONVERT(VARCHAR(3),Error_State())) + CHAR(10)
+ ' ErrorSeverity = ' + LTRIM(CONVERT(VARCHAR(3),Error_Severity()))  + CHAR(10)
+ ' LineNumber    = ' + LTRIM(CONVERT(VARCHAR(9),Error_Line()))  + CHAR(10)
+ ' ErrorDT       = ' + CONVERT(VARCHAR(23),GETDATE(),121) + CHAR(10) + ' ErrorBy = ' + SUSER_SNAME();

RAISERROR(@ErrorMsg,16,1);
    RETURN -1
END CATCH

 

–> My Answer:

The way you’ve drafted your TRY-CATCH looks good, however you can also consider few things:

1. Use THROW instead of RAISERROR as recommended by Microsoft, this will also reduce unnecessary (at times) declaration and assignment of error related variables, link.

2. With THROW you can also catch multiple errors, RAISERROR will only throw last error raised, link.

3. Make use of XACT_ABORT only when you want to rollback the entire transaction, link.

Check all these and other stuff about TRY-CATCH in my blog posts.
 

Ref Link.


Advertisements

SQLNCLI11 for linked server “XYZ” returned message “Requested conversion is not supported” | SQL Server 2012 upgrade behavior changes

March 27, 2015 1 comment

This was a year long pending post that got lost in my blog posts archives. Today while filtering the Posts on my Dashboard I saw this in Edit mode. So I thought to make it live after doing some edits. So, here it goes 🙂

While upgrading to SQL Server 2012 from SQL Server 2008 R2 on my DEV box, I observed some behavioral changes with SQL 2012, one ETL job that was working fine on SQL 2008 R2 was not running and throwing following error in SQL 2012:

OLE DB provider “SQLNCLI11” for linked server “MyLocal” returned message “Requested conversion is not supported.”.
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column “(user generated expression).Expr1003” from OLE DB provider “SQLNCLI11” for linked server “MyLocal”.

The above error depicts that there is something wrong with the Linked Server. But the same Linked Server was working for other tables, and it was failing for a specific table only. I checked the table and the ETL script where the Linked Server was being used. That script had a SELECT list fetching records from source table via Linked Server. The column list was having a computed column in the end like: CAST(NULL as UNIQUEIDENTIFIER) AS U_ID. I was not sure why anybody would write that code and what was the need to add this computed column. I removed this column and the error was gone. So it was clear that the CASTing of NULL to UNIQUEIDENTIFIER datatype was throwing this error, but the same code was working fine in SQL Server 2008 R2.

(Please check at the bottom of the post for more such behavioral changes)
 

–> Here is the issue that I’ve reproduced:

This will run fine in previous versions of SQL Server, but will throw error in 2012:

–> CREATE Linked Server

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver 
	@server = N'MyLocal', 
	@srvproduct=N'MSSQL', 
	@provider=N'SQLNCLI', 
	@datasrc=N'MANOJPANDEY-PC', -- plz change the server name here.
	@provstr=N'PROVIDER=SQLOLEDB;SERVER=MY-PC' -- plz change the server name here.
 
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLocal',@useself=N'True',
	@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

–> Create a new table:

USE [AdventureWorks2012]
GO

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
INTO dbo.Person
FROM [AdventureWorks2012].[Person].[Person]

–> Query table via Linked Server:

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
FROM [MyLocal].[AdventureWorks2012].dbo.[Person]

–> Query it after adding a column via Linked Server:

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
	,CAST(NULL as UNIQUEIDENTIFIER) AS U_ID
FROM [MyLocal].[AdventureWorks2012].[dbo].[Person]

OLE DB provider "SQLNCLI11" for linked server "MyLocal" returned message "Requested 
conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "(user generated expression).Expr1003" 
from OLE DB provider "SQLNCLI11" for linked server "MyLocal".

 

–> Creating a View in Remote Server:

CREATE VIEW [dbo].[vwPerson]
AS
SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
	,CAST(NULL as UNIQUEIDENTIFIER) AS U_ID
FROM [MyLocal].[AdventureWorks2012].[dbo].[Person]

–> View also fails, as it is also expanded (same error as above):

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate, U_ID
FROM [MyLocal].[AdventureWorks2012].[dbo].[vwPerson]

 

–> Workaround 1:

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
	,CAST(CAST(0 AS BINARY) AS UNIQUEIDENTIFIER) AS U_ID
FROM [MyLocal].[AdventureWorks2012].[dbo].[Person]

–> Workaround 2:

SELECT 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, 
	Suffix, EmailPromotion, ModifiedDate
	,CAST(0x AS UNIQUEIDENTIFIER) AS U_ID
FROM [MyLocal].[AdventureWorks2012].[dbo].[Person]

 

–> Final Cleanup:

DROP TABLE dbo.Person
DROP VIEW dbo.vwPerson
GO

USE [master]
GO

EXEC master.dbo.sp_dropserver @server=N'MyLocal', @droplogins='droplogins'
GO

 
–> I’ve documented more behavioral changes after SQL Server 2012 upgrade, and here is the list:

1. IDENTITY column value hop

2. Temp #Tables created with negative IDs

3. No native Linked Server support to SQL Server 2000


GROUPING SETS equivalent | for SQL Server 2005 and below

March 23, 2015 4 comments

Sometime back I discussed about [GROUPING SETS] in my previous post and today one reader of this blog asked me how we can create the same result-set without using GROUPING SETS if we are on previous versions like SQL Server 2005 or 2000.
 

–> Let’s take the following SQL Query that uses GROUPING SETS operator and let’s see what Output we get:

-- GROUPING SETS
SELECT class, section, rollno, sum(marks) [sum]
FROM #tempTable
GROUP BY GROUPING SETS (
	 (class, section, rollno)
	,(class, section)
	,(class)
	,()
)
Output:
class		section	rollno	sum
HighSchool	a	1	80
HighSchool	a	2	70
HighSchool	a	3	80
HighSchool	a	NULL	230
HighSchool	b	4	90
HighSchool	b	5	90
HighSchool	b	6	50
HighSchool	b	NULL	230
HighSchool	NULL	NULL	460
Intermediate	a	1	60
Intermediate	a	2	50
Intermediate	a	3	80
Intermediate	a	NULL	190
Intermediate	b	4	90
Intermediate	b	5	50
Intermediate	b	6	70
Intermediate	b	NULL	210
Intermediate	NULL	NULL	400
NULL		NULL	NULL	860

You can refer to the DDL and population of this #tempTable in my previous post, [link].
 

–> And now let’s see how can we recreate the same result set without using GROUPING SETS if we are on lower versions of SQL Server (<= 2005):

-- ROLLUP and Grouping Sets Equivalent (pre SQL 2008)
SELECT class, section, rollno, marks as [sum]
FROM #tempTable

UNION ALL

SELECT class, section, null as rollnu, sum(marks) [sum]
FROM #tempTable
GROUP BY class, section

UNION ALL

SELECT class, null, null, sum(marks) [sum]
FROM #tempTable
GROUP BY class

UNION ALL

SELECT null, null, null, sum(marks) [sum]
FROM #tempTable

ORDER BY class DESC, section DESC, rollno DESC

 

Thanks, please let me know your thoughts and comments!!!


Categories: SQL Tips Tags: , , ,

Dynamic SQL usage, when to use? – MSDN TSQL forum

March 4, 2015 Leave a comment

–> Question:

Can someone tell when or where to use Dynamic SQL ( exec sp_executesql and exec commands) over normal SQL?
 

–> My Answer:

Dynamic SQL queries should be avoided and one should put more thought and time on creating non-dynamic SQL statements.

But there could be some rare scenarios or requirements where you need to create Dynamic SQL, like doing some multiple DBA activities in one go, like:

– Enabling/Disabling multiple Jobs at once, link.

– Creating Dynamic PIVOT where there could be multiple columns based on multiple rows, link.

– Use sp_executeSQL instead of EXEC (SQL statement) to avoid SQL-Injection.

– Check this article by Erland on Dynamic SQL.

– Check this blog post for EXEC (SQL statement) vs sp_executeSQL.
 

Ref Link.