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

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 sqlserverprovidersnapin100add-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