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.
TIA,
Raphael
-
If you only have SQL 2012 installed then try this:
Import-Module SQLPS
Then try the command.
-
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?
-
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
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoExtended”)
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,
Raphael
-
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.
-
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 ?
Thanks
-
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
-
none of the snapins will register so I cannot proceed from step two.
-
Me too.
Why?
-
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.
-
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.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply