Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

A SQL Cmdlet a Day 4 SQLPS

, 8 Apr 2014
Rate this:
Please Sign up or sign in to vote.
A SQL Cmdlet a Day 4 SQLPS

Over the past few days, I have posted about working with SQL using PowerShell, introduction, piping, variables, Each one of these posts demonstrated working with a PowerShell session that was launched from SQL Server Management Studio rather than a Windows PowerShell prompt. If you have attempted any of the code snippets included in the posts within Windows PowerShell, you most likely would have received an error. The reason is that launching PowerShell from SSMS opens a prompt where the SQL Server PowerShell snap-ins have been loaded and registered, while Windows PowerShell does not automatically load these. The short answer is that Windows PowerShell does not automatically include all of the SQL modules that are needed to work with SQL Server.

To demonstrate this, open up a PowerShell prompt from SSMS and let’s take stock of the modules that are automatically loaded:

[appdomain]::CurrentDomain.GetAssemblies() | SELECT FullName

image

You will notice that there are a number of assemblies that are returned, but which ones are related to SQL? By piping the results to a Where cmdlet and then to our Select, we are able to filter those modules that have “sql” within the FullName:

[appdomain]::CurrentDomain.GetAssemblies() | where {$_.FullName -like “*sqlserver*”} | Select FullName

imageKewl, but what’s the difference between this and Windows PowerShell? Running the same cmdlet in a Windows PowerShell prompt shows that there are no assemblies that apply to our filter, having SQL in the FullName:

image

While working within Windows PowerShell, consider that you want to create a variable that is the localhost default instance of SQL. Running the below cmdlet in Windows PowerShell results in the following error:

$server = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’)  -argumentlist “localhost”

image

The reason for the error is that the SQL modules references have not been loaded. This does not mean that you can never use a Windows PowerShell prompt to work with SQL, but rather that you must first import the SQL modules. There are several ways of doing this, but the easiest is to call the sqlps utility from within your Windows PowerShell session:

image

Running this simple command starts a Windows PowerShell 2.0 session with the SQL Server PowerShell provider and cmdlets loaded and registered, which means I can now work with this prompt the same as if I had started it from SSMS. Something that may also jump out is the path of the prompt has changed. Rather than being in the current user directory, C:\Users\David>, the prompt is now in the context of PS SQLSERVER:\>.

As I said this is the easiest means, but it has also been marked as deprecated, which means that we should use our Import-Module cmdlet instead. Several additional steps MUST be taken to successfully import this module:

  1. The Windows PowerShell prompt must be run as Administrator
  2. The execution policy must allow signed scripts signed by a trusted security provider

Set-ExecutionPolicy RemoteSigned.

Once that has been done, you can call your Import-Module cmdlet:

Import-Module “sqlps” –DiableNameChecking

image

A couple more hoops to jump through, but the end result is the same. We can now verify the assemblies by again querying the loaded modules:

[appdomain]::CurrentDomain.GetAssemblies() | where {$_.FullName -like “*sqlserver*”} | Select FullName

image

Now running my cmdlet assigning the localhost default instance to my variable succeeds:

$server = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’)  -argumentlist “localhost”
$server.Databases 

image

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

derekman9707

United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 8 Apr 2014
Article Copyright 2014 by derekman9707
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid