捕获异常调用“ ExecuteNonQuery”； [英] Catch exception calling "ExecuteNonQuery"

问题描述

我有一个Powershell脚本，该脚本可以将主键值和另一个值插入SQL Server 2012数据库中。

I have a Powershell script that inserts a primary key value and another value into a SQL Server 2012 database.

这是 INSERT 包含 IF NOT EXISTS 的SQL字符串，其中 $ sql_output 是 int

This is the INSERT SQL string that includes IF NOT EXISTS, where $sql_output is an int

$SQL_UPDATE = "BEGIN
    IF NOT EXISTS (SELECT CONVERT(VARCHAR(12), GETDATE(), 107) as Date_to_Display
                    FROM dbo.Download
                    WHERE dbo.Download.Date_of_Download = GETDATE())
    BEGIN
        INSERT INTO dbo.Download
        VALUES (convert(date, GETDATE(), 100),$sql_output)
    END
END"

这是尝试插入数据库中的代码

And this is the code that attempts to insert into the database

$conn_update = New-Object System.Data.SqlClient.SqlConnection

$conn_update.ConnectionString = "Server=10.10.10.10;Initial Catalog=GUP;User Id=$username;Password=$password;"

$conn_update.Open()

$cmd_update = New-Object System.Data.SqlClient.SqlCommand($SQL_UPDATE,$conn_update)

$cmd_update.executenonquery()

$conn_update.Close()

如果我手动运行脚本，并引发了异常，即

If I manually run the script I get an exception thrown, i.e.

使用 0参数调用 ExecuteNonQuery的异常： Violation

重复键值为（2014-08-14）。

该语句具有

Exception calling "ExecuteNonQuery" with "0" argument(s): "Violation of PRIMARY KEY constraint 'PK_Download'. Cannot insert duplicate key in object 'dbo.Download'.
The duplicate key value is (2014-08-14).
The statement has been terminated.

但是脚本继续运行。

但是，如果Windows Server 2012上的Task Scheduler尝试运行此程序，它将无限期挂起。

However, if Task Scheduler on Windows Server 2012 attempts to run this, it hangs indefinitely.

如何处理此异常？

推荐答案

$SQL_UPDATE = "BEGIN
                  IF NOT EXISTS (SELECT 1
                                 FROM dbo.Download
                                 WHERE Date_of_Download = CAST(GETDATE() AS DATE))
                  BEGIN
                      INSERT INTO dbo.Download (Date_of_Download , OtherColumnName)
                      VALUES (CAST(GETDATE() AS DATE) ,$sql_output)
                  END
              END"

这篇关于捕获异常调用“ ExecuteNonQuery”；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！