SQL Server 'Resume Next' 等效项

SQL Server 'Resume Next' Equivalent

提问by VikingProgrammer

I'm working on a project in VB.net which takes large text files containing T-SQL and executes them against a local SQL database, but I've hit a problem in regards to error handling.

我正在 VB.net 中处理一个项目，该项目采用包含 T-SQL 的大型文本文件并针对本地 SQL 数据库执行它们，但我遇到了错误处理方面的问题。

I'm using the following technologies :

我正在使用以下技术：

• VB.net
• Framework 3.5
• SQL Express 2005

• VB.net
• 框架 3.5
• SQL Express 2005

The SQL I'm trying to execute is mostly straight-forwards but my app is completely unaware of the schema or the data contained within. For example :

我试图执行的 SQL 大多是直截了当的，但我的应用程序完全不知道架构或其中包含的数据。例如 ：

UPDATE mytable SET mycol2='data' WHERE mycol1=1
INSERT INTO mytable (mycol1, mycol2) VALUES (1,'data')
UPDATE mytable SET mycol2='data' WHERE mycol1=2
INSERT INTO mytable (mycol1, mycol2) VALUES (1,'data')
UPDATE mytable SET mycol2='data' WHERE mycol1=3

The above is a sample of the sort of thing I'm executing, but these files will contain around 10,000 to 20,000 statements each.

以上是我正在执行的那种事情的示例，但这些文件每个将包含大约 10,000 到 20,000 条语句。

My problem is that when using sqlCommand.ExecuteNonQuery(), I get an exception raised because the second INSERT statement will hit the Primary Key constraint on the table.

我的问题是，当使用 sqlCommand.ExecuteNonQuery() 时，我得到了一个异常，因为第二个 INSERT 语句将命中表上的主键约束。

I need to know that this error happened and log it, but also process any subsequent statements. I've tried wrapping these statements in TRY/CATCH blocks but I can't work out a way to handle the error then continue to process the other statements.

我需要知道这个错误发生并记录它，但还要处理任何后续语句。我已经尝试将这些语句包装在 TRY/CATCH 块中，但我无法找到一种方法来处理错误然后继续处理其他语句。

The Query Analyser seems to behave in this way, but not when using sqlCommand.ExecuteNonQuery().

查询分析器似乎以这种方式运行，但在使用 sqlCommand.ExecuteNonQuery() 时则不然。

So is there a T-SQL equivalent of 'Resume Next' or some other way I can do this without introducing massive amounts of string handling on my part?

那么是否有一个 T-SQL 等效于“继续下一步”或其他一些我可以在不引入大量字符串处理的情况下做到这一点的方法？

Any help greatly appreciated.

非常感谢任何帮助。

采纳答案by Remus Rusanu

Actually your batch executed until the end since key violations are not intrerupting batch execution. If you run the same SQL file from Management Studio you'll see that the result is that all the valid statements were executed and the messages panel contains an error for eachkey violation. The SqlClient of ADO.NEt behaves much the same way, but at the end of the batch (when SqlCommand.ExecuteNonQuery returns) it parses the messages returned and throws an exception. The exception is one single SqlException but it's Errors collection contains a SqlError for eachkey violation that occured.

实际上，您的批处理一直执行到最后，因为密钥违规不会中断批处理执行。如果您从 Management Studio 运行相同的 SQL 文件，您将看到结果是所有有效语句都被执行，并且消息面板包含每个键违规的错误。ADO.NEt 的 SqlClient 的行为方式大致相同，但在批处理结束时（当 SqlCommand.ExecuteNonQuery 返回时）它解析返回的消息并抛出异常。异常是一个单独的 SqlException，但它的 Errors 集合包含发生的每个键违规的 SqlError 。

Unfortunately there is no silver bullet. Ideally the SQL files should not cause errors. You can choose to iterate through the SqlErrors of the exception and decide, on individual basis, if the error was serious or you can ignore it, knowing that the SQL files have data quality problems. Some errors may be serious and cannot be ignored. See Database Engine Error Severities.

