SQL Select Query to PowerShell Help

Question

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 anymore. ;-)

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

JNK · Accepted Answer · 2010-11-23 23:29:06Z

2

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

edited Nov 23, 2010 at 23:29

answered Nov 23, 2010 at 21:14

JNK

65.5k15 gold badges127 silver badges138 bronze badges

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

6 Comments

Aaron Wurthmann Over a year ago

OK Noob time. I've spent the last half hour or so looking for where to download/add Invoke-SQL. (I also did that the other night and wasn't able to find it). I foolishly thought it would in the "Microsoft Windows PowerShell Extensions for SQL Server" off the SQL Feature Pack page. But after installing it I was no where closer. LOL

JNK Over a year ago

You have to install the packages. I'll add a note for that in the answer.

Aaron Wurthmann Over a year ago

Ohh you know I tried that. Nada.. :) Gonna reboot maybe, just maybe its required and I missed that.

Aaron Wurthmann Over a year ago

F'it I'm going to install the full blown management tools. I thought I could get away with traveling light but apparently not. shrug

Aaron Wurthmann Over a year ago

Alright I got it! I installed the management tools then ran Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100

|

Collectives™ on Stack Overflow

SQL Select Query to PowerShell Help

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related