Invoke-sqlcmd not running on chef powershell prompt

We are not able to run “invoke-sqlcmd” command on client (node).

As a solution we found out that Powershell 5 and SqlServer Module is needed on the server to enable it to run invoke-sqlcmd cmdlet. But even after downlaoding sqlserver module it is not working.

On further investigating we found out that “PSModulePath” Environment variable needs to have “;E:\Program Files(X64)\Microsoft SQL Server\120\Tools\PowerShell\Modules” in it’s value. But this is also not working and Chef-client will fail with msg
“invoke-sqlcmd is not recognised as cmdlet”

And if I set the environment variable again with the same value. Chef-client executes successfully without the last error. However the SqlServer module that I download on server disapperas from the path “C:\Program Files\Winddows Powershell\Modules”

I am not able to understand this behavior of chef-client. Kindly advise.

Can you post some of your recipe? I’ve installed modules and environmental paths in a recipe and they’ve worked.

Can you please show that particular resource mentioned in a recipe to set the environment variable and the path or the moudle required.
It would be a great help

I use a powershell script so powershell_script

And what will be the complete content of that powershell_script.

How this resource will set the env variable path?

Please share that specific powershell_script block with us so that we can check that in our cookbook.