Retrieving data using select SQL statement in Powershell

Question

My goal is to assign the value of the results returned to a variable:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=HOME\SQLEXPRESS;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from sysdatabases where name = 'tempdb'"
$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]

The value returned should obviously be 'tempdb', so how can I assign this to a variable so this will work:

Write-output "Database is " $variablename

Desired output: Database is tempdb

Gordon Bell · Accepted Answer · 2009-11-18 20:34:40Z

18

$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

answered Nov 18, 2009 at 20:34

Gordon Bell

13.7k3 gold badges48 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2015-11-01 12:51:11Z

If you are using SQL Server 2008 you should consider using the cmdlets that are available to PowerShell such as Invoke-SqlCmd that can be used to execute queries against a SQL Server database. I've used these on a project to automate the process of applying patches to a database and recording what patches have been applied:

You will first need to use these two commands to make the SQL Server cmdlets available to your session.

add-pssnapin sqlserverprovidersnapin100
add-pssnapin sqlservercmdletsnapin100

Once they are available you can invoke SQL commands as follows.

$x = invoke-sqlcmd -query "select name from sysdatabases where name = 'tempdb'"

The variable $x will hold the results of running the query.

Check out http://msdn.microsoft.com/en-us/library/cc281720.aspx for more details on using the SQL Server cmdlets

Collectives™ on Stack Overflow

Retrieving data using select SQL statement in Powershell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related