Invoke-Sqlcmd unexpected behaviour when using 'WAITFOR DELAY' command in SQL Query

Question

I have a requirement to execute below SQL query from PowerShell using invoke-sqlcmd, which will create two table 'testtable' and 'testtable1' on database 'TestDB'

SQL Query:

 create table testtable (id int null,
 names varchar(50) null);
 WAITFOR DELAY '00:00:59';
 go
 create table testtable1 (id int null,
 names varchar(50) null);

But when i try to execute above SQL query through '.sql' file using below PowerShell command i received an error 'There is already an object named 'testtable' in the database.'.

PowerShell Command:

Invoke-Sqlcmd -inputfile "E:\createtable.sql" -ServerInstance "ServerName" -Database "TestDB"

Errormessage:

Invoke-Sqlcmd : There is already an object named 'testtable' in the database.
At line:1 char:2
+  Invoke-Sqlcmd -inputfile "E:\create ...
+  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Even though i received error in powershell window, table 'testtable' has been created on database 'Testdb'.

I looks to me command 'WAITFOR DELAY '00:00:59';' causing this issue.

Note: I don't have table 'testtable' and 'testtable1' on database 'TestDB' before the execution.

This is a known issue and it has a fix (but it's more effective to just get a newer package rather than updating the native one). — Jeroen Mostert

Ramesh Murugesan Ramesh Murugesan · Accepted Answer · 2017-05-17T09:35:24

Below two options allowed to run the query successful without any error.

Remove WAITFOR DELAY '00:00:29' from the sql query.
Reduce the delay from 59 seconds to 29 seconds it worked.
```
WAITFOR DELAY '00:00:29'
```

Invoke-Sqlcmd unexpected behaviour when using 'WAITFOR DELAY' command in SQL Query

1 Answers