Try Catch to Pinpoint Set-Based Error?


SQL 2008.

I have a fairly simple Merge statement with this:

When Not Matched Then Insert Values
(
  ... long column list ...
cast( crazyCol1 as decimal(16,8) ) / 1000000 )
, cast( crazyCol2 as decimal(16,8) ) / 1000000 )
cast( crazyCol2 as decimal(16,8) ) / 1000000 )
cast( crazyCol2 as decimal(16,8) ) / 1000000 )
)

The insert source is about 50,000 rows. One of the rows is failing the
cast (original source is varchar(36).

I would like to implement a try/catch block around this somehow so I
can catch the offending row.

How can I do that?

Thanks.



0
SnapDive
6/7/2010 3:34:12 PM
sqlserver.programming 1873 articles. 0 followers. Follow

7 Replies
807 Views

Similar Articles

[PageSpeed] 40

SnapDive (SnapDive@community.nospam) writes:
> I have a fairly simple Merge statement with this:
> 
> When Not Matched Then Insert Values
> (
>   ... long column list ...
> cast( crazyCol1 as decimal(16,8) ) / 1000000 )
> , cast( crazyCol2 as decimal(16,8) ) / 1000000 )
> cast( crazyCol2 as decimal(16,8) ) / 1000000 )
> cast( crazyCol2 as decimal(16,8) ) / 1000000 )
> )
> 
> The insert source is about 50,000 rows. One of the rows is failing the
> cast (original source is varchar(36).
> 
> I would like to implement a try/catch block around this somehow so I
> can catch the offending row.
 
That's the draw-back with the set-based approach: it's really all
of nothing. The only way to avoid to make it happen is to use a 
CASE expression:

   CASE WHEN crazyCol2 LIKE 'somegoodpatterrn'
        THEN cast....
   END

If you want to find the failing row, you would need to run a SELECT
over the source.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
6/7/2010 10:05:33 PM
Since SQL Server failed the action, it has to know what and where
things failed. I was thinking that something akin to an OUTPUT clause
would tell me what blew up. There has to be something... If I could
collect the ID column of the row that failed, then I could issue a
SQL-Update and fix it and put the whole thing into a while loop until
there were no more failure. There is a massive amount of data, too
much to iterate over individually, but just a few glitches...
Somewhere.

Thanks Erland, all thoughts appreciatted!


On Tue, 08 Jun 2010 00:05:33 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>SnapDive (SnapDive@community.nospam) writes:
>> I have a fairly simple Merge statement with this:
>> 
>> When Not Matched Then Insert Values
>> (
>>   ... long column list ...
>> cast( crazyCol1 as decimal(16,8) ) / 1000000 )
>> , cast( crazyCol2 as decimal(16,8) ) / 1000000 )
>> cast( crazyCol2 as decimal(16,8) ) / 1000000 )
>> cast( crazyCol2 as decimal(16,8) ) / 1000000 )
>> )
>> 
>> The insert source is about 50,000 rows. One of the rows is failing the
>> cast (original source is varchar(36).
>> 
>> I would like to implement a try/catch block around this somehow so I
>> can catch the offending row.
> 
>That's the draw-back with the set-based approach: it's really all
>of nothing. The only way to avoid to make it happen is to use a 
>CASE expression:
>
>   CASE WHEN crazyCol2 LIKE 'somegoodpatterrn'
>        THEN cast....
>   END
>
>If you want to find the failing row, you would need to run a SELECT
>over the source.

0
SnapDive
6/8/2010 1:48:19 PM
SnapDive wrote:
> Since SQL Server failed the action, it has to know what and where
> things failed. I was thinking that something akin to an OUTPUT clause
> would tell me what blew up. There has to be something...

Huh? Why does there "have" to be something? It's a set-based operation.

> If I could
> collect the ID column of the row that failed, then I could issue a
> SQL-Update and fix it and put the whole thing into a while loop until
> there were no more failure. There is a massive amount of data, too
> much to iterate over individually, but just a few glitches...
> Somewhere.


You might try something like performing the updates on batches of the data 
until you find the batch that causes the error. Then break that batch into 
smaller batches, etc. until you finally get a small enough batch to loop 
through to find the defective row.

-- 
Bob Barrows 


0
Bob
6/8/2010 7:50:16 PM
SnapDive (SnapDive@community.nospam) writes:
> Since SQL Server failed the action, it has to know what and where
> things failed. 

Yeah, but that does not mean that it will tell us!

> I was thinking that something akin to an OUTPUT clause
> would tell me what blew up. 

Alas, SQL Server is a bit pedantic, so all rows inserted sofar 
in a failing INSERT statement will be rolled back. The one exception
is with INSERT EXEC. But that is only if the called procedure produces
several result sets.



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
6/8/2010 9:43:59 PM
Well, can you recommend a tight/foolproof/best-practice way to
possibly do the merge statement in n-row sets (each with their own
transaction?) so I can possibly isolate the bad row? At the expense of
a probable speed penalty, if I could at least know the error occured
within 20 rows, a human could visually likely see where the problem
might be.

Thanks.



On Tue, 08 Jun 2010 23:43:59 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>SnapDive (SnapDive@community.nospam) writes:
>> Since SQL Server failed the action, it has to know what and where
>> things failed. 
>
>Yeah, but that does not mean that it will tell us!
>
>> I was thinking that something akin to an OUTPUT clause
>> would tell me what blew up. 
>
>Alas, SQL Server is a bit pedantic, so all rows inserted sofar 
>in a failing INSERT statement will be rolled back. The one exception
>is with INSERT EXEC. But that is only if the called procedure produces
>several result sets.

0
SnapDive
6/9/2010 12:30:19 PM
SnapDive (SnapDive@community.nospam) writes:
> Well, can you recommend a tight/foolproof/best-practice way to
> possibly do the merge statement in n-row sets (each with their own
> transaction?) so I can possibly isolate the bad row? At the expense of
> a probable speed penalty, if I could at least know the error occured
> within 20 rows, a human could visually likely see where the problem
> might be.
 
I know that when I faced this situation once, my source was a file
which I tried to load with BCP. I think I used a text editor to divide 
the file in half, then I took the half that did not load and so on
until I found the date of 920230.

But with a mere 50000 rows, and today's hardware, I would try to write
a simple cursor which inserts the rows one by one in TRY CATCH, and 
in the CATCH block, I would insert into an error table. One reason for
this choice is that my experience tells me that there may be more than
one bad row.

Note that if you want to put this in a stored procedure, you have 
TRY CATCH around your MERGE, and the in the CATCH block call the
procedure that runs the cursor as a fallback.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
6/9/2010 9:25:42 PM
Thanks for the input (and Bob Barrows also)!

On Wed, 09 Jun 2010 23:25:42 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>SnapDive (SnapDive@community.nospam) writes:
>> Well, can you recommend a tight/foolproof/best-practice way to
>> possibly do the merge statement in n-row sets (each with their own
>> transaction?) so I can possibly isolate the bad row? At the expense of
>> a probable speed penalty, if I could at least know the error occured
>> within 20 rows, a human could visually likely see where the problem
>> might be.
> 
>I know that when I faced this situation once, my source was a file
>which I tried to load with BCP. I think I used a text editor to divide 
>the file in half, then I took the half that did not load and so on
>until I found the date of 920230.
>
>But with a mere 50000 rows, and today's hardware, I would try to write
>a simple cursor which inserts the rows one by one in TRY CATCH, and 
>in the CATCH block, I would insert into an error table. One reason for
>this choice is that my experience tells me that there may be more than
>one bad row.
>
>Note that if you want to put this in a stored procedure, you have 
>TRY CATCH around your MERGE, and the in the CATCH block call the
>procedure that runs the cursor as a fallback.

0
SnapDive
6/10/2010 12:05:47 AM
Reply:

Similar Artilces:

Business Portal Error-SQL server does not exist or access denied
Hi, We are running business portal 4.0 for one of our customer. It was running correctly, however, they have changed the SQL server port (previously it was set as default 1433). After that the business portal becomes very slow and while creating a new request (purchase requisition) if we open the item pop up; it is showing exception "SQL Server does not exist or access denied...." Can any body tell me how can I provide the new port number to business portal connection to the database. Thanks and Regards, Waliullah, Thanks for using the newsgroups. I have a...

.pdf save as settings
Hi, In Office 2007 Pro is there a way to change the settings such as preventing content copying etc. when saving a file to pdf? I have been trialling Open Office, alongside Office 2007 Pro (full version not a trial), which allows access to these settings but I can't see a way to do it in MS Office. -- Chas MS Office doesn't do that. Chass wrote: > Hi, In Office 2007 Pro is there a way to change the settings such as > preventing content copying etc. when saving a file to pdf? I have been > trialling Open Office, alongside Office 2007 Pro (full version not...

Web query timeout setting.
Dear Group, I fill an Excel table using data that I take from an Internet site. Unfortunately, this site is very slow and so I often get a "query did not provide any data" error message. How can I increment the default web query timeout limit? Thanks in advance, Enrico. ...

crm 3.0 error 03-01-06
Hello, I'me getting this error while installing crm3.0 for SBS: "error writing to file microsoft.mshtml.dll verify that you have access to that directory" That file is in the C:\Program Files\Microsoft.NET\Primary Interop Assemblies directory. I (and 'everyone') has full access to that dir. What can I do about this?? kind regards, Thomas ...

Setting up a new e-mail account
I'm trying to set up my yahoo account so that I receive my e-mail messages in my Outlook Inbox. I went to Accounts, and servers, and put in mail.yahoo.com for incoming mail (POP3) and smtp.yahoo.com for outgoing mail (smtp). When I click on the send/receive button I get an error message. Dave <anonymous@discussions.microsoft.com> wrote: > I'm trying to set up my yahoo account so that I receive > my e-mail messages in my Outlook Inbox. I went to > Accounts, and servers, and put in mail.yahoo.com for > incoming mail (POP3) and smtp.yahoo.com for outgoing mail &...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Error in Outlook Today
Whenever I go to Outlook Today, I get a runtime error, line: 297 Error: Class Not Registered. Then I get the error two more times when I click 'Customize Outlook Today...' and the list 'Show Outlook Today In This Style' is empty, and the box under it has a broken image icon. What could be the cause of this? Sorry...forgot to say...I'm using Outlook 2003 Student and Teacher Edition on Windows XP. >-----Original Message----- >Whenever I go to Outlook Today, I get a runtime error, >line: 297 Error: Class Not Registered. Then I get the >error two more time...

Re: 'Uknown Error 0x800CCC97'
I just heard back from the folks with whom I filed this bug. They say the bug is fixed in cppop 5.4 - request that your ISP upgrade to that. -- Jeff Stephenson Outlook Development This posting is provided "AS IS" with no warranties, and confers no rights "Jeff Stephenson [MSFT]" <stephenson@online.microsoft.com> wrote in message news:... > See the attached reply to another similar question. Your ISP's POP3 server > has a bug, and they should get a fixed version of the server. > > -- > Jeff Stephenson > Outlook Development > This posting...

I would like to know how to set up a 'fill in the blanks' form?
I would like to set up a templet of sorts to fill in the blanks for certificates. ...

stop error defeating me
Hi, XP Pro PC. When I start the computer I can start in safe mode but when I try to start in normal mode it loads to the log on screen. I type the username and password in then it starts to load but stops after a few moments with a blue screen. The error is Stop: c000021a (fatal system error) The windows subsystem system process terminated unexpectedly with a status of 0xc0000005 (0x7c9106c3 0x0055f36c). Begininning dump of physical memory. I have uninstalled AVG, also taken out the graphics card and uninstalled all the drivers for it. I have also changed the RAM. I have also d...

Error in database....
A user posted a batch in payables management. After posting, there was an error encountered. It displays that the table updating was interrupted, use batch recovery to continue the posting. But when I used the batch recovery, it was not successful to continue the update process. When I click the "More Details" button it displays, A save operation on table 'PM_Transaction_WORK' caused a sharing error. How can I resolve this issue? Thanks, John John, it is a db sharing violation. Have all users logout DELETE tempdb..DEX_LOCK DELETE tempd..DEX_SESSION DELETE dynami...

I want to add a mail account without setting a SMTP server. Is it possible?
I want to add a second mail account that is outside my company. Since the company firewall does not allow us to connect to SMTP-servers I want to add this account without setting the SMTP propertiy. I cannot specify the company server as it is an Exchange server and the outside one an IMAP one. Currently I have specified the outside SMTP server since outook does not allow me to add an account wihtout setting this. I keep getting annoying error messages as my computer can't connect to the outside SMTP. Any one know a way around this? cheers, mortb The company server has SMTP enabled, u...

error 553
The following error occurs when sending email from my business domain. It does not occur when sending through my roadrunner account. The following recipient(s) could not be reached: on 10/22/2003 2:05 PM 553 sorry, that domain isn't in my list of allowed rcpthosts (#5.7.1) What does this mean and how can it be fixed? ...

80070005 error #2
I am getting this error when trying to view public folder property from system manager. My issue is same as what you can find out from http://forums.msexchange.org/ultimatebb.cgi? ubb=get_topic;f=19;t=000114 Anyone has a clue? ...

Macro to change default setting on startup
I am in need of a macro that can change a default setting in excel and for it to run on startup The task is: Tools Options General Web Options Files uncheck Update links on save Below is the recording of the macro: With ActiveWorkbook.WebOptions ..RelyOnCSS = True ..OrganizeInFolder = True ..UseLongFileNames = True ..DownloadComponents = False ..RelyOnVML = False ..AllowPNG = False ..ScreenSize = msoScreenSize800x600 ..PixelsPerInch = 96 ..Encoding = msoEncodingWestern End With With Application.DefaultWebOptions ..SaveHiddenData = True ..LoadPictures = True ....

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

Setting in Outlook 2007
Hi I have Outlook 2007 with all updates, how do I change the settings so attachments, web addresses or inserted pictures are not removed or disallowed. Thanks for any help regards Daniel Attacments removed? - Uninstall your anti virus application Web addresses - In internet options ensure outlook is set as default mail app "Daniel" <noone@nowhere.com> wrote in message news:OcOS1y97JHA.5356@TK2MSFTNGP05.phx.gbl... > Hi > > I have Outlook 2007 with all updates, how do I change the settings so > attachments, web addresses or inserted pictures are not removed...

CRM Error
Hello When a user replies to an CRM email, clicks the "reply" button or the "reply all" button, clicks in the body of the email message and clicks "insert template", this error appears. This does not happen every time, and happens to various users. Does anyone know why we would get this error? ...

Setting up Word to automatically open in Standard mode, zoom=page margins
I've tried to find ways to handle this through the normal.dot file and can't find it... Word wants to open in layout mode and 200% zoom, forcing me to manually select mode and zoom. Any way to get Word to do what I want instead of what it wants? Thanks for your time. ===== Edward Lipsett Fukuoka, Japan Hi Edward: Word will open each document in the view and zoom it as last saved in. The onyl way to have a particular view happen every time is to create an AutoOpen() macro that specifies the view you want. Turn on your Macro Recorder and record yourself setting the view and zo...

smtp authentification errors
Hello, I just installed Exchange 2003. It all works fine. I=B4m now=20 retrieving the emails via pop-con and pop3. However I=20 cannot send any authenticicated emails, because the=20 exchange server sends a wrong authentification account to=20 the mail provider, despite the fact, that the right one to=20 be used is enterd. The mail provider tells me that he recives a smtp call,=20 but it=B4s aborted by his server. We do not have a static IP but a dynamic IP, that changes=20 every 24 hours. If a messages is sent to an existing hotmail account the=20 following error message is displayed in the ...

Outlook2K3 and POP3 settings
Greetings list, I have a quick question and am also needing something somewhere that will explain in further detail how POP3 works with Outlook 2003. Scenario: I have a remote user that had been using Outlook 2003 with Exchange services and is now unable to use them from his location. In order to allow for him to connect to our mail server, we switched him over to a POP3 connector. All of his settings are correct and he is able to receive email without issue. However, it appears that the setting in his Send/Receive groups settings are not responsive to the time limit he has listed. I ha...

#error in the calculated field
Hello, I locked the data entered for some users, because their role is just to input the date of invoice for approval by Prj.Manager. My qeustion is, is it the reason we see the "# error" in the VAT checking field??. I tried to ck formula in the qrid query, nothing wrong Thanks for your explanation -- H. Frank Situmorang On Tue, 15 Jan 2008 23:33:01 -0800, Frank Situmorang <hfsitumo2001@yahoo.com> wrote: >Hello, > >I locked the data entered for some users, because their role is just to >input the date of invoice for approval by Prj.Manager. > >My q...

Error when changing average perpetual
I Have the following error in Microsoft Dynamics GP on the screen of average perpetual "Message #10577 Missing" somebody knows about this??? Thankļæ½s. Gabriela Martinez. ...

Outlook 2007 "Move to folder" default setting
By default, in Outlook 2007, when you right-click on a message and select "Move to Folder", the initial folder selected is the last folder that a message has been moved into. Is there a way to have this option always default to Inbox? Thanks much for the help! No, but you can also directly move the message to your Inbox folder via drag & drop instead of using the Move To dialog for this. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.ms...

Outlook MAPI (WMS idle error message while shutting down)
I am loading the mapi32/mapisp32 dll in my program. When I shut down while my program is still running, I am getting "WMS Idle" message. 1. While shutting down, why i am not getting 'fnevCriticalError' notification in my IMAPIAdviseSink class?? and 2. What exactly is causing the WMS Idle message ? Any help will be appreciated... ...