SQL SERVER – SPARSE Property Doesn’t Carry Forward Using SELECT INTO

During one of the recent posts on a SQLBangalore UG FB page, one of the members asked an interestingly simple question. He was asking why is it that when you use SELECT-INTO to create a new table the triggers are not getting carried forward as part of the definition. A number of folks answered and it was a great learning experience for me personally. This blog was inspired from that post and I thought to add a different spin to it.

Though the basic premise of Triggers etc. are written on the FB page, let me ask you a simple question. Though using SELECT-INTO bring forward most of the attributes of the columns during the definition. It is not always true that the definitions will move forward.

One of the outlets that I found recently is the definition of the columns as SPARSE. One person asked that I was writing a lot of blogs around SPARSE columns and if the same will be taken forward while doing a SELECT INTO. The best to learn this is by testing in our environment, here is the script I have used:

-- Create a table and insert a row
CREATE TABLE tbl_host (Age SMALLINT sparse, Name VARCHAR(25));
INSERT INTO tbl_host VALUES (24, 'SQLAuthority');
-- Create a new table from the original
SELECT * INTO tbl_Copy FROM tbl_host;
-- See if the sparse property was carried over
name AS colname,
FROM sys.columns
-- Clean up time
DROP TABLE tbl_host

The output of the above script would look like:


From this it is evident that the SPARSE property is not carried forward when we use SELECT-INTO to create a new table. I would love to learn from you folks on other things that don’t carry forward when we use SELECT-INTO. Tell me what scenario’s you were surprised and learnt something new while using the SELECT-INTO clause. Use the comments section to share your knowledge.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Startup Problem – FIX – Error 5118, Severity 16, State 1

I have been monitoring some of the error messages that SQL Server has been throwing in the recent versions and they are self-explanatory. If we understand the English, then half the problem is solved. It becomes easier and quicker for an administrator if they ever encounter such errors. This blog is an inspiration to one such error which one of my readers found it difficult to understand because he was just hired from college.

My blog reader contacted me via comments and told that he is not able to start SQL Service. Without any delay, I have asked to share the error message. He told that he is stating it from Services.msc and getting an error:

startup 01 SQL SERVER   Startup Problem – FIX    Error    5118, Severity 16, State 1
Windows could not start the SQL Server (SQL2014) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 3417.

I told that this is not an error which can help much and I asked to share SQL Server ERRORLOG. Since he was not a SQL person, he was not aware of that file. I shared below blog:

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

When I looked into the ERRORLOG, found below messages.

2015-06-10 20:54:44.00 spid7s      Starting up database ‘master’.
2015-06-10 20:54:44.00 spid7s      Error: 5118, Severity: 16, State: 1.
2015-06-10 20:54:44.00 spid7s      The file “C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\master.mdf” is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

I asked the question – Did you make some changes with the file master.mdf? He said NO so I asked to share screenshot.

startup 02 SQL SERVER   Startup Problem – FIX    Error    5118, Severity 16, State 1

Blue color of the file indicates that files are compressed and that’s the precise error, we are seeing in the ERRORLOG also. I asked him to un-compress the file using below option.

startup 03 SQL SERVER   Startup Problem – FIX    Error    5118, Severity 16, State 1

After that he was able to start SQL Service. I asked him the changes which are done and he told that due to disk space issues, complete C:\ drive was compressed.

Have you ever enabled compression on SQL machines on the folder which has SQL database files? Never do that! Have you ever encountered these errors? Let me know some of the reasons you did so and faced this issue.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #024 – What is the Best Recovery Model?

Here is one of the most popular questions I often see people asking on the internet.

Question: What is the best recovery model for my database?

Answer: Every situation is different and each situation has different needs for the recovery model.

SQL Server offers three recovery models: full recovery, simple recovery and bulk-logged recovery. The recovery models determine how much data loss is acceptable and determines whether and how transaction logs can be backed up.

Select Simple Recovery Model if:
* Your data is not critical.
* Losing all transactions since the last full or differential backup is not an issue.
* Data is derived from other data sources and is easily recreated.
* Data is static and does not change often.

-- Set the recovery model to Simple

