Invoke-Sqlcmd erroneously Inserts multiple rows when check constraint occurs - by cashfoley

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


16
0
Sign in
to vote
ID 811560 Comments
Status Resolved Workarounds
Type Bug Repros 3
Opened 12/13/2013 1:52:36 PM
Access Restriction Public

Description

I was prototyping using Invoke-Sqlcmd using the script at http://msdn.microsoft.com/en-us/library/ms188792.aspx

I was getting strange behavior with duplicate entries.  I tested the script using sqlcmd.exe and had no issues.

I simplified the example to use one table with a check constraint and was able to reproduce the error.

In my steps to reproduce, if you remove the line inserting 4 you correctly get 1,2,3

The same error occurs if you pass the query by parameter
> Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $Query 

If you execute the query using sqlcmd.exe it works correctly

> & sqlcmd -S $Server -d $Database -i $TempFile

Sign in to post a comment.
Posted by Microsoft on 4/27/2017 at 8:30 PM
Actually, the new 'SqlServer' module can be downloaded from the PS Gallery.

The current version is available here: https://www.powershellgallery.com/packages/SqlServer/21.0.17099

Thanks,
-Matteo
Posted by Microsoft on 11/22/2016 at 8:03 AM
Hello all,

This issue has been fixed so that it will no longer retry failed queries silently. The fix will be available in the next RC build of SSMS 17.0.

Thank you for the report!

-Charles Gagnon (chgagnon@microsoft.com)
Posted by Cody Konior on 7/8/2016 at 12:13 AM
Microsoft, please explain why this serious issue will not be fixed. Any batches with a failing statement inexplicably get executed twice. I can reproduce this in the latest SQL 2016 SQLPS and SSMS July 2016 SqlServer modules.
Posted by jyao on 10/25/2015 at 11:43 AM
I have experienced exactly the similar issue as I describe it here http://stackoverflow.com/questions/33271446/invoke-sqlcmd-runs-script-twice.
IMO, Invoke-sqlcmd is actually the MOST important cmdlet in SQLPS modue, and with more people using sqlps, this bug (exists in sql server 2014 as well) alone can actually make people giving up the sqlps. So please fix it at your earliest convenience, and do not treat it lightly.