Here's an extremely useful stub to get data from SQL-Server within a PowerShell script:

$TaskName = "20071029-AllRejectedDuring"
$SqlServer = "SQLDEV01";
$SqlCatalog = "MyData";

# Get the T-SQL Query from .SQL file
$SqlQuery = Get-Content (".\" + $TaskName + ".sql")

#Write-Host ($SqlQuery) -foregroundcolor "gray"

# Setup SQL Connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = 
          "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"

# Setup SQL Command
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

# Setup .NET SQLAdapter to execute and fill .NET Dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet

#Execute and Get Row Count
$nRecs = $SqlAdapter.Fill($DataSet)

Write-Host ($nRecs.ToString() + " Records retrieved.") -foregroundcolor "Cyan"
$SqlConnection.Close();

if ($nRecs -gt 0)
{
  # Do Stuff
  $DataSet.Tables[0].Rows[0][0]  #Print first data element
}

The connection is using Integrated Security for simplicity, it wouldn't be difficult to switch to UID/PWD instead.  Also, I put the SQL in a .sql file (flat text) to make life easier; you could also put the statement in the string declaration, if it is a simple query.  PowerShell's Get-Content mechanism makes reading a file very easy.  Also, clean up after yourself, I won't include that here. UPDATE:  I have posted a full script to export to Excel or XML in a followup post.

Earlier, I wrote about getting data from MS-SQL with PowerShell, which is a very useful and powerful thing.  But just displaying data is very limited.  I have a need at work to create extract "reports" for business owners frequently into Excel.  So I have leverage this scripting technique to do the legwork for me.  After many iterations, I have completed the first version of a script that will take a SQL query in a text file and create an Excel file with the results.  All fully automated with time-stamping, archiving of the successful query, and the ability to export XML or CSV as well.

The script is unfortunately only listed as a page on this site because WordPress does not allow posting of non-media files.  You will have to copy the full content and paste it into a text file named QuerySQL.ps1.  One day, I will host this weblog on a real provider in order to have better control (I will accept donations!).  If many folks prefer, I will post it on my personal webspace with my ISP and provide links on request.  And maybe to CodeProject too.

Feel free to use it in anyway you wish.  Please learn from it if you can, or conversely, send me a note on how to improve it.  I would love to get the field-name bolding working among other things.

Here's an extremely useful stub to get data from SQL-Server within a PowerShell script:

$TaskName = "20071029-AllRejectedDuring"
$SqlServer = "SQLDEV01";
$SqlCatalog = "MyData";

# Get the T-SQL Query from .SQL file
$SqlQuery = Get-Content (".\" + $TaskName + ".sql")

#Write-Host ($SqlQuery) -foregroundcolor "gray"

# Setup SQL Connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"

# Setup SQL Command
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

# Setup .NET SQLAdapter to execute and fill .NET Dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet

#Execute and Get Row Count
$nRecs = $SqlAdapter.Fill($DataSet)

Write-Host ($nRecs.ToString() + " Records retrieved.") -foregroundcolor "Cyan"
$SqlConnection.Close();

if ($nRecs -gt 0)
{
  # Do Stuff
  $DataSet.Tables[0].Rows[0][0]  #Print first data element
}

The connection is using Integrated Security for simplicity, it wouldn't be difficult to switch to UID/PWD instead.  Also, I put the SQL in a .sql file (flat text) to make life easier; you could also put the statement in the string declaration, if it is a simple query.  PowerShell's Get-Content mechanism makes reading a file very easy.  Also, clean up after yourself, I won't include that here.

UPDATE:  I have posted a full script to export to Excel or XML in a followup post.

If you do any scripting at all in Windows, you should be using PowerShell to do it.  But the first time you do, assuming you've installed it properly is the following:

File C:\DEV\Report.ps1 cannot be loaded because the execution of scripts is
 disabled on this system. Please see "get-help about_signing" for more details.

You have two options:  sign your scripts (you should do this) or take the easy route and change the Execution Policy (do this at your own risk).  If you want to do it the right way, see Scott Hanselman's excellent post on the subject.  If you just want to run ps1 scripts, and are very careful about the source, namely yourself, execute the following command within the PowerShell shell:

Set-ExecutionPolicy RemoteSigned