Select Bulk-Logged Recovery Model if:
* Data is critical, but logging large data loads bogs down the system.
* Most bulk operations are done off hours and do not interfere with normal transaction processing.
* You need to be able to recover to a point in time.

-- Set the recovery model to Bulk Logged

Select Full Recovery Model if:
* Data is critical and no data can be lost.
* You always need the ability to do a point-in-time recovery.
* Bulk-logged activities are intermixed with normal transaction processing.
* You are using replication and need the ability to resynchronize all databases involved in replication to a specific point in time.

-- Set the recovery model to Full

You can switch from any recovery model to another recovery model, but prior to or after the switch, you may need to issue additional transaction log or full backups to ensure you have a complete backup set.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Server: Login failed for User ‘NT AUTHORITY\ANONYMOUS LOGON’

Errors related to logins are one of the most searched in this blog. Many of the blogs that talk about these errors are the ones that get viewed often too. This is one of the interesting error which you might see in your environments. One of my blog reader contacted me and told that their event log and ERRORLOG is full of below messages:

Error: 18456, Severity: 14, State: 11.
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. [CLIENT: x.x.x.x]

Very first thing which should catch your eye would be “ANONYMOUS LOGON”. It is also referred as NULL session. My first ask from him was if he was seeing any SPN related errors in SQL Server ERRORLOG?

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

Here is the message which we found in ERRORLOG

2015-06-09 16:25:59.86 Server      SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

In above lines, SQL is trying to register automatically. But below indicates that SQL was unable to do so.

2015-06-09 16:26:06.90 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SQLA.mydomain.com:SQL2014 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

2015-06-09 16:26:06.90 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SQLA.mydomain.com:1500 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

So, the fix of the problem would be to create SPN which are needed for this SQL Instance. We need to use SETSPN.exe and create SPNs which SQL was trying to register. How would one get the tool? As per https://technet.microsoft.com/en-us/library/cc731241.aspx
Setspn is a command-line tool that is built into Windows Server 2008. It is available if you have the Active Directory Domain Services (AD DS) server role installed. To use setspn, you must run the setspn command from an elevated command prompt. To open an elevated command prompt, click Start, right-click Command Prompt, and then click Run as administrator.

For a TCP/IP connection the SPN is registered in the format of MSSQLSvc/<FQDN>:<tcpport>. We should remember that both named instances and the default instance are registered as MSSQLSvc, but <tcpport> value to would be different for instances. Here are the commands if you want to create them manually.

  1. To List SPN we need -L parameter.

SetSPN -L domain\account

  1. To create/add SPN we need to use -A parameter

SetSPN –A MSSQLSvc/<SQL Server FQDN>:<port> <Domain\Account>

While searching for SETSPN.exe command I came across this nice tool which can help a lot of pains like syntax error etc. is called as “Microsoft® Kerberos Configuration Manager for SQL Server®” which can be downloaded from https://www.microsoft.com/en-us/download/details.aspx?id=39046

The good piece about this tool is that it can help in finding missing SPN and also provide script to run or fix it directly, if you have permission. Basically, it can

  • Gather information on OS and Microsoft SQL Server instances installed on a server.
  • Report on all SPN and delegation configurations on the server.
  • Identify potential problems in SPNs and delegations.
  • Fix potential SPN problems.

Once SPN was created, we were able to fix the issue.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How Many Line of Code Do You Have in Database?

Last weekend I happened to be in one of the beautiful cities in Sri Lanka for some fun time with SQL Enthusiasts. We had our first ever SQLSaturday Event in Sri Lanka and it was just too much fun to be amongst the amazing audiences. I was fortunate to deliver a couple of sessions and not to forget the hours and hours of interaction with the attendees post session.

SQLSaturdays and Usergroups are an amazing way to learn and share your experience with our SQL lovers from the neighboring country. I couldn’t have asked for a better reception and hospitality. I am eagerly looking forward for the future SQLSaturdays too.

srilanka beach SQL SERVER   How Many Line of Code Do You Have in Database?

