invoke-sqlcmd not recognized even though SqlPs loaded

2
votes

I am trying to create a shell script to run a query and store the output, and I've got my script looking like this so far:

push-location;
import-module SqlPs;
Pop-Location;
$myData = invoke-sqlcmd -InputFile "C:\<path>\blah.sql" -serverinstance dbatest -database testdb;
$mydata | out-file C:\Users\<path>\shelloutput.csv;
remove-module sqlps;

It loads SqlPs fine which I can check with with Get-Module -ListAvailable producing thisenter image description here

but when I do invoke-sqlcmd it is not recognized:enter image description here

Any ideas as to what else I can do?

Edit 1

When I add the option -Verbose I see this error enter image description here saying that some files are already present. Is this error preventing me from loading the module? When I do Get-Module it doesn't list the SQLPS module.

EDIT 2

Loading the 32 bit version gives me fewer errors but still errors loading files...now only 2 files are missing as opposed to 5 earlierenter image description here

1
powershell
Get-Module -ListAvailable shows what modules are present, not which ones were loaded. Drop the -ListAvailable to show what is loaded into the current session. - Mike Shepard
@MikeShepard made an edit reflecting your comment...I tried using import-module sqlps -force but that didn't help - Moderat
Is there a platform mismatch between PowerShell and SQL server? If you're running 32 bit SQL server, try launching the 32 bit version of PowerShell from C:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe and see if that can load SQLPS. - briantist
@briantist made an edit that reflects your comment, all it does is drop the number of files already present - Moderat

1 Answers

1
votes

I found on my system that I had in the path several sql bin references to 32 bit (Program Files (x86)) paths ahead of the 64 bit paths. I moved all the 32 bit paths below the 64 bit paths and it cleared up the issues.