SQL try-catch statement not handling error (SQL Se

问题:

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

回答1:

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:

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

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

回答2:

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.

回答3:

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.

回答4:

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.