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

Without seeing what your procedure does, and the table definitions, we cannot diagnose it. Unclear why the whole preamble about TrustServerCertificate is relevant to this. Anyway, it might just be as simple as some bad error handling combined with XACT_ABORT being on or off depending on version. But hard to say without seeing what you are doing. — Charlieface
– Charlieface, Commented Jan 11, 2024 at 11:30

Martin Iszac · Accepted Answer · 2024-01-11 15:17:49Z

0

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

answered Jan 11, 2024 at 15:17

Martin Iszac

5443 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Maze Over a year ago

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review

Martin Iszac Over a year ago

Hi @DavidMaze the answer is to just to use the correct version of the SQLserver module….not sure what you mean here.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related