SQL try-catch statement not handling error (SQL Server 2008)

Question

I am trying to catch an error in a SQL query (not in a stored procedure) using try-catch.

For some reason this is not handling my error and I am still getting:

Msg 213, Level 16, State 1, Line 29 Column name or number of supplied values does not match table definition.

Any help please?

begin try
create table #temp_hierarchy
    (temp_gl_number varchar(50)
    ,temp_store_location varchar(255)
    ,temp_store_key varchar(50)
    ,temp_serving_dc varchar(50)
    ,temp_exploris_db varchar(50)
    ,temp_dc_account varchar(50)
    ,temp_store_type varchar(50)
    ,temp_dvp_ops varchar(50)
    ,temp_rdo varchar(50)
    ,temp_team varchar(50)
    ,temp_dvp_sales varchar(50)
    ,temp_rds varchar(50)
    ,temp_closed varchar(50)
    ,temp_open_date varchar(50)
    ,temp_close_date varchar(50)
    ,temp_store_manager varchar(250)
    ,temp_sales_teammate varchar(250)
    ,temp_machine_shop varchar(50)
    ,temp_address varchar(250)
    ,temp_city varchar(50)
    ,temp_state varchar(50)
    ,temp_zip varchar(50)
    ,temp_phone varchar(50)
    ,temp_fax varchar(50))

insert into #temp_hierarchy
select * 
from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;Database=C:\SQL_DATA_REPORTING\8-31-11 Store Hierarchy.xlsx;HDR=YES', 
    'SELECT * FROM [Master List$]');

truncate table tbl_hierarchy

insert into tbl_hierarchy
select *
from #temp_hierarchy
where temp_gl_number is not null
    and temp_gl_number <> 'GLID'

select @@ROWCOUNT + ' Records sucessfully imported'

end try

begin catch
select 'ERROR: ' & ERROR_NUMBER() + '. Unable to import records, existing data was not lost.' 
end catch;
go

My guess would be that tbl_hierarchy and #temp_hierarchy have different column definitions. — etliens
– etliens, Commented Sep 2, 2011 at 17:19
You can use select * INTO T from OPENROWSET... to see the correct table definition to use for the temp table. — Martin Smith
– Martin Smith, Commented Sep 2, 2011 at 17:20

etliens · Accepted Answer · 2011-09-02 17:16:21Z

15

You have a compile time error which cannot be caught in a try-catch.

BooksOnline:

Compile and Statement-level Recompile Errors

There are two types of errors that will not be handled by TRY…CATCH if the error occurs in the same execution level as the TRY…CATCH construct:

Compile errors, such as syntax errors that prevent a batch from executing.

Errors that occur during statement-level recompilation, such as object name resolution errors that happen after compilation due to deferred name resolution.

answered Sep 2, 2011 at 17:16

etliens

1,3429 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ChandlerPelhams Over a year ago

That makes sense. How would I be able to trap this error then?

etliens Over a year ago

@ChandlerPelhams Further down in that BOL article it mentions using either dynamic sql or wrapping the query in a stored procedure. The try-catch will catch it.

HLGEM · Accepted Answer · 2011-09-02 18:12:42Z

1

You should not be inserting using SELECT * - ever! This is poor practice and it is causing exactly the error you posted. Define the columns in your select and in the INSERT part of your query.

answered Sep 2, 2011 at 18:12

HLGEM

97k15 gold badges120 silver badges191 bronze badges

3 Comments

ChandlerPelhams Over a year ago

My question was about why the error was not getting trapped, not the fact that the error occurred.

ColinMac Over a year ago

I have the same problem as @ChandlerPelhams; I am expecting my table definition to change, and I want my catch block to execute when that has happened. It worked when I was first making the SP. But, once the TRY Block version of my table definition had been changed - the SP won't even run b/c of a compile error.

Nick Fotopoulos Over a year ago

Never say never...errrr...or ever. I'm currently working on a way of detecting changes to a source table that we import from but don't control. I'd like to know when columns are added, but we can't query INFORMATION_SCHEMA on the remote server. In an attempt to detect these changes in a round about way i'm duplicating my destination table structure, then using INSERT INTO DestTable SELECT * FROM SourceTable and catching the error so I can send an email notification to the right team. There is always a use case for even the most hated syntax...even *hard gulp* the lovely GOTO keyword. xD

JeffPGMT · Accepted Answer · 2018-04-05 19:10:49Z

1

HLGEM - I use insert dbo.mytable select*from dbo.mySrcTbl all the time and on purpose, I do so as to catch schema changes, and catch, log, send me an email.

I don't control all the tables in my world and the data czar is often asleep during non business hours.

answered Apr 5, 2018 at 19:10

JeffPGMT

3492 silver badges3 bronze badges

1 Comment

Nick Fotopoulos Over a year ago

100% this right here. I found my way to this post for this very reason. Some people just want to watch the world burn, and many of them apparently become "data czars". xD

Leons · Accepted Answer · 2011-09-02 17:32:19Z

0

The error is caused by the following statement

select @@ROWCOUNT + ' Records sucessfully imported'

@@ROWCOUNT is an integer, so convert to a string first.

select convert(varchar(10),@@ROWCOUNT) + ' Records sucessfully imported'

EDIT: That is an error, but it does appear that this error will be caught be the catch, so you must have another compile-time error causing the problem.

answered Sep 2, 2011 at 17:32

Leons

2,6741 gold badge23 silver badges26 bronze badges

Collectives™ on Stack Overflow

SQL try-catch statement not handling error (SQL Server 2008)

4 Answers 4

2 Comments

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related