In one of those late night SQL talks, one of my friends asked an interesting question. They were a product based company and one of their clients was asking how many total lines of code they have in their product. My instinct was to say – “Doesn’t Visual Studio help you find this in a jiffy? Getting this data must have been some right click somewhere?” My friend looked at me and said – “Pinal, I never had a problem with my .NET code because VS did help him in a lot of ways. His problem was when working with SQL Server.” Trust me, I am no VS expert here – but the SQL part of the question did get me thinking. I said – “I think I can come up with something may be a couple of days after returning to India”.

I thought of putting together something rudimentary and basic to get the ball rolling. So here is my first shot at this requirement.

DB_NAME(DB_ID()) [DB_Name],
COUNT(*)   AS Object_Count,
SUM(LinesOfCode) AS LinesOfCode
LEN(definition)- LEN(REPLACE(definition,CHAR(10),'')) AS LinesOfCode,
FROM sys.all_sql_modules a
JOIN sysobjects  s
ON a.OBJECT_ID = s.id
-- AND xtype IN('TR', 'P', 'FN', 'IF', 'TF', 'V')
) SubQuery

The above script gives a high level numbers of the LOC inside SQL Server by counting the number of “Carriage return” keys in our various functions, modules, procedures, triggers etc. I know it may not be perfect or scientific. But this is my first shot at this requirement. I ran the same against the AdventureWorks database to get the below output.

 SQL SERVER   How Many Line of Code Do You Have in Database?

I would love to know any other methods you have ever used in your environments for this requirement? Do you need such numbers in your projects? What have you done for such requirements? Let me know via your comments so that we can learn together.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – The Basics of the Execute Process Task – Notes from the Field #084

[Note from Pinal]: This is a new episode of Notes from the Field series. Every time I give an introductory note, however, this time there is no need of intro note. This note is from Andy and as we all know he is amazing person when we have to understand the fundamentals. He has written this blog post with such an interesting way that you must read it to understand the very basic of the file system task.

andyleonard SQL SERVER   The Basics of the Execute Process Task   Notes from the Field #084

Many data integration scenarios involve executing some other process, whether starting a custom application or performing an operating system operation.

Remember: SSIS is a software development platform. With “SQL Server” included in the name, it is easy for people to confuse SSIS as a database tool or accessory, but Control Flow Tasks put that confusion to rest.

SSIS provides several Control Flow tasks. Here is a list that provides a good approximation of which tasks I use most, from most-used to least-used:

In this article I provide an example of configuring the SSIS Execute Process Task, shown in Figure 1:

notes 84 1 SQL SERVER   The Basics of the Execute Process Task   Notes from the Field #084
Figure 1: SSIS Execute Process Task

As with the File System Task, the Execute Process Task provides yet another way to implement an SSIS Design Pattern for source file archival via file compression. When you first open the Execute Process Task Editor, you will note several properties in the property grid, as shown in Figure 2:

notes 84 2 SQL SERVER   The Basics of the Execute Process Task   Notes from the Field #084
Figure 2: SSIS Execute Process Task Properties

An important property is the Executable property which holds the path to the application or process you wish to start with the Execute Process Task. In this case, I am going to start the 7-Zip command line executable to zip a data file. 7-zip is a free file compression utility, and the command line utility is pretty cool. On my system, the 7-Zip command line utility is located at “C:\Program Files\7-Zip\7z.exe” so I configure the Executable property of the Execute Process Task as shown in Figure 3:

notes 84 3 SQL SERVER   The Basics of the Execute Process Task   Notes from the Field #084
Figure 3: The Execute Process Task Editor with the Executable Property Configured

The Arguments property allows me to specify command line arguments to the executable. For 7-Zip, the “a” argument informs the application that I want to add files to a compressed file. The text following the “a” argument specifies the name of the compressed file. The argument that follows the name of the compressed file configures the file (or files) to add. My arguments property reads:

a E:\Projects\7Zip\data\archive1.7z E:\Projects\7Zip\data\test.csv

These arguments tell the 7z.exe executable that I want to add the E:\Projects\7Zip\data\test.csv file to a compressed file named E:\Projects\7Zip\data\archive1.7z, as shown in Figure 4:

notes 84 4 SQL SERVER   The Basics of the Execute Process Task   Notes from the Field #084
Figure 4: The Execute Process Task Editor with the Arguments Property Configured

