Advertisements

Archive

Posts Tagged ‘Denali’

Capture multiple errors in TRY CATCH by using THROW statement

February 4, 2015 4 comments

This post relates to my earlier post [link] where I mentioned on benefit of using THROW clause with same SQL examples.

The THROW clause was introduced in SQL Server 2012 and may be replacing the RAISERROR function in near future.

Normally the SQL statements returns single error, but some SQL statements returns more than one error message when they go wrong due to some reason or exception.
 

–> On executing the below BACKUP statement in SSMS we can see we get two errors:

BACKUP DATABASE [AdventureWorks2012] 
TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'

The above code throws 2 errors with Error-Message IDs 3201 & 3013, as shown below:

error messages:
Msg 3201, Level 16, State 1, Line 2
Cannot open backup device ‘E:\FOLDER_NOT_EXISTS\test.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.

 

-> But when we want to track these errors by using RAISERROR function it just returns the last (single) error message and its details, and the previous error message details are not returned by this function.

BEGIN TRY
	BACKUP DATABASE [AdventureWorks2012] 
	TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'
END TRY
BEGIN CATCH
	DECLARE @msg VARCHAR(1000) = ERROR_MESSAGE()
	RAISERROR(@msg,16,0)
END CATCH

Here, only 1 error message will be returned:

error messages:
Msg 50000, Level 16, State 0, Line 7
BACKUP DATABASE is terminating abnormally.

 

–> With the new THROW clause you won’t see any issue of omitting the previous errors, as it returns all error details thrown by the SQL Statement itself.

BEGIN TRY
	BACKUP DATABASE [AdventureWorks2012] 
	TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'
END TRY
BEGIN CATCH
	THROW;
END CATCH

The above statement throws both the error details as we saw in the first example:
error messages:
Msg 3201, Level 16, State 1, Line 2
Cannot open backup device ‘E:\FOLDER_NOT_EXISTS\test.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.

 

Thus, if you are on SQL Server 2012 and above you must consider using THROW clause instead of the RAISERROR function.

Advertisements

Another reason to use THROW clause instead of RAISERROR | SQL Server 2012

January 24, 2013 4 comments

In my previous post [link] I talked about the new THROW clause introduced in SQL Server 2012 and how it is different from the existing RAISERROR function.

I just stumbled on one more reason to stop using RAISERROR function and start using the new THROW clause.

There are some SQL statements that throws more than one error message when they go wrong due to some reason.

-> When using RAISERROR function it just returns the last (single) error message and its details, but the previous error message details are not returned by this function.

-> With the new THROW clause you won’t see any issue of omitting the previous errors. And it returns all error details as thrown by the SQL statement itself.

Let’s check this with a small example where we want to take backup of a database, but the folder provided does not exist, and hence it errors out:

Example #1:
On executing the below BACKUP statement we can see we get 2 errors:

BACKUP DATABASE [AdventureWorks2012] 
TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'

The above code throws 2 errors with Error-Message IDs 3201 & 3013, as shown below:

error messages:
Msg 3201, Level 16, State 1, Line 2
Cannot open backup device ‘E:\FOLDER_NOT_EXISTS\test.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.


Example #2:
And when you try to use error-handling by using TRY-CATCH and RAISERROR(), it fetches us only 1 error:

BEGIN TRY
	BACKUP DATABASE [AdventureWorks2012] 
	TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'
END TRY
BEGIN CATCH
	DECLARE @msg VARCHAR(1000)
	SET @msg = ERROR_MESSAGE()
	RAISERROR(@msg,16,0)
END CATCH

Here, only 1 error message will be returned:

error messages:
Msg 50000, Level 16, State 0, Line 7
BACKUP DATABASE is terminating abnormally.


Example #3:
But this is not the case with the new THROW clause, as I mentioned previously.
It throws all the errors that are originally thrown by the original SQL statement, like below:

BEGIN TRY
	BACKUP DATABASE [AdventureWorks2012] 
	TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'
END TRY
BEGIN CATCH
	THROW;
END CATCH

The above statement throws both the error details as we saw in the first example:
error messages:
Msg 3201, Level 16, State 1, Line 2
Cannot open backup device ‘E:\FOLDER_NOT_EXISTS\test.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.


Thus, you must consider using THROW clause instead of the RAISERROR function if you are in SQL Server 2012.
But, it still depends upon different conditions and scenarios where you would need either of both the features.

Enhanced OVER() Clause in SQL Server 2012 – Part2

January 2, 2013 8 comments

