CODEWITHSUNDEEP
sql-serverpowershellexception
Glowie
Glowie

Reputation: 2309

Catch exception calling "ExecuteNonQuery"

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

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.

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.

But the script continues to run.

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

How do I handle this exception?

Upvotes: 0

Views: 962

Answers (1)

M.Ali
M.Ali

Reputation: 69524

$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"

Upvotes: 1

Related Questions