Solved

Handling errors in T-SQL

Posted on 2014-09-16
10
142 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 45

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 45

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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 45

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

747 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

12 Experts available now in Live!

Get 1:1 Help Now