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 ""[email protected]"""

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 ""[email protected]"""

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

postanote · Accepted Answer · 2020-02-19 07:22:28Z

This...

write-host "Enable-RemoteMailbox ""$name"" -RemoteRoutingAddress ""[email protected]"""

... 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 [email protected]}

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)

What I am trying to do is store the login id and pass it to an stored procedure.But i am not sure how to pass this as a paramtere, this doesn't work. $SqlCmd.CommandText = "exec usp_UpdateRemoteMailbox '$PSItem.loginid'"

Collectives™ on Stack Overflow

Saving SP output into a variable in powershell

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related