问题描述
我遇到了一个非常奇怪的问题,可以重复.
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)
在一个数据库中创建两个表
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 运行脚本两次的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!