Powershell Invoke-Sqlcmd restarts the scripts from the beginning after an error

Question

I have a .sql file which is similar to the below

BEGIN TRY
   PRINT 'Loading master data....'
   BEGIN TRANSACTION

   -- Some Insert scripts
   -- COPY Settings from DBOne to DBTwo

   PRINT 'Copy Settings Started...'
   INSERT INTO DBTWO.dbo.SETTINGS
   (Id,
    Code,
    Name)
   SELECT Id,Code,Name FROM DBONE.dbo.SETTINGS
   EXCEPT
   SELECT Id,Code,Name FROM DBTWO.dbo.SETTINGS

   PRINT 'Copy Settings completed...'

   COMMIT
END TRY
BEGIN CATCH
   ROLLBACK
END CATCH

I am invoking the sql script with the following powershell command

Invoke-Sqlcmd -ServerInstance ".\" -InputFile ".\Insert_Script.sql" -Verbose

During testing of the script I observed that after printing the text 'Copy Settings Started...' the script started again from the beginning and I see the print statements in the beginning of the file appears again, and when the control reaches the INSERT INTO..EXCEPT, it failed with the timeout exception.

Later I identified that the table 'DBTWO.dbo.SETTINGS' does not exist, and after creating the table the script executed successfully without any issues.

But the question is, why the script restarted again from the beginning and failed at the same place during the second run. At this stage I even checked the sp_who2 and that showed a deadlock, both the victim and the culprit are the same script block. So, with all this explanations, the question remains unanswered is 'why the script started again?'

Could someone please provide a better reasons for this behavior.

Answer 1

Looks like this is an issue with the SQL Server itself. Microsoft has confirmed this issue at https://support.microsoft.com/en-us/help/4010159/fix-invoke-sqlcmd-cmdlet-executes-a-query-statement-multiple-times-if

To fix this, the recommended resolution is to install the updates as mentioned in the above link.

Since, my SQL server version is 2016, I installed the service pack SP2 and the latest cumulative update for SP2 (CU11). After the installation, I ran the script and it failed in the first run itself and throws the expected error correctly ('Invalid Object name...')

This SO post helped me to find the above link.