The SQL Server SMO client API can be used query and alter settings and objects within your SQL Server instances, however in case you want to query your user database, you will run into some limitations. The client API simply isn’t aware of your data objects and therefore not exposing a typed scheme. Fortunately, it’s pretty easy to accomplish when using the SQL Server PowerShell Module (SQLPS)
Executing SQL Statements using PowerShell – Examples
The first part will create an instance of the SMO Server object providing the SQL server instance name. In addition it’s required to provide the database context by just providing the name of the user database you would like to access.
PREREQUISITES: You’ll need to install SQL Server Powershell Support Objects library, additional details on how to accomplish this can be found within the following TechNet article http://technet.microsoft.com/en-us/library/hh231683.aspx or a previous blog post.
The server and instance settings
NOTE: For long running queries, it’s required to specify values for $server.ConnectionContext.ConnectTimeout and $server.ConnectionContext.StatementTimeout
Code sample 1
The first sample is pretty basic and just executing a statement without expecting a return statement (ado.net execute non query):
Code sample 2
The following sample will export the query results in CSV format, which can be easily post processed using PowerShell. As you can see this can be very useful when generating custom reports or generating simple exports.
Note: You can separate the SQL statements from the PowerShell scrip just by using the