Invoke-Sqlcmd runs script twice

Question

I experienced a very strange issue and can be repeated.

Basically, I use invoke-sqlcmd to call a script file by using -inputfile, but if the script file has some execution error (like insert into a table where a column should not be null), the script file will be executed twice. I can see the two executions from profiler as well.

Here is the way to reproduce the issue (My environment: Win 8.1 + SQL2014 + PS 5.0)

1. create two tables in a database

   Use TestDB
   
   create table dbo.s (id int identity primary key, b varchar(50));
   
   create table dbo.t (id int primary key, s_id int, b varchar(50));
   alter table dbo.t add constraint fk_t foreign key (s_id) references dbo.s(id)
   

2. Now create a sql file (let's call it, c:\temp\t.sql) with the following two lines

   insert into dbo.s ( b) select  'hello world'
   insert into dbo.t (s_id, b) -- purposely missing id to cause an error
   select 1, 'good morning'
   

3. Run the following PS cmdlet

   invoke-sqlcmd -Server "<my_local_server>" -database TestDB -inputfile "c:\temp\t.sql"
   

Your PS will return an error, now if you open an SSMS query window and do the following

select * from TestDB.dbo.s

You will see two records there instead of one. On the other hand, if I run sqlcmd.exe, there is NO such issue, i.e. just one record in dbo.s. Is there some configuration in SQLPS I missed?

Answer 1

I see you asked this same question on the MSDN Database Engine forum: https://social.msdn.microsoft.com/Forums/en-US/d4167226-2da7-49ec-a5c2-60e964785c2c/powershell-invokesqlcmd-calls-stored-procedure-second-time-after-query-timeout-is-expired. Below is the SMO workaround from that thread.

$SqlServerName = "YourServer";
$DatabaseName = "YourDatabase";
$ScriptFileName = "C:\Scripts\YourSqlScriptFile.sql";

Add-Type -Path "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll";

$sr = New-Object System.IO.StreamReader($ScriptFileName);
$script = $sr.ReadToEnd();
$sr.Close();

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName);
$db = $Server.Databases[$DatabaseName];
$db.ExecuteNonQuery($script);