Cannot create a Powershell script with both Invoke-SqlCmd and Invoke-RestMethod?

Question

Powershell v3 adds Invoke-WebRequest and Invoke-RestMethod, but I cannot get Invoke-SqlCmd to work from that version. If I drop back to Powershell v2, I can Invoke-SqlCmd, but now Invoke-WebRequest and Invoke-RestMethod are no longer available.

I could 'Invoke-Expression sqlcmd' or '& sqlcmd' but then I think I have to manually marshal the output - which is just a string - to something more objectified for PS.

Is there any way to have all of these methods at once, preferably from PS3?

Are you explicitly adding the SQL Server Cmdlet Snapin in v3? Maybe something in your profile(s)? — Matt, Nov 01 '13 at 16:27
Try Get-PSSnapin -Registered. Do you see the snapin listed as available? If so, try adding it with Add-PSSnapin. — crownedjitter, Nov 02 '13 at 00:45

score 0 · Answer 1 · answered Nov 04 '13 at 23:53

Invoke-SqlCmd really does exactly that, it executes the external sclcmd with the given arguments.

Alternative:

Import-Module SQLPS
# Create connection to server using Integrated Security
$ServerName = "localhost"
$DBname = "master"

$sqlConn = New-Object -Type:Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList:$ServerName
$server = New-Object -Type:Microsoft.SqlServer.Management.Smo.Server -ArgumentList:$sqlConn
$Database = $server.Databases[$DBname]

# Get script from file:
#$Script = (Get-Content $ScriptFileName) -Join [Environment]::NewLine
$Script = "sp_who"
$Batch = New-Object -TypeName:Collections.Specialized.StringCollection
$Batch.AddRange($Script)
$result = $Database.ExecuteWithResults($Batch)

$result.Tables | Format-Table

Thanks, the `Format-Table` pipe looks like the bit I was missing when wanting to parse the text output to something more objectified. — Gary McLean Hall, Nov 09 '13 at 18:56

score 0 · Accepted Answer · answered Nov 09 '13 at 18:55

I have found no direct answer to my question, just a work-around.

Installing Powershell 4 and using installutil again to register the .dll files for the Sql Powershell Snapin seems to have worked. No matter what I did with installutil when running PS 3, it always registered the snapin in for version 2 of Powershell, which would not work.

Get-PSSnapin -Registered would always show the SQL*100 dlls correctly registered, but for version 2 of Powershell. Repeating the same command for the same dlls when running version 4 would register them for version 4. From there, I could run Add-PSSnapin Sql*100 and Invoke-SqlCmd would then be available.

Although this helped me, it is likely to be something environmental that has gone awry and this answer is probably not going to help anyone much.

Cannot create a Powershell script with both Invoke-SqlCmd and Invoke-RestMethod?

2 Answers2