Archive

Archive for the ‘SQL Server 2012’ Category

SQL Server 2012: Error handling for multiple errors – MSDN TSQL forum


–> Question:

I have a stored procedure which steps through a list of transaction log backups and applies them to a DR database with NORECOVERY. As part of our DR test process, it is sometimes possible that the DR database gets ahead of the transaction log list. In this circumstance I get the error:

Msg 4326, Level 16, State 1, Line 8
The log in this backup set terminates at LSN 74000000023300001, which is too early to apply to the database. A more recent log backup that includes LSN 74000000025200001 can be restored.

Msg 3013, Level 16, State 1, Line 8
RESTORE LOG is terminating abnormally.

This is fine and I’m happy to ignore this log and move on to the next one. However there appears to be no way to catch the 4326 error. Wrapping the restore in a TRY … CATCH only identifies the 3013 error number and that could be caused by many issues that I don’t want to ignore. If my TRY … CATCH uses the THROW command, both the 4326 and 3013 errors are displayed so my session clearly has a handle to them both.

My questions are:

1. Can I catch the first error thrown?
2. Can I review all the errors thrown?
3. Can the output from THROW be captured in a variable so I can parse it?
 

–> Answer:

While using RAISERROR it will only catch the last error thrown, it won’t catch and return all the errors.

The new THROW keyword introduced in SQL Server 2012 returns all the errors, check this link: https://sqlwithmanoj.com/2015/02/04/capture-multiple-errors-in-try-catch-by-using-throw-statement/

But I don’t think if there is any mechanism to store both the errors returned by any script as after THROW the execution ends and control is transferred to the client.

Other than using DBCC OUTBUFFER(@@spid), you will need to parse the multiple error details spread through several rows. 

Ref link.


Preparing for 70-462 Exam : Administering Microsoft SQL Server 2012 Databases | Certification

May 1, 2015 8 comments

I’m from a Database development background and working mostly with T-SQL code only. I passed Exam 70-461 back in Nov-2012, and now I’m planing to give 70-462 Exam i.e. Administering Microsoft SQL Server 2012 Databases.

This exam is intended for Database Administrators who perform:
– Installation
– Maintenance
– Configuration tasks
– and other responsibilities like:
– – setting up Database systems
– – making sure those systems operate efficiently
– – regularly storing, backing up, and securing data from unauthorized access

Thus being a Database Developer (primarily) this exam is going to be the most toughest and trickiest exam in this series as I don’t have DBA experience and haven’t worked on anything in Database Administration yet.

For preparation I’m referring to Training Kit (Exam 70-462) Administering Microsoft SQL Server 2012 Databases book.

–> The Exam is divided into 6 modules:
1. Install and Configure
2. Maintain Instances and Databases
3. Optimize and Troubleshoot
4. Manage Data
5. Implement Security
6. Implement High Availability

You need to brush up on following Skills in the these Modules:
-:Check links with the below items for more information:-

1. Install and Configure | 19%
– Plan installation
– Install SQL Server and related services
– Implement a migration strategy
– Configure additional SQL Server components
– Manage SQL Server Agent
– PREP Links: Understanding surface area configuration | Hardware and software requirements for installing SQL Server 2012 | Quick-start installation of SQL Server 2012

2. Maintain Instances and Databases | 17%
– Manage and configure databases
– Configure SQL Server instances
– Implement a SQL Server clustered instance
– Manage SQL Server instances
– PREP Links: ALTER DATABASE file and filegroup options (Transact-SQL) | Contained databases | Data compression

3. Optimize and Troubleshoot | 14%
– Identify and resolve concurrency problems
– Collect and analyze troubleshooting data
– Audit SQL Server instances
– PREP Links: blocked process threshold server configuration option | Configure login auditing (SSMS) | Data collection

4. Manage Data | 19%
– Configure and maintain a back-up strategy
– Restore databases
– Implement and maintain indexes
– Import and export data
– PREP Links: Back up and restore of SQL Server databases | File restores (full recovery mode) | DBCC INDEXDEFRAG (Transact-SQL)

5. Implement Security | 18%
– Manage logins and server roles
– Manage database permissions
– Manage users and database roles
– Troubleshoot security
– PREP Links: Server-level roles | Permissions (database engine) | Database-level roles