不幸的是，没有银弹。理想情况下，SQL 文件不应导致错误。您可以选择遍历异常的 SqlErrors 并根据个人情况决定错误是否严重，或者您可以忽略它，因为知道 SQL 文件存在数据质量问题。有些错误可能很严重，不容忽视。请参阅数据库引擎错误严重性。

Another alternative is to explictily tell the SqlClient not to throw. If you set the FireInfoMessageEventOnUserErrorsproperty of the connection to true it will raise an SqlConnection.InfoMessageevent instead of twroing an exception.

另一种选择是明确告诉 SqlClient 不要抛出。如果将连接的FireInfoMessageEventOnUserErrors属性设置为 true，它将引发SqlConnection.InfoMessage事件而不是引发异常。

回答by MusiGenesis

SQL Server does have a Try/Catch syntax. See:

SQL Server 确实具有 Try/Catch 语法。看：

http://msdn.microsoft.com/en-us/library/ms175976.aspx

http://msdn.microsoft.com/en-us/library/ms175976.aspx

To use this with your file, you would either have to rewrite the files themselves to wrap each line with the try/catch syntax, or else your code would have to programatically modify the file contents to wrap each line.

要在您的文件中使用它，您必须重写文件本身以使用 try/catch 语法包装每一行，否则您的代码必须以编程方式修改文件内容以包装每一行。

There is no T-SQL equivalent of "On Error Resume Next", and thank Cthulhu for that.

没有 T-SQL 等同于“On Error Resume Next”，并为此感谢 Cthulhu。

回答by mjv

At the risk of slowing down the process (by making thousands of trips to SQL server rwther than one), you could handle this issue by splitting the file into multiple individual queries each for either INSERT or UPDATE. Then you can catch each individual error as it take place and log it or deal with it as your business logic would require.

冒着减慢进程的风险（通过多次访问 SQL 服务器而不是一次），您可以通过将文件拆分为多个单独的查询来处理此问题，每个查询用于 INSERT 或 UPDATE。然后，您可以在发生的每个错误时捕获并记录它或按照您的业务逻辑需要处理它。

回答by R.Alonso

begin try
 --your critical commands
end try
begin catch
  -- is necessary write somethink like this
  select ''
end cath

回答by tsilb

You need to check whether the PK value already exists. Additionally, one large transaction is always faster than many little transactions; and the rows and pages don't need to be locked for as long overall that way.

您需要检查PK值是否已经存在。此外，一笔大交易总是比许多小交易快；并且行和页面不需要以这种方式锁定那么长时间。

-- load a temp/import table
create table @importables (mycol1 int, mycol2 varchar(50))
insert @importables (mycol1, mycol2) values (1, 'data for 1')
insert @importables (mycol1, mycol2) values (2, 'data for 2')
insert @importables (mycol1, mycol2) values (3, 'data for 3')

-- update the base table rows that are already there
update mt set MyCol2 = i.MyCol2
from @importables i (nolock)
    inner join MyTable mt on mt.MyCol1 = i.MyCol1
where mt.MyCol2 <> i.MyCol2 -- no need to fire triggers and logs if nothing changed

-- insert new rows AFTER the update, so we don't update the rows we just inserted.
insert MyTable (mycol1, mycol2)
select mycol1, mycol2
from @importables i (nolock)
    left join MyTable mt (nolock) on mt.MyCol1 = i.MyCol1
where mt.MyCol1 is null;

You could improve this further by opening a SqlConnection, creating a #temp table, using SqlBulkCopy to do a bulk insertto that temp table, and doing the delta from there (as opposed to my @importables in this example). As long as you use the same SqlConnection, a #temp table will remain accessible to subsequent queries on that connection, until you drop it or disconnect.

