What extra do I need to access SQL Server from PowerShell?

You don’t need anything additional in the way of .NET libraries or PowerShell modules to work with SQL Server, but for many scripting tasks you can get a lot of extra value from using them.

The approach that you choose towards accessing SQL Server depends on the sort of task you need to produce a script for.

• If you need to just perform queries against SQL Server databases and save the results to a file, then we can just run SQLCMD or BCP from PowerShell.
• You can choose to load the .NET data provider. This gives you a lot more versatility, but you are working at a lower level, having to create in code what is already built into SQLCMD
• You can load SMO or even SQLPS, the SQL Server provider that uses SMO behind the scenes. SMO is used primarily to provide all the functionality for SQL Server Management Studio. Anything you can do in SSMS you can also automate in SMO.
• With SQLPS, you can read the output from the cmdlet Invoke-sqlcmd into PowerShell’s native data objects, and you can process the results and save data to file in a number of formats such as XML

The same is true of outputting data from PowerShell. The built-in features of PowerShell are enough to get you a long way, but what you use depends on your task. If you need more …

• A lot can be done with command-line tools such as BCP
• You can use modules or functions to provide extra, such as writing out in Excel spreadsheet format

This article is all about how you get started with all these approaches, and about how you can load and use the modules and assemblies to do this safely and effectively.

The Various methods of working with SQL Server

Running an External command-line Program

You can run external command-line programs such as OSQL , BCP or SQLCMD. You can call those external programs inside PowerShell and then use them to query the SQL Server instance:

This example will query the test table and redirect the output to a txt file

Another approach is to use BCP utility which allows us very fast import or export of a table, view or query. In the following example we are exporting the product table from AdventureWorks2014:

Something to be aware of is that, if we get an error using this coding style, it will not be visible. The output will be the same. The difference will be that the BCPError.txt will have the error. Let’s export a table that does not exists called IdontExist so as to deliberately cause an error:

‘So Laerte, how can I test in my code if it was successful or not?’

One approach is to use a property that is returned by the start-process called ExitCode. If it is 0, it was running with no errors and 1, of course, an error occurred.

Saving the output in a variable:

Saving only the ExitCode property:

Or even, testing the property directly:

You can read more about BCP here: Working with the bcp Command-line Utility

Querying SQL Server via the .NET data provider

If you don’t need SMO for any other reason, you can query SQL metadata or data, or to work with any kind of SQL Server Object, by using the System.Data.SqlClient Namespace – the .NET Framework Data Provider for SQL Server. It will allow you to use T-SQL commands and then manage what you need in much the same way as you would with the SMO database connection methods. It does not require you to load anything extra, because the namespace is already loaded by PowerShell.

In your test database, execute this…

And then, in PowerShell, amend the server instance name and database, and run the script

If your result set returns more than one table, you can use the DataAdapter and Dataset class:

In addition, you may want to execute a stored procedure:

Using SMO

SMO is a .NET library that enables you to work with SQL Server as objects, classes, methods and so on. When you access a database on a server, the database object provides an open System.Data.SqlClient connection that is exposed by the Database.ExecuteNonQuery and Database.ExecuteWithResults methods that you can use for querying either the server or a database. You can use it in the same way that you use any other SqlClient connection. Check out my Stairway to Server Management Objects (SMO) Level 1: Concepts and Basics on SQLServerCentral.com.

Unlike the previous method, you need to load the SMO assembly before you can use SMO. The traditional way of doing this is to use the .NET method LoadWithPartialName.

This isn’t ideal, and we’ll be discussing refinements to this method later on in the article.

Using SQLPS

If you use SQLPS, you will not only be able to use SMO’s own .NET connection for querying databases but you will also have the SQL CmdLet’s. Everytrhing you need is loaded for you under the covers.The Cmdlet to use for executing SQL is Invoke-SQLCMD, but you can get a complete list of CmdLet’s that are available for use once you have loaded SQLPS by using the PowerShell command …

Invoke-SQLCMD

Invoke-SQLCMD is a compiled cmdlet from SQLPS.

You can also run several .SQL files.

For that let’s use the amazing SQL Server Diagnostic Information Queries for September 2014 from Glenn Berry, copy some SQL from there and split into .sql files. I´ve created three .sql files: FileNamesPaths.SQL, ServerProperties.SQL and SQLServerAgentjobsCategory.SQL

One approach is using the :r parameter inside a .sql file. First create a .sql file with the .SQL files to run: Let’s call this file SQLFiles.SQL

Then it´s just run the .sql file

It will run all the .SQL files you have specified in the SQLFiles.SQL

You may want to export to a txt file the output of all files ran:

This approach can be very useful if you just need to check the output, not generating reports, or even create objects in SQL Server – an full environment for instance.

Another approach is to call invoke-SQLCMD for each file. For that we don’t need the SQLFiles.SQL

You can, of course, export the results to CSV files:

Even better, you can write an XLSX file from one or more CSV files with each CSV file turned into a Worksheet. For that, we can use the function of my fellow PowerShell MVP Boe Prox called Convert-CSVToExcel (you can download the source from the top of this article)

Loading Community functions

You can also run Community functions such as Invoke-SQLCMD2, a System.Data.SQLClient based function wrote by Chad Miller that extends the functionality of Invoke-SQLCMD. You just need to download it and run it inside your script, save into .ps1 file and call the function as a .ps1 script, load it in your profile or even save the code in a psm1 file (script module file) and save in any path shown by the $env:psmodulepath to load directly when calling it (PowerShell 3.0 and higher)