First of all I would like to wish my readers a very Happy and Prosperous New Year – 2013!!!

This is my 1st post in this new year and I will try to come up with more informative and interesting topics related to SQL Server.

Coming back to the topic, sometime back in Part-1 [link] I discussed about how to use both PARTITION BY & ORDER BY clause within OVER() clause when using it with AGGREGATE functions, like SUM().

Here in this post we will see other features that are added to the OVER() clause.

ROWS/RANGE are new the 2 new clauses that you can use inside the OVER() clause to limit the records within the Partition by specifying start and end within the partition. They use a new set of keywords to define the boundaries within the partition, i.e. PREECEDING, FOLLOWING mixed with UNBOUNDED or CURRENT, or number of rows. The PRECEDING and FOLLOWING rows are defined based on the ordering in the ORDER BY clause of the query.

#1. Using ROWS/RANGE UNBOUNDED PRECEDING with OVER() clause:
Let’s check with 1st example where I want to calculate Cumulative Totals or Running Totals at each row. This total is calculated by the SUM of current and all previous rows.
We will use UNBOUND PRECEDING option here, which means that the window starts from the 1st row till the current row.

USE [AdventureWorks2012]
GO

-- To Calculate Cumulative SUM or Running Totals:
;WITH CTE AS (
	SELECT BusinessEntityID AS SalesPersonID, CAST([Rate] AS DECIMAL(10,0))AS Salary, [ModifiedDate] AS SalDate
	FROM [HumanResources].[EmployeePayHistory]
	WHERE BusinessEntityID <= 10
	)
SELECT SalesPersonID, SalDate, Salary
   ,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS UNBOUNDED PRECEDING) AS CumulativeSumByRows
   ,SUM(Salary) OVER (ORDER BY SalesPersonID RANGE UNBOUNDED PRECEDING) AS CumulativeSumByRange
FROM CTE
ORDER BY SalesPersonID, SalDate

OUTPUT:
SQL2012_OVER_Enhanced_Clause_2_1

In the Output above you can see the was the SUM is calculated, as we go down all previous and current rows are getting summed at all levels.
Here you can also see a difference between the way ROW & RANGE are calculated, for SalesPersonID=4:
– ROWS shows different and continuous calculation at different levels.
– But RANGE shows same calculation of all 3 records at different levels.


#2. Using ROWS/RANGE CURRENT ROW & UNBOUNDED FOLLOWING with OVER() clause:
Now, let’s say you’ve to do the same calculation, but in reverse order. So, here we will use BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING options to define our Window. This means the window starts from current row and ends at the last row.

-- To Calculate Cumulative SUM or Running Totals, but in REVERSE order:
;WITH CTE AS (
	SELECT BusinessEntityID AS SalesPersonID, CAST([Rate] AS DECIMAL(10,0))AS Salary, [ModifiedDate] AS SalDate
	FROM [HumanResources].[EmployeePayHistory]
	WHERE BusinessEntityID <= 10
	)
