With PowerShell becoming more popular in daily DBA work, I’d like to mention one huge ugly bug I recently encountered to, IMHO, an corner-stone cmdlet in SQLPS module, invoke-sqlcmd.
I mentioned this here http://stackoverflow.com/questions/33271446/invoke-sqlcmd-runs-script-twice (and also in MS forum)
But I’ll give a more simplified version here to verify this bug (for SQLPS module of SQL 2012/SQL 2014)
First, let’s create two test tables in TestDB
use testdb --drop table dbo.s, dbo.t create table dbo.s (id int identity primary key, b varchar(50)); create table dbo.t (id int primary key, b varchar(50));
Ok, in PowerShell, now let’s just run two t-sql statements, one insert into each table, with the 2nd statement firing an error (I purposely make it error out by not populating the primary key column)
import-module sqlps; invoke-sqlcmd -Server localhost -Database TestDB -Query "insert into dbo.s (b) values ('Hello'); insert into dbo.t (b) values ('World')"
Now you will see the following error
Now, if we check what is in table dbo.s, by select * from dbo.s, we will see two records instead of the expected one record as shown below
To me, this bug alone will stop me from using this invoke-sqlcmd, yet without which, SQLPS seems crippled in many cases.
When I try to report this as a bug in MS Connect, I find someone has reported a similar issue before in 2013, yet, MS closed it as “Won’t Fix”, I guess this is another bad decision from some PM who really did not appreciate the importance of this bug. The MS connect item is here
I hope those DBAs who are keen on using SQLPS, please vote it up to let MS change their weird previous decision.
Once SQL 2016 is RTM’ed, I will test it again and see how it goes and report back here.