=== UPDATE 2014-10-19 === I created a separate module that uses the SqlClient where you do not have to specify the command type and where you can also use LocalDB connections. ===
For the full module and description see my Post Module: biz.dfch.PS.System.Data.
=== UPDATE 2013-12-21 === added an additional parameter to specify the command type manually ===
Sometimes we have to access data from a (SQL) database from PowerShell script but do not want to install the management tools from SQL Server just to get the ‘SqlServerCmdletSnapin100’ snapin to use ‘Invoke-SqlCmd’. However we created enough scripts that got used to the syntax of Microsoft’s Cmdlet. So here we are and present a ‘clone’ to the original ‘Invoke-SqlCmd’. We actually use it as a module so we can define default parameters within the module configuration file (that means you can ignore the default values in the ‘PARAM’ block). However, standalone it is just as good to use …
Logging has mainly been removed from the function code. In addition, the returned result set is actually an array of PSCustomObjects and the order of the properties might change from the original one defined in the query or the table.
Note: if you have installed both, the PSSnapin from Microsoft and this module, calling ‘Invoke-SqlCmd’ by default will use the module as this gets implicitly loaded in PowerShell 3.0, whereas the PSSnapin has to be loaded explicitly via ‘Add-PSSnapin’.