I can configure other Execute Process Task properties. For example, I choose to hide the command line window for 7-Zip when it executes. To do so, I set the WindowStyle property to Hidden, as shown in Figure 5:

notes 84 5 SQL SERVER   The Basics of the Execute Process Task   Notes from the Field #084
Figure 5: The Execute Process Task Editor’s WindowStyle Property

The SSIS Execute Process Task is now configured to compress a file. Let’s test it! Click the OK button to close the Execute Process Task Editor. Press the F5 key or select SSIS->Start Debugging to test your work. My result is shown in Figure 6:

notes 84 6 SQL SERVER   The Basics of the Execute Process Task   Notes from the Field #084
Figure 6: Successful Test Execution of the SSIS Execute Process Task

Viewing the source and destination directories, we see the file was successfully moved – shown in Figure 7:

notes 84 7 SQL SERVER   The Basics of the Execute Process Task   Notes from the Field #084
Figure 7: The File, Compressed!

As I stated earlier, the SSIS Execute Process Task is powerful, flexible, and robust. This article has demonstrated another way you can use the Execute Process Task to compress files for archiving. Archiving files after loading the data they contain is a common practice in data integration.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Security Auditing With ApexSQL Audit

SQL Servers and databases are probably the most critical IT parts of an enterprise. The most important and confidential data are stored there and even a potential issue with that data can be business critical. In situations where significant preventive/protective actions are applied, such measures will not be of any help in determining the cause of an incident when it occurs. For that purpose, establishing the SQL Server continuous auditing and especially being informed, timely of security configuration changes have been of the utmost importance.

SQL Server security auditing with ApexSQL Audit ensures the early or immediate detection of any SQL Server security changes allowing the DBAs to prevent it to cause an unwanted security breach or even worse potential data loss, interruption or hindered server operations. Being informed, timely about any security issue or even about the potential issue is not important only when immediate actions are required, but also for continuous auditing purposes.

In order to fulfill not only basic auditing requirements and to ensure the granular and precise auditing, ApexSQL Audit is featured with two types of auditing filters: Simple and Advanced.

Even though it is named “Simple” for its ease of use, this is quite a comprehensive filter which allows a high level of granularity in filtering. In situation when SQL Server security auditing is required, the following security related events can be specified for auditing at the server and database level.

Simple filter

Server level security related events:

 apexaudit 01 SQL SERVER   Security Auditing With ApexSQL Audit

Additionally, there is an easy option to include or exclude specific login from auditing using the Logins filter when such requirement exists (excluding the trusted users for example)

apexaudit 02 SQL SERVER   Security Auditing With ApexSQL Audit

Database level security related events:

 apexaudit 03 SQL SERVER   Security Auditing With ApexSQL Audit

ApexSQL Audit’s simple filter allows specifying SQL Server security auditing requirement for each security operation independently, but what’s more ApexSQL Audit allows specifying filtering conditions for all individual SQL Server auditing operation on both, server and database levels.

With a few simple mouse clicks, the desired SQL Server security auditing can be set according to specific security requirements, and Simple filter is the fastest way for configuring auditing of any important auditing requirements.

Advanced filter

Even though it is highly granular, the simple filter still doesn’t allows meeting some fine tuning requirements imposed by some advanced users or specific auditing requirements, so ApexSQL Audit also has a highly granular filter that can meet even the most demanding SQL Server auditing/compliance requirements and auditing precision. The Advanced filter utilizes a logical set of operators that can be selected for defining the auditing filter condition.

The image below displays all data fields available in the advanced auditing filter:

apexaudit 04 SQL SERVER   Security Auditing With ApexSQL Audit

Depending on the data field selected is, is not, contain and does not contain operators will be available for filtering. By using the advanced filter, user can create the auditing rule that will ensure any level of auditing precision required

apexaudit 05 SQL SERVER   Security Auditing With ApexSQL Audit

The advance filter is actually interpreted as the standard logical expression in the background where indentation represents the bracket and grouped conditions are treated as enclosed between the brackets. There is no limitation in the number or depth of filtering rules used.

