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)

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)

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'

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?

Reproducible. Maybe the dba.stackexchange.com folks have more insight, so I'll vote for migrate. — vonPryz
– vonPryz, Commented Oct 22, 2015 at 11:09

jumxozizi · Accepted Answer · 2018-08-09 12:02:12Z

2

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);

edited Aug 9, 2018 at 12:02

jumxozizi

64910 silver badges23 bronze badges

answered Oct 22, 2015 at 11:31

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jyao Over a year ago

Thanks @Dan. I know an alternative way to solve this issue, because when I use invoke-sqlcmd2 (a function in sqlpsx link, which uses System.Data.SqlClient.SqlCommand, there is no problem. I guess my question or concern should be "this is a BIG bug for SQLPS module"

jumxozizi Over a year ago

Worked for me without the "Add-Type" line.

jumxozizi Over a year ago

I didn't need the "Add-Type" line, when executing from the console directly. Didn't work without it in a script though. This path didn't exist in my environment, so I used [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null instead.

Dan Guzman Over a year ago

@jumxozizi, Add-Type or LoadWithPartialName isn't needed if the assembly is already loaded into the current app domain. I's needed if you run it from a stand-alone PowerShell ISE or command-line.

Piotr Palka · Accepted Answer · 2019-11-20 18:00:31Z

2

Updating this tread with the fix from Microsoft:

Cumulative Update 2 for SQL Server 2016 SP1
Cumulative Update 4 for SQL Server 2014 Service Pack 2

FIX: "Invoke-sqlcmd" cmdlet executes a query statement multiple times if an error occurs in SQL Server 2014 or 2016

answered Nov 20, 2019 at 18:00

Piotr Palka

3,2351 gold badge12 silver badges20 bronze badges

1 Comment

Thangadurai Over a year ago

Great. It really helped to me solve a long pending issue.

Hemendra Singh · Accepted Answer · 2017-10-10 20:35:37Z

1

Even i had a similar issue.

Fixed it by adding -QueryTimeout parameter to Invoke-Sqlcmd cmdlet.

Basically it seems that the query somehow times out and due to a bug in Invoke-Sqlcmd cmdlet it tries to insert it again. Strange but try this. Keep the query timeout big enough for the query to execute.

answered Oct 10, 2017 at 20:35

Hemendra Singh

1481 silver badge4 bronze badges

Comments

Markus · Accepted Answer · 2018-03-08 10:52:02Z

0

I know this is a old thread, but maybe it can help someone.

I found out if you move your line "select 1, 'good morning'" in your example before the insert statement, which has the exception, it works like intended.

I had a similar Issue with try catch, that the first return value will be ignored when it's an exception, so I had to make sure that the first line was Select 1. Strange bug.

answered Mar 8, 2018 at 10:52

Markus

1

Collectives™ on Stack Overflow

Invoke-Sqlcmd runs script twice

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related