SQL Select Query to PowerShell Help

So.... I have a SQL (MSSQL 2005) query that gathers some data I need to represent or send elsewhere. In this particular case I need to create a CSV from the query, which in PowerShell is pretty simple stuff.

HOWEVER my problem is this. How do I run my query (preferably against a remote system/the SQL server) ? How do I pass the query required parameters? (see PARAM1, PARAM2, PARAM3) ?

Bonus points... PARAM1 should be a date, format yyyy-mm-dd works. PARAM2 should be one of three options, OPT1, OPT2, OPT3 PARAM3 should be a string of X number of characters.

PS Yes using osql.exe occurred to me, but as I was scripting it in batch it dawned on me that... well I don't want to use batch anym开发者_C百科ore. ;-)

Select COL1, COL2 FROM [database].[dbo].[function](PARAM1,PARAM2,PARAM3);

This is pretty easy with the SQL cmdlets in 2.0:

$Query = "Select COL1, COL2 FROM [database].[dbo].[function](PARAM1,PARAM2,PARAM3)"
$Results = Invoke-SQLCmd -ServerInstance <server> -Database <DB> -Query $Query

Then you can use the column names in your $Results, i.e.:

$Col1 = $Results.COL1

You can parse your $Query for your other parameters too, i.e.:

$Query = "Select COL1, COL2 FROM [database].[dbo].[function](" + $Param1 + "," + $Param2 + "," + $Param3 + ")"

EDIT:

To enable the snapins, use:

Add-PSSnapin SqlServerProviderSnapin
Add-PSSnapin SqlServerCmdletSnapin