Solved

Handling errors in T-SQL

Posted on 2014-09-16
10
149 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)
[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 49

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 49

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 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 49

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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

749 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