Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Handling errors in T-SQL

Posted on 2014-09-16
10
Medium Priority
?
153 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 24

Expert Comment

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

Accepted Solution

by:
Vitor Montalvão earned 1400 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 48

Author Comment

by:Dale Fye
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 49

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 52

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
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 600 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 48

Author Comment

by:Dale Fye
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 48

Author Closing Comment

by:Dale Fye
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 52

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 48

Author Comment

by:Dale Fye
ID: 40325754
LOL!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

715 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