Does Powershell, through invoke-sqlcmd or through running a query directly through a cmdlet, wrap a query in an implicit transaction? And if so, is there a way to work around this should my query require it?

For a trivial example of what I'm getting at:

$sqlcmd = "WHILE(1=1)
               BEGIN TRANSACTION
                   SELECT @@TRANCOUNT;
               SELECT @@TRANCOUNT;
               IF(@@TRANCOUNT = 0)

Running this through Powershell should result in something that never ends, whereas running it in SSMS results in a query that ends in one iteration. Would immediately committing a transaction at the start work, or are there other ways to work around this in Powershell or through changing the query?

To rephrase the question, can we support proper transaction scoping using Powershell?

  • 2
    Pardon for not answering your question but I have found invoke-sqlcmd to have some bugs when I was using it and found it much easier to just call osql or sqlcmd from powershell and get similar functionality. Even the MSSQL DSC code uses OSQL internally instead of invoke-sqlcmd because workflows like DSC were having incompatibility issues with invoke-sqlcmd Apr 28, 2016 at 22:33
  • It looks like this has been fixed, it no longer loops forever in 16.4.1 (the SqlServer module with SSMS). Oct 16, 2016 at 9:47


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.