Powershell SQL Select Output to Variable

Powershell SQL SELECT output to variable

Alternatively you could use the following code, if you are looking for simple return values rather than tables for processing later.

[string] $Server= "10.0.100.1",
[string] $Database = "Database123",
[string] $SQLQuery= $("SELECT [FeedID] FROM [dbo].[FeedList] WHERE [FeedFileName] = 'filename.txt'")

function GenericSqlQuery ($Server, $Database, $SQLQuery) {
    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
    $Connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $SQLQuery
    $Reader = $Command.ExecuteReader()
    while ($Reader.Read()) {
         $Reader.GetValue(0)
    }
    $Connection.Close()
}

Powershell SQL Query to variables

I work with data tables directly. You should also look into handling parameters correctly. It helps with handling special characters in the variable value. And, it protects from SQL Injection Attacks:

$Sql = 'select top 100 first_name, Last_name from person where last_name=@lastname'
$Database = 'XXX'
$Server = 'XXX'

$LastName = 'Jones'

$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True"
$Connection.Open()

$cmd = new-object system.Data.SqlClient.SqlCommand($Sql, $Connection)

$null = $cmd.Parameters.AddWithValue('@lastname', $LastName)

$DataTable = New-Object System.Data.DataTable
$SqlDataReader = $cmd.ExecuteReader()
$DataTable.Load($SqlDataReader)

$Connection.Close()
$Connection = $null

$DataTable.Rows.Count

foreach ($r in $DataTable.Rows)
{
    $fn = $r.first_name
    $ln = $r.last_name    

    "$fn $ln"
}

Powershell query database and store results in a variable

$DataSet.Tables[0] is an object with the columns from the query as properties. In your case $list will be an PowerShell object with property ServerName.

Solution 1

When calling Get-DbaSqlService -ComputerName $server, you will pass one the whole object, where you only want to provide the name, when you change this to Get-DbaSqlService -ComputerName $server.ServerName you will only pass on the value of the property ServerName.

Solution 2

As you only want to explore the Servername property, you could already expand it so $list will represent what you expect. Therefore you should add Select-Object -ExpandProperty ServerName where you define $list.

So

$list = SQL_Query "Select ServerName From Server_Inventory Where Status = 'A' " 

becomes

$list = SQL_Query "Select ServerName From Server_Inventory Where Status = 'A' " | Select-Object -ExpandProperty ServerName

---

Addition

The $output variable is overwritten in each loop. The results for each server should be appended to the output variable.

$output = @()
Foreach ($server in $list) {
   $output += Get-DbaSqlService -ComputerName $server | Where-Object {$_.ServiceType -in ('SSIS','Agent','Engine','SSRS','FullText')} 
}
$output | Out-GridView -Title "View all SQL Services"

Passing a single value from a SQL query to a PowerShell variable

You've seen that $password is a [DataRow], and Write-Host can only use [string]. Try one of these two:

• Output the result by itself (no write-host). Powershell will format all of the fields and rows for you:

$password

# outputs:

password
--------
hunter2 

• Output just the string from your query result instead of the table format above:

$password.Password

# Outputs:

hunter2

DataRow objects are made to contain multiple rows and multiple fields, so it can get confusing when you're expecting just a single value as a result.

How to capture output message and save it to variable in powershell and SqlCmd

These are just standard exception errors that you can capture using the CommonParameter -ErrorVariable or by using the automatic variable $Error.

Below is an example of using ErrorVariable. We tell Invoke-SqlCmd to store any errors encountered in a variable called 'dbCheckErrors'. We later check if this variable $dbCheckErrors contains any ErrorRecords and if it does we collect all the Exception messages into our $errorCollection array for later access. Once done with our db checks we can either view the messages, save the messages into file, both, or whatever else.

# Create an empty array to later capture error message in our loop
$errorCollection = @()
foreach ($DB in $Databases)
{
    Write-Output "Processing $($DB.Database)..."
    Invoke-SqlCmd -ErrorVariable 'dbCheckErrors' -ServerInstance '(localdb)\MSSQLLocalDB' -Database master -Query "DBCC CHECKDB ([$($DB.Database)]) WITH NO_INFOMSGS,PHYSICAL_ONLY;" -OutputSqlErrors:$true -Verbose 

    if ($dbCheckErrors) {
        # Do something with any errors here

        # Or just add the exception message(s) to a collection to handle later
        $errorCollection += $dbCheckErrors.Exception.Message
    }
}

# view the exception messages
$errorCollection

# save the exception messages to file
$errorCollection | Set-Content -Path '.\dbCheckErrors.log'

Retrieving data using select SQL statement in Powershell

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=HOME\SQLEXPRESS;Database=master;Integrated Security=True"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from sysdatabases where name = 'tempdb'"
$SqlCmd.Connection = $SqlConnection
$dbname = $SqlCmd.ExecuteScalar()
$SqlConnection.Close()
Write-output "Database is " $dbname

Put sql query output in variable powershell

Sqlcmd is different from most Windows applications that it is case sensitive. That is, switches -q and -Q have different purposes.

-q "cmdline query"  
-Q "cmdline query" (and exit)

Since lower case q means to query without exit, change it to upper case q for query and exit behavior.

How to give to a powershell variable only the output of my sqlplus query?

You need add ';'

PS C:\> 'select 2 as result from dual;' | sqlplus -s user/password@DEV19

    RESULT
----------
         2

If you want only '2' then add this lines:

'SET FEEDBACK OFF 
set heading off
set termout OFF
SET FEEDBACK OFF
SET TAB OFF
set pause off
set verify off
SET UNDERLINE OFF
set trimspool on
set timing off
set echo off
set linesize 1000
set pagesize 100
select 2 as result from dual;' | sqlplus -s user/password@DEV19
     2

---

Related Topics