Executing SQL Statements using PowerShell

The SQL Server SMO client API can be used query and alter settings and objects within your SQL Server instances. In case you want to query your user database; you will run into some limitations. The client API 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. Besides, you must give the database context. This includes the name of the user database you want 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 –InputFile parameter.