Saving SP output into a variable in powershell

Question

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=TSG-ADLT014;Integrated Security=true;Initial Catalog =GenExAll"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "exec usp_GetAccountForRemoteMailbox"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

$name = "test"
$LoginId = "jsmith"
$Userid = "14"

write-host "Enable-RemoteMailbox ""$name"" -RemoteRoutingAddress ""$LoginId@wkhs.mail.onmicrosoft.com"""

Right now I am just passing the value in the parameter, $name, $loginid, $userID. I need to store the output from the sp to a variable and then use that variable.

This image is the output of the sp, sp is outputting Userid, Loginid, Name

TO RUN THE CODE ON:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=TSG-ADLT014;Integrated Security=true;Initial Catalog =GenExAll"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "CREATE TABLE [dbo].[Ident_TESTTABLE](
    [UserId] [int] NOT NULL,
    [LoginId] [nvarchar](128) NOT NULL,
    [Name] [nvarchar](2000) NULL
    ) INSERT INTO Ident_TESTTABLE VALUES(12,'ATEST','AMSAL TEST') SELECT  top 1 *  FROM [Ident_TESTTABLE] "
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

$name = "test"
$LoginId = "jsmith"
$Userid = "14"

write-host "Enable-RemoteMailbox ""$name"" -RemoteRoutingAddress ""$LoginId@wkhs.mail.onmicrosoft.com"""

write-host $name
write-host $LoginId
write-host $Userid

Answer 1

This...

write-host "Enable-RemoteMailbox ""$name"" -RemoteRoutingAddress ""$LoginId@wkhs.mail.onmicrosoft.com"""

... will not do anything but write to the screen.

Is this what you are trying to do...

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=TSG-ADLT014;Integrated Security=true;Initial Catalog =GenExAll"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "CREATE TABLE [dbo].[Ident_TESTTABLE](
    [UserId] [int] NOT NULL,
    [LoginId] [nvarchar](128) NOT NULL,
    [Name] [nvarchar](2000) NULL
    ) INSERT INTO Ident_TESTTABLE VALUES(12,'ATEST','AMSAL TEST') SELECT  top 1 *  FROM [Ident_TESTTABLE] "
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]| 
ForEach {Enable-RemoteMailbox $PSItem.name -RemoteRoutingAddress $PSitem.LoginId@wkhs.mail.onmicrosoft.com}

Meaning, take the output from the SP, as it happens to enable O35 mailboxes? No reason to output it in order to use it. Well, unless you wanted to and even that should just have you doing $PSItem.name, etc. No real reason for the extra variables, IMHO.

Oh, so you mean stuff like this...

$SqlQuery = "select Server from ALLdevices where SerialNumber = '$($item.SerialNumber)'"

or this...

$SerialNumber='234978'
$SqlQuery=@"
SELECT Server
FROM ALLdevices
WHERE SerialNumber = '$SerialNumber'
"@
$SqlQuery

There are examples like this all over the web. The above is just stuff I have saved in my OneNote files. Search for say this...

'pass PowerShell variable to a sql query'

Or this...

'pass PowerShell variable to a SQL stored procedure'

... will give example(s) like this...:

Using Powershell Variables in a File-Stored Query with SQL Server

SQL Server - Trouble passing variable to a stored procedure

Pass a powershell variable into a SQL value during out-datatable (invoke-sqlcmd2)