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

https://connect.microsoft.com/SQLServer/Feedback/Details/811560

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.