The Text data filter will additionally allow meeting some very specific requirements as it is capable to parse the actual T-SQL of for audited event and then to collect or filter out the audited event depending on the used condition

apexaudit 06 SQL SERVER   Security Auditing With ApexSQL Audit

In the example above, the filtering condition will audit and log only the alter login and create login events which doesn’t fulfill the company rule that Enforce password policy and Enforce password expiration must be enabled for these events, so DBA can be timely notified and take necessary steps to fix that.

As already stated, it is very important and usually required to be informed timely about any SQL Server security auditing issue or event. In order to meet such requirement, ApexSQL Audit provides the capable alerting system that consists of:

  • Built-in system alerts
  • User defined data alerts for alerting on captured events
  • Custom script alerts for alerting on any value that can be retrieved via SQL script

ApexSQL Audit utilizes the true real time alerting since alerting engine will intercept and process the audited event, according to defined alerting condition even before the information is stored in the central repository database, allowing user instant notification when alert is raised.

Some highlights of the ApexSQL Audit alerting system:

  • Fully customizable alert name and notification options (user definable text of the alert name, subject and body text)
  • Alerting engine utilize the same advanced filter engine used for the auditing filter which allows very precise alerting conditions to be created
  • Allows alert email notification with ability to define individual recipient email address for each alerting condition created
  • Unlimited number of created alerts
  • Editing of built-in system alerts to adjust them according to individual needs
  • Editing, removing, disabling or deleting of user created alert
  • Alert history viewer with unlimited number of stored alerts
  • Detail information in alert body about the event

 apexaudit 07 SQL SERVER   Security Auditing With ApexSQL Audit

All collected and stored SQL Server security auditing events should be presented in a human readable form for any internal purpose or on request of an auditor. Besides being able to ensure reporting ability within the standard GUI, ApexSQL Audit features the Web based reporting ability. Web reports allows full reporting ability from any computer within the local network using the standard web browser. Both, the GUI, and Web-based reports are equally capable and equipped. Regardless of whether user decides to use the GUI or web reports, there are two types of reporting –common reports and custom reports.

Common reports consist of 11 predefined basic reports including the comprehensive filtering abilities, which ensures the quick and easy preview of collected events but also the precise reporting on required audited events

apexaudit 08 SQL SERVER   Security Auditing With ApexSQL Audit

The security related reports are Security configuration history, Logon activity history, Permission changes and Unauthorized access

 apexaudit 09 SQL SERVER   Security Auditing With ApexSQL Audit

For those who wish more from reporting, ApexSQL Audit has built-in custom reports feature that grants the full freedom in creating and organizing of reporting on collected during the SQL Server auditing. Again, the advanced filter form is used here as well (it is consistent through all parts of application) making any customization in ApexSQL Audit custom reports or any other part of application to be easy and consistent. Custom reports allow creating the precise filtering condition that can be saved and reused whenever it is needed

apexaudit 10 SQL SERVER   Security Auditing With ApexSQL Audit

There is no limitation in how many reports user can create and save, which allows creating the general overview, thematic or very precise reports that matches very specific requirements in just a few mouse clicks

apexaudit 11 SQL SERVER   Security Auditing With ApexSQL Audit

With ApexSQL Audit, SQL Server security auditing, but also any SQL Server auditing and compliance has never been more reliable, easier and simple. There’s quite a bit more to ApexSQL Audit than what’s outlined here, but this easily show how ApexSQL Audit is capable of meeting most of auditing concerns directly out of the box.

ApexSQL Audit shares other ApexSQL solutions easiness of installation, setup and administration. ApexSQL Audit is focused on ensuring requirements for easy and comprehensive auditing and compliance for SQL Server. The most impressive feature of ApexSQL Audit is the fact that it is a lightweight, it uses central repository database with the built-in tamper proofing mechanism and unprecedented ability to achieve high precision auditing, alerting, and reporting.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Puzzle with Year Function

Puzzle pieces SQL SERVER   Puzzle with Year FunctionA couple of weeks back, I ran a contest with MONTH () function and had close to 300 of you answer the puzzle. It was a great brain teaser and we had an awesome response from you. Most of you got the answer right and it is great to see you folks getting the basics right.

