Invoke-Sqlcmd : A parameter cannot be found that matches parameter name 'OutputAs'

Question

I have a powershell script that will run a sql command and output the results as a dataset.

PowerShell:

(Invoke-Sqlcmd -ServerInstance $Server -Database 'STARS' -Username $Username -Password $Password -query $sqlCommand -OutputAs DataSet)

I am getting the following error when I try to run the script.

Invoke-Sqlcmd : A parameter cannot be found that matches parameter name 'OutputAs'.
At D:\PipelinePowerShellScripts\UnitTests\Generate.Auth.Tokens.ps1:163 char:132
+ ... me $Username -Password $Password -query $sqlCommand -OutputAs DataSet ...
+                                                         ~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Invoke-Sqlcmd], ParameterBindingException
    + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

PowerShell Version:

Major.Minor.Build.Revision

5.1.14409.1005

When I run this script on my local if runs without issues. However trying to run this on a Windows Server 2012 R2 returns the error above.

Answer 1

I had the same problem because the module has been completely re-written for powershell v5. You need to run the get-help invoke-sqlcmd -full on that server to see the available methods in that version.

HERE - you have it better explained ("two products" section)

I think that the easiest way would be to upgrade the powershell on the servers to 5+ and to use the new SQL Module.