Solved

Handling errors in T-SQL

Posted on 2014-09-16
10
144 Views
Last Modified: 2014-09-16
I'm an Access MVP moving into SQL Server in a big way.

I'm writing a series of T-SQL procedures to massage some data from several different data sources and insert it into a variety of tables in SQL Server.

I am keeping track of these processes in tbl_Processes, where I update the status of each of the processes as they complete, with a comment "updated x records from abc", "inserted y records from 123" and a date time stamp, so that I can display this information in a dashboard.

One of the processes is a MERGE query which simultaneously updates and appends records.  What I found out today is that one of the data sources occasionally has duplicate records for the combination of fields that are used in the MERGE join.  When this happened, I got an error and the procedure did not complete properly.

How do I trap for errors in T-SQL so that I can
1.  skip steps that rely on an earlier event in the process.

For example, if the Merge is successful, I truncate a staging table. If the merge fails, I don't want to delete the records from the staging table, I want to be able to review them from Access, make the appropriate changes and then re-run that particular SP.

2.  display an error message in my tbl_Processes
0
Comment
Question by:Dale Fye (Access MVP)
10 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40325138
Can you post your MERGE query please.
0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 350 total points
ID: 40325162
What is massage data?
(...) to massage some data (...)

It really needs to be with T-SQL? Integration Services can do the job for you and showing the progress and errors if any.

For handling errors in T-SQL you have the TRY ... CATCH. Full explanation with examples in MSDN.
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40325175
Phillip,

The Merge procedure really is not important other than that is where I encountered the error.  I understand why the error occurred (multiple records in the source table matched the joining fields in the target table).

Vitor,

Still a newbie with SSIS, but may eventually may move some of this there.  Much of the massaging of the data involves pushing the data from Access into SQL Server staging tables, then identifying discrepancies in the data before performing the SQL SPs.  Thanks for the reference to Try ... Catch, believe that is what I'm looking for.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40325229
Vitor

"massage" is used in the sense of  "to adjust"; to adjust the data to suit some purpose e.g. to remove malformed data or ensure dates are in fact dates etc.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40325254
And I thought was a typo :)
Sorry but English is not my mother tongue language so sometimes have these little surprises. Living and learning. Thanks for the explanation.

Cheers
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 150 total points
ID: 40325296
>1.  skip steps that rely on an earlier event in the process.
This is going to be tricky, as you're going to have to map out all of these threads, or put everything in IF blocks to handle dependancies, like this..

IF @some_dependant_process = Y
   IF EXISTS (SELECT some_column FROM some_table WHERE process_name='some_name' AND some_flag='Y')
      --- Do good path stuff here
  ELSE
      -- Do not so good stuff here
ELSE
   --- Do really not good stuff here

Note that in the above block, if the -- contains more than one line, you'll have to surround it with BEGIN up top and END at the bottom.   (Yes this is different from Access)

>2.  display an error message in my tbl_Processes
Paint us a picture of what you're trying to pull off here.

As far as handling errors, the above link to TRY..CATCH is the way to go.
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40325557
Jim,

I like the idea of using a variable and an If statement to execute the subsequent steps in the process.  So, I think in this case, I would do something like:
DECLARE @MergeSuccess bit
DECLARE @RecordsAffected int 
DECLARE @Message as nvarchar(150)

Set @MergeSuccess = 1

BEGIN TRY

    SELECT 1/0;
    SET @RecordsAffected = @@ROWCOUNT
    
END TRY
BEGIN CATCH

    Set @MergeSuccess = 0
    SET @RecordsAffected = 0
    SET @Message = ERROR_MESSAGE()
    
 END CATCH
 
IF @MergeSuccess = 1
    TRUNCATE Table zz_Staging_tanks;

INSERT INTO tbl_Processes (ProcessName, RecordCount, Comment, DT_When)
 SELECT 'Merge X', @RecordsAffected, @Message, dbo.TodaysDate()

Open in new window


What I've been doing with tbl_Processes is keeping track of when each process was run, # of records affected.  In Access, I have a query and form that identifies all of the processes, the most recent run, then # of records affected.  I use conditional formatting to highlight when the RecordsAffected = 0; this gives me an indication that the data coming from the source did not get pulled properly, or an error of some sort occurred.  If the most recent [DT_When] is not today, that gets flagged as well.  And if RecordsAffected is outside 1.5 standard deviations from the average for that process, I color code that yellow, as something that needs to be looked into.
0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 40325560
Thanks, guys.

@Vitor, I assumed the English was not your primary language when the link you posted sent me to the German language version of MSDN.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40325630
My german's worst than my English. It's just because I'm in Switzerland and my browser assuming that I know german :)
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40325754
LOL!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now