Click here to Skip to main content
11,484,941 members (69,744 online)
Click here to Skip to main content

Tagged as

A SQL Cmdlet a Day 4 SQLPS

, 8 Apr 2014 CPOL 2.5K 2
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
Database Developer
United States United States
David retired as a Sergeant with the Cape Coral Police Department after 22 years of service. His final 10 years of duty were as a database administrator and developer in the Administrative Services Division. He began his career with the police department in 1990 in the patrol division and worked various assignments until being promoted to Sergeant in 1998. Based on his education and experience David was assigned to Administrative Services in 2002 and was responsible for database administration, software integration, and development for public safety. David’s primary focus and expertise is with SQL Server, reporting services, integration services, and analysis services, and he was recognized for his work by SQL Server Magazine as “Innovator of the Year” runner up in 2007. David is an MCITP for SQL Server 2005 and 2008 in both database administration and business intelligence and is a Microsoft Certified Trainer. He regularly posts on the MSDN SQL Server forums where he also serves as a moderator, and is a contributor at SQLCLR.net. In addition to his knowledge of SQL Server David works as a software developer using VB.net and C# and has worked extensively in SharePoint development.
Follow on   Twitter

Comments and Discussions

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