您可以通过打开 SqlConnection、创建 #temp 表、使用 SqlBulkCopy对该临时表进行批量插入并从那里进行增量（与本示例中的 @importables 相对）来进一步改进这一点。只要您使用相同的 SqlConnection，#temp 表将一直可供该连接上的后续查询访问，直到您删除它或断开连接。

回答by cmsjr

I'm not aware of a way to support resume next, but one approach would be to use a local table variable to prevent the errors in the first place e.g.

我不知道接下来支持恢复的方法，但一种方法是首先使用本地表变量来防止错误，例如

Declare @Table table(id int, value varchar(100))
UPDATE mytable SET mycol2='data' WHERE mycol1=1
--Insert values for later usage
INSERT INTO @Table (id, value) VALUES (1,'data')
--Insert only if data does not already exist. 
INSERT INTO mytable (mycol1, mycol2) 
Select id, Value from @Table t left join 
mytable t2 on t.id = t2.MyCol1
where t2.MyCol is null and t.id = 1

EDIT

编辑

Ok, I don't know that I suggest this per se, but you could achieve a sort of resume next by wrapping the try catch in a while loop if you set an exit condition at the end of all the steps and keep track of what step you performed last.

好的，我不知道我本身是否建议这样做，但是如果您在所有步骤结束时设置退出条件并跟踪什么，那么您可以通过将 try catch 包装在 while 循环中来实现某种恢复您上次执行的步骤。

Declare @Table table(id int, value varchar(100))

Declare @Step int
set @Step = 0
While (1=1)
Begin

    Begin Try

        if @Step < 1 
        Begin                 
               Insert into @Table  (id, value) values  ('s', 1)
                   Set @Step = @Step + 1
        End
        if @Step < 2
        Begin
            Insert into @Table values ( 1, 's')
                    Set @Step = @Step + 1
        End
        Break
    End Try 
    Begin Catch
      Set @Step = @Step + 1
    End Catch 

End 
Select * from @Table

回答by Justin Niessner

Unfortunately, I don't think there's a way to force the SqlCommand to keep processing once an error has been returned.

不幸的是，我认为没有办法在返回错误后强制 SqlCommand 继续处理。

If you're unsure whether any of the commands will cause an error or not (and at some performance cost), you should split the commands in the text file into individual SqlCommands...which would enable you to use a try/catch block and find the offending statements.

如果您不确定任何命令是否会导致错误（并且会造成一些性能损失），您应该将文本文件中的命令拆分为单独的 SqlCommands...这将使您能够使用 try/catch 块并找出违规言论。

...this, of course, depends on the T-SQL commands in the text file to each be on a separate line (or otherwise delineated).

...当然，这取决于文本文件中的 T-SQL 命令，每个命令都在单独的行上（或以其他方式描述）。

回答by reinierpost

One technique I use is to use a try/catch and within the catch raise an event with the exception information. The caller can then hook up an event handler to do whatever she pleases with the information (log it, collect it for reporting in the UI, whatever).

我使用的一种技术是使用 try/catch 并在 catch 内引发一个带有异常信息的事件。然后调用者可以连接一个事件处理程序来对信息做任何她喜欢的事情（记录它，收集它以在 UI 中报告，等等）。

You can also include the technique (used in many .NET framework areas, e.g. Windows.Forms events and XSD validation) of passing a CancelableEventArgs object as the second event argument, with a Boolean field that the event handler can set to indicate that processing should abort after all.

您还可以包含将 CancelableEventArgs 对象作为第二个事件参数传递的技术（在许多 .NET 框架领域中使用，例如 Windows.Forms 事件和 XSD 验证），事件处理程序可以设置一个布尔字段来指示处理应该毕竟中止。

Another thing I urge you to do is to prepare your INSERTs and UPDATEs, then call them many times with varying argments.

我敦促您做的另一件事是准备您的 INSERT 和 UPDATE，然后使用不同的参数多次调用它们。