Invoke-Sqlcmd 运行脚本两次 [英] Invoke-Sqlcmd runs script twice

问题描述

我遇到了一个非常奇怪的问题，可以重复.

I experienced a very strange issue and can be repeated.

基本上，我使用invoke-sqlcmd通过-inputfile调用脚本文件，但是如果脚本文件有一些执行错误(比如插入到一个列不应该为空的表中)，脚本文件将被执行两次.我也可以从分析器中看到两次执行.

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.

这里是重现问题的方法(我的环境:Win 8.1 + SQL2014 + PS 5.0)

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

1. 在一个数据库中创建两个表

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)

现在用以下两行创建一个sql文件(我们称之为c:\temp\t.sql)

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'

运行以下 PS cmdlet

Run the following PS cmdlet

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

你的 PS 会返回一个错误，现在如果你打开一个 SSMS 查询窗口并执行以下操作

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

select * from TestDB.dbo.s

您会在那里看到两条记录，而不是一条.另一方面，如果我运行 sqlcmd.exe，则没有这样的问题，即 dbo.s 中只有一条记录.有没有我遗漏的 SQLPS 配置?

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?

推荐答案

使用 Microsoft 的修复程序更新此步骤:

Updating this tread with the fix from Microsoft:

SQL Server 2016 SP1 的累积更新 2
SQL Server 2014 Service Pack 2 的累积更新 4

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

修复:如果 SQL Server 2014 或 2016 中出现错误，Invoke-sqlcmd"cmdlet 会多次执行查询语句

这篇关于Invoke-Sqlcmd 运行脚本两次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！