Try Catch to Pinpoint Set-Based Error?
in [Programming SQL]
|
From: SnapDive on 7 Jun 2010 11:34 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.
From: Erland Sommarskog on 7 Jun 2010 18:05 SnapDive (SnapDive(a)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(a)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
From: SnapDive on 8 Jun 2010 09:48 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(a)sommarskog.se> wrote: >SnapDive (SnapDive(a)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.
From: Bob Barrows on 8 Jun 2010 15:50 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
From: Erland Sommarskog on 8 Jun 2010 17:43
SnapDive (SnapDive(a)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(a)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 |