6. Implement High Availability | 12%
– Implement AlwaysOn
– Implement Database Mirroring
– HADR (High Availability and Disaster Recovery)
– PREP Links: AlwaysOn Availability Groups (SQL Server) | Microsoft SQL Server AlwaysOn solutions guide for HADR | AlwaysOn architecture guide: Building a HADR solution by using AlwaysOn Availability Groups


-–> You can visit following Study materials to prepare for this Exam:

Microsoft Link for this Certification: http://www.microsoft.com/learning/en-us/exam-70-462.aspx

Book on AMAZON.com: Training Kit (Exam 70-462) Administering Microsoft SQL Server 2012 Databases


Will try to update this blog post or will put up a new post with my learning while preparing for this exam.

All The Best!!!

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


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.

Preparing for 70-463 Exam : Implementing a Data Warehouse with Microsoft SQL Server 2012

December 5, 2013 15 comments

After passing 70-461 exam last year I’m now preparing for 70-463 exam i.e. Implementing a Data Warehouse with Microsoft SQL Server 2012. This exam mainly focuses on implementing Data Warehouses with Dimension & Fact tables, working with SSIS packages and Data Quality solutions. Thus being a Database Developer (primarily) this exam will be bit tough for me as I’ve not worked with Dimensional modelling, SSIS and DQS.

For preparation I’m referring to Training Kit (Exam 70-463) Implementing a Data Warehouse with Microsoft SQL Server 2012 book.

This exam is primarily intended for ETL and Data Warehouse (DW) developers who create Business Intelligence (BI) solutions, and whose responsibilities include Data Cleansing, and Extract Transform Load and Data Warehouse implementation.
 

–> The Exam is divided into 5 modules:

1. Design and Implement a Data Warehouse | 11%
– Introducing Star and Snowflake Schemas
– Design and Implement Dimensions
– Design and Implement Fact tables
– Managing the Performance of a Data Warehouse
– PREP links: Introduction to dimensions (Analysis Services – multidimensional data) | Dimension relationships | Columnstore indexes

2. Extract and Transform data | 23%
– Define Connection managers
– Design Data Flow
– Implement Data Flow with Transformations
– Control Flow tasks and Containers, Precedence Constraints
– Manage SSIS package execution
– Implement Script tasks in SSIS
– PREP links: Integration Services (SSIS) connections | Data flow | Slowly changing dimension transformation

3. Load Data | 27%
– Design control flow
– Implement package logic by using SSIS variables and parameter
– Implement Control flow
– Implement Data Load options
– Implement Script components in SSIS
– Slowly Changing Dimensions
– Preparing a Package for Incremental Load
– Package Transactions, Checkpoints, Event Handlers
– PREP links: Integration Services transactions | Developing a custom task | Integration Services (SSIS) parameters

4. Configure and Seploy SSIS solutions | 24%
– Troubleshoot Data Integration issues
– Install and Maintain SSIS components
– Implement Auditing, Logging, and Event handling
– Deploy SSIS solutions
– Configure SSIS security settings
– Data Mining Task and Transformation
– Preparing Data for Data Mining
– Implementing SSIS Fuzzy Transformations
– PREP links: Troubleshooting tools for package development | Load-balancing packages on remote servers by using SQL Server Agent | Integration Services (SSIS) logging

5. Build Data Quality Solutions (DQS) | 15%
– Install and maintain Data Quality services
– Creating and Maintaining a Knowledge Base
– Create a Data Quality project to clean data, Profiling Data and Improving Data Quality
– Using DQS and the DQS Cleansing Transformation
– Implement Master Data Management (MDM) solutions
– Using Master Data Services Add-in for Excel
– PREP links: Install Data Quality Services | Install Master Data Services | Master Data Services features and tasks
 

-–> You can visit following Study materials to prepare for this Exam:

Microsoft Link for this Certification: http://www.microsoft.com/learning/en-us/exam-70-463.aspx

Book on AMAZON.com: Training Kit (Exam 70-463) Implementing a Data Warehouse with Microsoft SQL Server 2012
 

I will try to update this blog post or will put up a new post with my learning while preparing for this exam.

All The Best!!!