SQL try-catch语句不处理错误(SQL Server 2008) [英] SQL try-catch statement not handling error (SQL Server 2008)
问题描述
由于某种原因,这并不是处理我的错误,所以我试图在SQL查询(而不是存储过程)中捕获一个错误。我仍然得到:
消息213,级别16,状态1,行29
列名或提供的数字与表定义不匹配。 / p>
有任何帮助吗?
开始尝试
创建表#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 varcha r(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
从OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;数据库= C:\SQL_DATA_REPORTING\8-31-11存储Hierarchy.xlsx; HDR = YES选择*
',
'SELECT * FROM [Master List $]');
truncate table tbl_hierarchy
insert into tbl_hierarchy
select#
from #temp_hierarchy
其中temp_gl_number不为null
和temp_gl_number <> 'GLID'
选择@@ ROWCOUNT +'成功导入记录'
end try
begin catch
select'ERROR:' &安培; ERROR_NUMBER()+'。无法导入记录,现有数据没有丢失。'
end catch;
go
你有编译时错误这不能被抓住。
编译和语句级重新编译错误
有两种类型的错误将不会被TRY ... CATCH处理,如果
的错误发生在与TRY ... CATCH
构造相同的执行级别:
编译错误,例如妨碍
执行批处理的语法错误。
在语句级重新编译期间发生的错误,例如由于延迟的
名称解析而在编译后发生的对象
名称解析错误。
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
You have a compile time error which cannot be caught in a try-catch.
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.
这篇关于SQL try-catch语句不处理错误(SQL Server 2008)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!