When I was on that step, I was wondering to run this second contest. Sometimes our basics can get tricked if we add a twist to the whole setup. Below are two sets of Queries and I would want you to guess what is the output and why?

Query 1:

What is the output for the below query? And why?

DECLARE @dt DATE = '0001-1-1'

Part of the answer is in the earlier puzzle I mentioned starting this blog. But what would the year value show? Take a guess and let me know.

Query 2:

I have made a small change in the default value for the second query. Now can you guess what the output from this query is?

DECLARE @dt DATE = '1-1-1'

As part of the hint I can tell you the MONTH function returns the same value. But there is something different for the YEAR function.

So here is the quiz – Why are the values different? What is the reason behind this?

Please leave correct answer in comment below the blog.

I will announce winner of this contest in 48 hours. Two people who give the right answer, I will share a learning resource worth USD 29 on June 11 via Email.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Know Your Backup Before Deleting Database

Earlier last week I wrote a blog around, SQL SERVER – FIX – Msg 3702, Level 16, State 3 – Cannot Drop Database “DB_Name” Because it is Currently in Use. The premise of that blog was driven by some of the demo’s I show at conferences. During one of the UG Meets, I met my good friend Balmukund doing something different and that inspired me to write that blog.

When I wrote that, many people did write back stating it can be dangerous etc. I sort of agree if you are on a production box. As my usecase was based on Demo environment for sessions, lesser did I think about it. Having said that, immediately I thought it would be good to back-it-up with a blog that will prevent us from getting into trouble.

So I wrote back to my friend who wrote an email to me about the dangers. Yes, we need to be careful while dropping databases but be prepared for contingencies. If you have taken a backup of your database, make sure to check if the same is available by querying the MSDB. I gave a typical script would look like this:

CASE msdb..backupset.TYPE
'D' THEN 'Database'
WHEN 'I' THEN 'Differential'
WHEN 'F' THEN 'Filegroup'
END AS backup_type,
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
-- Add the WHERE condition if you want it for a specific database
-- WHERE msdb.dbo.backupset.database_name = 'AdventureWorks2014'
ORDER BY msdb.dbo.backupset.backup_finish_date


The above script is a classic way to find all the backups done for a given database or the databases on a given instance.

As a best practice, I would like to figure out from these DMV’s if any backups were taken on every single database before working on them. I am sure as a seasoned DBA, you all are always aware of this important steps when working on production databases.

I know each organization has a restore strategy, so can you let me know about your restore strategies for critical databases? When do you take FULL, Differential and how often you take TLog backups in your production environments? Sharing this is a great way to tell the blog readers about typical usage patterns.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #023 – Error Handling with TRY…CATCH

Here is one of the most popular question I see people discussing in SQL Interviews.

Question – How do you handle errors with the help of TRY…CATCH?

Answer –

TRY/CATCH helps to write logic separate the action and error handling code. The code meant for the action is enclosed in the TRY block and the code for error handling is enclosed in the CATCH block. In case the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back and resume execution. In addition to this, the CATCH block captures and provides error information that shows you the ID, message text, state, severity and transaction state of an error.

Functions to be used in CATCH block are :

  • ERROR_NUMBER: returns the error number, and is the same value of @@ERROR.
  • ERROR_SEVERITY: returns the severity level of the error that invoked the CATCH block.
  • ERROR_STATE: returns the state number of the error.
  • ERROR_LINE: returns the line number where the error occurred.
  • ERROR_PROCEDURE: returns the name of the stored procedure or trigger for which the error occurred.
  • ERROR_MESSAGE: returns the full message text of the error. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

You can use these functions anywhere inside a CATCH block, and they will return information regarding the error that has occurred. These functions will return the value null outside of the CATCH block.

{ sql_statement |
statement_block }
{ sql_statement |
statement_block }

The TRY or CATCH block can contain a single T-SQL statement or a series of statements. The CATCH block must follow immediately after the TRY block. The TRY/CATCH block cannot span more than a single batch. In addition, TRY/CATCH block cannot span an IF/ELSE statement.

You can read more about this subject over here: Explanation of TRYCATCH and ERROR Handling.

Reference: Pinal Dave (http://blog.sqlauthority.com)