Stairway to SQL PowerShell Level 2: SQL PowerShell Setup and Config

  • That did not do it. It's a VM running under Parallels and I have complete control over it. I added myself to the administrators local group, and ran the powershell console as administrator. Still getting the same error message.


    It works perfectly via SMO. Is it something to do with SQLServer 2012? I 2012 installed.



  • If you only have SQL 2012 installed then try this:

    Import-Module SQLPS

    Then try the command.

    Ben Miller
    Microsoft Certified Master: SQL Server, SQL MVP
    @DBAduck -

  • Still the same error/warning messages. No solution.

    Additionally, when running import-module SQLPS, I get the following: "Object reference not set to an instance of an object.

    WARNING: Some imported command names include unapproved verbs which might make them less discoverable. Use the Verbose

    parameter for more detail or type Get-Verb to see the list of approved verbs."

    The warning part of this seems to be OK. Searching online I found that "some verbs used in the names of two SQL Server cmdlets (Encode-Sqlname and Decode-Sqlname) do not match the approved verbs for Windows PowerShell 2.0. This has no effect on their operation, but Windows PowerShell raises a warning when the sqlps module is imported to a session." The part that concerns me is the "Object reference not set to an instance of an object"

    Any idea? TIA, Raphael

  • Something just does not seem right. What is the OS?

    I am assuming that you can connect via management studio. But if you close PowerShell and open it fresh with Run As Administrator and then import-module sqlps it should take you to SQLSERVER:>. Does it?

    Ben Miller
    Microsoft Certified Master: SQL Server, SQL MVP
    @DBAduck -

  • OK. You found what the problem was. I had the $profile already set up with all the commands you presented on your articles. Somehow, because I had already loaded the other libraries it was interfering with this one. When I cleared the profile and loaded only SQLPS, it worked. THANK YOU.

    Now, what should I maintain in the profile and what should I remove?

    Here's what I had:

    "$global:CurrentUser = [System.Security.Principal.WindowsIdentity]::GetCurrent()

    function prompt


    $wintitle = $CurrentUser.Name + " " + $Host.Name + " " + $Host.Version

    $host.ui.rawui.WindowTitle = $wintitle

    Write-Host ("PS " + $(get-location) +">") -nonewline -foregroundcolor Magenta

    return " "


    Add-PSSnapin SqlServerCmdletSnapin100

    Add-PSSnapin SqlServerProviderSnapin100



    import-module adolib

    import-module SQLServer

    import-module Agent

    import-module Repl

    import-module SSIS

    import-module SQLParser

    import-module Showmbrs

    import-module SQLMaint

    #import-module SQLProfiler

    import-module PerfCounters

    import-module SQLPS -disablenamechecking


    What do I keep in the profile and what do I remove?

    Thank you,


  • Well, you should be able to have both the Cmdlet100 and the Provider100 loaded with the SQLPS.

    But if you loaded 2012 on your machine, you would have what you have in the old ones. But I would keep the Cmdlet100 one and the SQLPS one, but I don't think you need to load the Provider100.

    I will check this out and let you know what I find, but if you remove the Provider100 one you should not have any conflicts.

    Ben Miller
    Microsoft Certified Master: SQL Server, SQL MVP
    @DBAduck -

  • No conflicts by removing the Add-PSSnapin SqlServerProviderSnapin100 one. All good. Thanks much! Waiting for Level 4. 🙂

  • I tried to:

    notepad $profile

    but got an error: "The system cannot find the path specified."

    What is the whole path ?


  • But you should get a dialog saying

    Cannot find the

    C:\Users\(youruserid)\Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1 file.

    Do you want to create a new file?

    Instead what are you getting?

  • The error is most likely indicating that you do not have a WindowsPowerShell directory in your Documents folder.

    You can solve that by using this:

    New-Item -Path (Split-Path $profile) -ItemType Directory -Force

    Then when you have that created then you can do the notepad $profile

    You can also do something like this for the file:

    New-Item -Path $profile -ItemType File -Force

    Then do the notepad $profile

    Ben Miller
    Microsoft Certified Master: SQL Server, SQL MVP
    @DBAduck -

  • none of the snapins will register so I cannot proceed from step two.

  • Me too.


  • Snapins are only available if you install SQL 2008 feature packs or tools. They are not installed with 2012 or 2014. They are replaced by the sqlps module.

    Ben Miller
    Microsoft Certified Master: SQL Server, SQL MVP
    @DBAduck -

  • I'm not feeling PowerShell. I'm running SQL Server 2014 so I have to run Import-Module "sqlps" in order to get all the features and methods available with the Snap-Ins in 2008. I've added this little bit of code to my profile and a PowerShell console always takes forever to load. It may have "awesome new features" that I "can't live without" but if I can run to the moon and back by the time the thing loads, it's not useful to me...

  • There is a new version in the new module for 2016. The old sqlps loads slowly because it does something that it doesn't need to do. So hang in there it now loads in less than a second. You should be able to get the SQL 2016 Feature Pack from the web and it may have the new one.

    Ben Miller
    Microsoft Certified Master: SQL Server, SQL MVP
    @DBAduck -

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply