invoke-sqlcmd results are different based on the SqlServer module installed

Question

I'm running a PS script that is loading the SqlServer module and then calling Invoke-SqlCmd. The Invoke-SqlCmd is calling a script with stored procedure.

I originally developed the script using 21.1.18256. Everything is working, but v21.1.18256/Invoke-SqlCmd does not work with -TrustServerCertificate. I found out this option is not available in this version and so decide to upgrade to be able to use this parameter.

I then upgraded to v22.1.1. I tested and found that the Invoke-SqlCmd reacts differently in this version running the same stored procedure. Running the exact same script, the procedure does not work.

I ran it interactively and it see it calling the procedure and shows that it is working. I checked the database and found that no records were inserted.

I have a Try/Catch, Begin/End Tran in the SQL script. I removed the Begin/End Tran and the stored procedure reacted differently. Now it does the inserts as expected, but only partially. In a table that I'm expected 100 records, I'm only getting 25 records.

I've tried v22.0.59 and the results were the same as v22.1.1. When I switch back to v21.1.18256, everything is working correctly and the record counts are as expected. I'm currently switching to native sqlcmd.exe and it is working as expected.

Not sure if anybody have seen this before or am I doing something incorrectly.

I'm on Windows 2019 / SQL Server 2019 / PS v5.1

Answer 1

See: PowerShell: invoke-sqlcmd with Get-Credential doesn't work

And: Invoke-Sqlcmd works on one desktop, fails on another

These might address the issues you are having:

• Not all commands work correctly with -Credential
• Quoted: "Fundamentally - unfortunately - there are two - technically distinct Invoke-SqlCmd cmdlets"
• The obsolete Invoke-SqlCmd command from the obsolete SQLPS module.
• The current Invoke-SqlCmd command from the successor module, SQLServer

Install the correct SQL Server module here: https://learn.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-ver16