SELECT SalesPersonID, SalDate, Salary
   ,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS ReverseCumulativeSumByRows
   ,SUM(Salary) OVER (ORDER BY SalesPersonID RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS ReverseCumulativeSumByRange
FROM CTE
ORDER BY SalesPersonID, SalDate

OUTPUT:
SQL2012_OVER_Enhanced_Clause_2_2

In the Output above we can see that the Running totals are now in reverse order, compared to the 1st example.
Also, the ROWS & RANGE difference can be seen for the SalesPersonID=4 similar to the 1st example.


#3. Using ROWS/RANGE PRECEDING & FOLLOWING with OVER() clause, without UNBOUNDED:
Now, if you want to calculate the SUM only of those values that are just adjacent to a current row. Here we will use combination of row numbers with CURRENT/PRECEDING & FOLLOWING keywords.
Let’s us check this by following example:

-- To Calculate Moving SUM, by taking 3 moving rows:
;WITH CTE AS (
	SELECT BusinessEntityID AS SalesPersonID, CAST([Rate] AS DECIMAL(10,0))AS Salary, [ModifiedDate] AS SalDate
	FROM [HumanResources].[EmployeePayHistory]
	WHERE BusinessEntityID <= 10
	)
SELECT SalesPersonID, SalDate, Salary
   ,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS MovingSumByRowsPrevCurrentNext
   ,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS MovingSumByRowsCurrentNext2
FROM CTE
ORDER BY SalesPersonID, SalDate

OUTPUT:
SQL2012_OVER_Enhanced_Clause_2_3

In the output above for:
– Column MovingSumByRowsPrevCurrentNext: it calculates the SUM of just one Previous, Current & one Next row.
– Column MovingSumByRowsCurrentNext2: it calculates the SUM of Current and next two rows.


So, by above 3 examples we saw how to use new ROWS & RANGE clauses with a mix of UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING, CURRENT ROW, etc. Similarly there could be many different cases and scenarios where we can use there clauses and options.

I will try to post more on these features as I see anything new and exciting around this.

For more info on OVER() Clause check MS BOL: http://msdn.microsoft.com/en-us/library/ms189461.aspx

New THROW statement in SQL Server 2012 (vs RAISERROR)

December 20, 2012 7 comments

Newly introduced THROW keyword in SQL server 2012 is an improvement over the existing RAISERROR() statement. Yes, it’s single ‘E’ in RAISERROR.

Both RAISERROR & THROW can be used in T-SQL code/script to raise and throw error within a TRY-CATCH block. Check my previous post for TRY-CATCH block, [link].

–> With RAISERROR developers had to use different ERROR_xxxx() system functions to get the error details to pass through the RAISERROR() statement, like:
– ERROR_NUMBER()
– ERROR_MESSAGE()
– ERROR_SEVERITY()
– ERROR_STATE()

let’s see an example:

-- Using RAISERROR()
DECLARE  
	@ERR_MSG AS NVARCHAR(4000)
	,@ERR_SEV AS SMALLINT
	,@ERR_STA AS SMALLINT

BEGIN TRY
	SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
	SELECT @ERR_MSG = ERROR_MESSAGE(),
		@ERR_SEV =ERROR_SEVERITY(),
		@ERR_STA = ERROR_STATE()
	SET @ERR_MSG= 'Error occurred while retrieving the data from database: ' + @ERR_MSG

	RAISERROR (@ERR_MSG, @ERR_SEV, @ERR_STA)  WITH NOWAIT
END CATCH
GO

Output:
(0 row(s) affected)
Msg 50000, Level 16, State 1, Line 15
Error occurred while retrieving the data from database: Divide by zero error encountered.

The RAISERROR() can take first argument as message_id also instead of the message. But if you want to pass the message_id then it has to be in sys.messages

–> With THROW the benefit is: it is not mandatory to pass any parameter to raise an exception.
Just using the THROW; statement will get the error details and raise it, as shown below:

-- Using THROW - 1
BEGIN TRY
	SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
	THROW;
END CATCH
GO

Output:
(0 row(s) affected)
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

As you see in the Output above, the error message thrown is the default one. But you can also add your customized message, we will see below.

IMP NOTE: THROW will show the exact line where the exception was occurred, here the line number is 2. But RAISERROR will show the line number where the RAISERROR statement was executed i.e. Line 15, but not the actual exception position.

Also passing the message_id won’t require it to be stored in sys.messages, let’s check this:

-- Using THROW - 2
DECLARE  
	@ERR_MSG AS NVARCHAR(4000)      
	,@ERR_STA AS SMALLINT      

BEGIN TRY
	SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
	SELECT @ERR_MSG = ERROR_MESSAGE(),
		@ERR_STA = ERROR_STATE()

	SET @ERR_MSG= 'Error occurred while retrieving the data from database: ' + @ERR_MSG;

	THROW 50001, @ERR_MSG, @ERR_STA;
END CATCH
GO

Output:
(0 row(s) affected)
Msg 50001, Level 16, State 1, Line 14
Error occurred while retrieving the data from database: Divide by zero error encountered.

But if you parameterize the THROW statement as above it will not show the actual position of exception occurrence, and the behavior will be same as RAISERROR(). As with RAISERROR() you’ve to provide mandatory params, so there is no way to get the actual position of Line where the error occurred.

As per MSBOL following are the difference between RAISERROR & THROW:

RAISERROR statement

THROW statement

If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.

The error_number parameter does not have to be defined in sys.messages.

The msg_str parameter can contain printf formatting styles.

The message parameter does not accept printf style formatting.

The severity parameter specifies the severity of the exception.

There is no severity parameter. The exception severity is always set to 16.

NOTE: As per MS BOL for exception handling in new development work THROW must be used instead of RAISERROR.

SQL Server 2012 does not support Linked Server to SQL Server 2000, workaround

December 10, 2012 24 comments

Yes, you read it right, “SQL Server 2012” has stopped connecting to “SQL Server 2000” via linked Servers. As this new version uses a new Native Client version i.e. SQLNCLI11, instead of the old SQLNCLI10. This new client only connects back to 2008R2, 2008 and 2005 only.

– I upgraded my Database Servers from “SQL Server 2008 R2” to “SQL Server 2012”.

– Restored the databases from the backup taken from 2008R2.

– Ran the jobs and found that one of them was failing with following error:
 

Error Message:
OLE DB provider “SQLNCLI11” for linked server “NorthWind2000” returned message “Client unable to establish connection”.
Msg 22, Level 16, State 1, Line 0
SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions.
OLE DB provider “SQLNCLI11” for linked server “NorthWind2000” returned message “Invalid connection string attribute”.


 

As I have upgraded from 2008 R2, so I checked Providers under the Linked Server and found that with the new SQLNCLI11, I still have the “SQL Server Native Client 10.0” i.e. “SQLNCLI10”.

So, I tried to create the Linked Server by using “SQLNCLI10”, but it again gave an error, as follows:

Msg 8522, Level 16, State 3, Line 1
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.


 

I scripted out the DDL of my existing Linked Server, as below:

USE [master]
GO

-- Existing LinkedServer [NorthWind2000]:
EXEC sp_addlinkedserver	@server = N'NorthWind2000', 
						@srvproduct=N'MSSQL', 
						@provider=N'SQLNCLI', 
						@provstr=N'PROVIDER=SQLOLEDB;SERVER=NorthWind'

EXEC sp_addlinkedsrvlogin   @rmtsrvname=N'NorthWind2000', 
							@useself=N'True', 
							@locallogin=NULL, 
							@rmtuser=NULL, 
							@rmtpassword=NULL
GO

 

=> WORKAROUND / FIX:

Now as a workaround to make this Linked Server work we have an option to use the ODBC Data Source which will connect to our remote server.
There are 2 approaches:

1. Either we create an ODBC Data Source (DSN) and use it in our Linked Server.

2. Or, use the Data Source (DSN) connection string directly in the Linker Server Provider.
 

–> Using appraoch #1:

Create an ODBC Data Source:
1. Open Control Panel, go to Administrative Tools, then “Data Sources (ODBC)”.
2. On “ODBC Data Source Administrator” window go to “System DSN” Tab.
3. Here click on Add to create a new DSN.
4. Choose “SQL Server” and click Finish.
5. On the new window, give a proper name for the Source DSN (like: NorthWind2000DSN), we will use this name while creating our Linked Server. Provide the Server name which is on SQL Server 2000, here “NorthWind”. Click Next.
6. Choose the Authentication Type, either Windows or SQL Server auth. Click Next.
7. Change the default database, not necessary. Click Next.
8. Click Finish. You will see a new DSN created under System DSN tab.

Now, create Linked Server and provide this DSN in the @datasrc param and provide the @provider param “MSDASQL”.

You can use the below query to create the same:

USE master
GO
-- Drop Existing LinkedServer [NorthWind2000]:
EXEC sp_dropserver @server=N'NorthWind2000', @droplogins='droplogins'
GO

-- Re-create LinkedServer [NorthWind2000] by using the ODBC connection:
EXEC sp_addlinkedserver @server = N'NorthWind2000', 
						@srvproduct=N'MSDASQL', 
						@provider=N'MSDASQL', 
						@datasrc = N'NorthWind2000DSN', 
						@location=N'System';

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'NorthWind2000', 
						  @useself=N'True', 
						  @locallogin=NULL, 
						  @rmtuser=NULL, 
						  @rmtpassword=NULL
GO

 

–> Using appraoch #2:

We can also directly put the DSN connection String in the Provider String @provstr param.
Let’s check it below:

USE master
GO
-- Drop Existing LinkedServer [NorthWind2000]:
EXEC sp_dropserver @server=N'NorthWind2000', @droplogins='droplogins'
GO
-- Re-create LinkedServer [NorthWind2000] by using the ODBC connection:
EXEC sp_addlinkedserver @server = N'NorthWind2000', 
						@srvproduct=N'', 
						@provider=N'MSDASQL', 
						@provstr=N'DRIVER={SQL Server};SERVER=NorthWind;Trusted_Connection=yes;'

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'NorthWind2000', 
						  @useself=N'True', 
						  @locallogin=NULL, 
						  @rmtuser=NULL, 
						  @rmtpassword=NULL
GO

This way you can query SQL Server 2000 data from SQL Server 2012 via Linked Servers by using ODBC DSN.

This seems seamless but it is an indirect process and a workaround to query SQL Server 2000 database.
To make your queries or ETLs efficient it is advisable to upgrade to a higher version, at-least SQL Server 2005.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.