Loading and handling the extras

Installing Module.

To Install a PowerShell module means nothing more than to copy the module files to a folder in a computer. To load a module in small projects is pretty simple. You just need to use the Import-Module cmdlet. If you don’t specify the path of the module PowerShell will look in the environment variable $ENV:PSMODULEPATH and check if it is there and automatically will load it. In PowerShell 2.0 you need to explicitly the import-module in your profile.

Further reading:

• Persistent PowerShell: The PowerShell Profile
• Installing a PowerShell Module

Loading an assembly

Most of the code you will see that uses SMO does not leave the loading of assemblies to SQLPS, but instead uses the loadwithpartialname method. This method is deprecated because it doesn’t give you control of the version of SMO that will be loaded if you have several versions in your system. The choice of file is controlled by GAC*. On the other hand, using the ADD-Type can be a pain because you need to be explicit about the version and change your code. Let’s take a look at some examples:

LoadWithPartialName

The general way:

But we also can check if there is any SMO installed and return an error if not, otherwise it will be loaded:

Add-Type:

General way:

Testing:

The recommend way is to use ADD-Type, but what is the best approach? Well, it depends. If you are dealing with several version of SMO and it’s not a problem for your PowerShell environment then loadwithpartialname will be easier; otherwise, use add-type. But always remember LoadwithpartialName is a deprecated method and in some point will be excluded from the framework

Checking which version of SMO or SQLPS was loaded in the session

By default, PowerShell loads some assemblies when you open a session. Of course, it needs some basic assemblies to work properly. SMO is a .NET library and it needs to be loaded. The same happens with SQLPS module. To find out which assemblies were loaded, you can use this code (credits to fellow PowerShell MVP Richard Siddaway – Assemblies loaded in PowerShell:)

For our purposes, we just need to filter for SMO:

Finding out the version of the SQLPS module is a little different. PowerShell has a cmdlet called Get-Module that shows all the information on the modules.

Also it has a parameter to check whether a specific module was loaded:

Also you can check what modules are available to load using the parameter – ListAvaliable

The Get-Module cmdlet will search all the modules available in the Environment variable $Env:PSMODULEPATH, a special variable that contains all the paths for the installed modules and will list them for you.

Determining the version of SQLPS that PowerShell will load

If you use the name parameter, PowerShell will always check in the $env:psmodulepath variable and then will, according fellow PowerShell MVP Joel Bennet, import them in the following order:

Path order, then alphabetic order, then highest version if you have side-by-side modules in the same folder (in PS5)

Loading a specific version of SQLPS or SMO

That´s an interesting question that comes out in the comments on the SMO stairway, and I will give a different answer for SQLPS, which is a module, and SMO which is a group of assemblies.

Loading a particular version of SQLPS

As we saw, if we use the parameter name in the import-module it will load the SQLPS in the order of the environment variable . So in this specific case we use the parameter -FullyQualifiedName

Or even the parameter -name with the FQDN:

The point is that you need to specify the path for the SQLPS module.

Shawn Melton showed, in the comments, a very cool function to make the job easy:

In this function, you can pass as a parameter the version you want to import and the default is 130.

This example will load the highest SQLPS version

We ended up with another version of Shawn´s function where you can choose the most recent SQLPS version by using a switch parameter -highest

Loading a particular version of SMO:

If you use the code that I included earlier on, it will only show you the assemblies that are already loaded in your session. If you use the loadwithpartialname method to load the SMO, this is controlled by the GAC* . If you want to load a specific version of SMO, I recommend that you should use ADD-Type and you can then specify which version will be loaded:

Using the same idea from Shawn, we could think of doing something similar with SMO, but for this we would need to query the GAC: Let’s say we want to load the highest version SMO installed

We could then develop this to speed it up by saving on the pipeline traffic. This is achieved by doing the selection within the Get-ChildItem cmdlet.

You can read more about how modules work in PowerShell here: (Importing a PowerShell Module)

*GAC or Global Assembly Cache is a machine-wide code where the assemblies are installed and stored to be shared with applications on the computer.

Creating a Custom Type Accelerator for SMO

You will sometimes find that when you load an assembly like SMO, that specifying the NET types is laborious. Type accelerators are a great help for getting around this. Type Accelerators are like shortcuts for .NET types. It can simplify your code and make it more intelligible if you use them. For instance [adsi] is a type accelerator for System.DirectoryServices.DirectoryEntry or [pscustomobject] for System.Management.Automation.PSObject. You can do the same for SMO

This code shows the type accelerators currently defined in your session:

To create a custom type accelerator you just need to add the key and the .net type:

So for instance, to create a type accelerator for the SMO Server:

Then I can easily access all properties and methods using the new type accelerator.

Or also call a method:

Now it´s just play around it and add in your PowerShell profile to load your custom SMO type accelerators

Reference: Boe Prox – Quick Hits: Finding, Creating and Removing PowerShell Type Accelerators

Conclusion

There really isn’t a single best method of accessing SQL Server via PowerShell. What you use depends on what you need to achieve. Every approach has its advantages and disadvantages. In this article, I’ve described how to get up and running with each of these approaches, and I’ve tried to suggest the best ways of approaching the task.