The compelling thing about PowerShell is that you’re working with objects instead of just text. For SQL folks you’re already aware of the benefits of working with objects is better because you know it’s easier to add a week to a date that is stored as a DATE datatype instead of a date that is stored as a VARCHAR datatype.
The same benefits hold true when working with a datatable instead of an array. For one thing, when working with a DataTable you can very easily insert your result set into a table inside a SQL Server database using this very handy function that I’ve written about before. I’m not going to get into all of the benefits of DataTables today, because you can’t get them natively from the Invoke-Sqlcmd and that’s the problem!
Many years ago Chad Miller ( b | t ) improved his Invoke-Sqlcmd2 script to include outputting the results as either a “DataSet”, a “DataTable”, or a “DataRow”. For years I’ve hoped that the SQL Server team would notice this and add similar functionality to the Invoke-Sqlcmd cmdlet that comes with SQLPS. With the wonderful news that they’ve already fixed the first 3 issues we submitted (and y’all Up-Voted) on SQLPS it’s obvious that NOW is the time to ask for this.
So today I created a Connect Item asking the SQL Server team add this. Without this functionality, taking query results from SQL Server and trying to pass them to other objects/commands in PowerShell is downright archaic! Please Up-Vote this J
