Try-catch SQL query that does not handle error (SQL Server 2008)

Question

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

For some reason, this does not handle my error, and I still get:

Msg 213, Level 16, State 1, Line 29 The column name or number of set values ​​does not match the 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 

Answer 1

You have a compile-time error that cannot be caught in an attempt.

BooksOnline :

Recompilation errors at the compilation and approval level

There are two types of errors that will not be handled by TRY ... CATCH if the error occurs at the same run level as the TRY ... CATCH build:

• Compile errors, such as syntax errors, that prevent the execution package.

• Errors that occur during recompilation at the instruction level, such as the object name resolution errors that occur after compilation due to delayed name resolution.

Answer 2

You should not embed SELECT * - ever! This is an unsatisfactory practice, and it causes exactly the error you posted. Define the columns in your selection and in the INSERT section of your query.

Answer 3

The error is caused by the following operation

 select @@ROWCOUNT + ' Records sucessfully imported' 

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

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

EDIT: This is a bug, but it looks like this bug will be caught as a trick, so you should have another compile-time error causing the problem.

Answer 4

HLGEM - I use insert dbo.mytable select * from dbo.mySrcTbl all the time and on purpose, I do this to catch circuit changes and catch, register, send me an email.

I do not control all the tables in my world, and the king’s data often sleeps after hours.