Cannot find path SQLSERVER:\SQL - Set-Location SQLSERVER:\SQL

PowerShell Help
1

Hi, I have a win 2012 box with SQL 2K12. After investigating MSDN resources around “Navigate SQL Server PowerShell Paths”, I registered the provider in my C:\documents\user.…\Microsoft.PowerShell_profile.ps1 as follows

Register-PSSessionConfiguration -Force -AccessMode Remote -Name SQLSupport -StartupScript C:\Users\salam\Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1
to make it available in all sessions.

Issuing the command
[blockquote]Set-Location SQLSERVER:\SQL[/blockquote]
generates the following error

Set-Location : Cannot find drive. A drive with the name 'SQLSERVER' does not exist.
At line:1 char:1
+ Set-Location "SQLSERVER:\SQL"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: [SQLSERVER:String] [Set-Location], DriveNotFoundException
    + FullyQualifiedErrorId : DriveNotFound,Microsoft.PowerShell.Commands.SetLocationCommand

but the following one succeeds without any issue [and switches the command line to PS SQLSERVER:>]

[blockquote]invoke-sqlcmd -query “sp_databases” -database master -serverinstance localhost | format-table
[/blockquote]
So my question is what is the right syntax to navigate the sql server tree and how can we CD between the sql server and going back and forth to my C:\documnts\user from PS SQLSERVER:>
Thanks

2

OK. I may not be following what you’re doing.

Register-PSSessionConfiguration has nothing to do with providers. It creates a new Remoting endpoint, in this case named SQLSupport. Anything configured in that endpoint will only be effective if you’re connected to that endpoint, using either Enter-PSSession or Invoke-Command. I don’t see you using either of those commands.

The SQLSERVER PSProvider is not present in the shell by default. In order for it to work, the SQL Server snapping or module that contains the PSProvider must be loaded. This can be done by running Import-Module and providing the name of the module, or by first running a command within that module, which can implicitly load the module for you. If you run Get-PSProvider and don’t see the SQL provider, then it isn’t loaded. If you run Get-PSDrive and don’t see the SQLSERVER: drive, then you can’t use it.

Invoke-SqlCmd is a command, which is contained within he SQL Server module. Running the command implicitly loads the module. Attempting to access a PSProvider will not implicitly load the corresponding module. After running Invoke-SqlCmd, I bet your SQLSERVER: drive would be present and usable, because running the command implicitly loaded the module that also contains the PSProvider.

3

Thanks Don, nice explanation around Register-PSSessionConfiguration which I was not aware of. In fact, I visited the url

where I read “create a new Remoting Configuration to load your SQL Items into your profile…”.

Anyway, here is the story behind all this mess. I am implementing a POC for SQL Database continuous integration using flywayDB and Jenkins where I need to use a Jenkins powershell job to delete and create a database on a remote server as follows (this was my starting point as I am new to PS remoting)

[blockquote]$password = ConvertTo-SecureString “mypassword” -AsPlainText -Force
$cred= New-Object System.Management.Automation.PSCredential [“mydomain\myuser”, $password ]
Enter-PSSession -ComputerName SQL2K12 -Credential $cred
Push-Location
Import-Module sqlps -DisableNameChecking
$databaseName = “HRDB”
$serverPath = “SQLSERVER:\SQL\SQL2K12\default”[/blockquote]
…
…
The statement Import-Module was failing when it was run from the jenkins platform but same code worked fine from ISE or the PS command prompt on the same machine. Not knowing well the “Remoting with powershell correctly”, I thought this forcing of loadind sqlps in the profile for each session will help which did not and by accident I came across this Set-location issue which you are absolutly correct.

To make a long story short, after posting this thread, I browsed MSDN and read about

[blockquote]New-PSSession and using invoke-command -session $session -scriptblock {…

[/blockquote]So I changed my code as follows which is working like a charm

[blockquote]$session = New-PSSession sql2k12 -Credential $cred
invoke-command -session $session -scriptblock { Set-ExecutionPolicy -ExecutionPolicy Unrestricted ; $databaseName = “payroll”; $serverPath = “SQLSERVER:\SQL\SQL2K12\default”; invoke-sqlcmd -query “sp_databases” -database master -serverinstance localhost | format-table; $databasePath = Join-Path $serverPath “Databases$databaseName”}
[/blockquote]
then issung all other commands using invoke-command

[blockquote]invoke-command -session $session -scriptblock {

if [Test-Path $databasePath]
{
Invoke-SqlCmd -ServerInstance SQL2K12 “USE [master]; ALTER DATABASE [$databaseName] set SINGLE_USER WITH ROLLBACK IMMEDIATE ; DROP DATABASE [$databaseName]”

}
}
[/blockquote]
Then
[blockquote]invoke-command -session $session -scriptblock { Invoke-SqlCmd -ServerInstance SQL2K12 “CREATE DATABASE [$databaseName]” }
[/blockquote]
So it seems to me that even when we have a handle for a remote session, we should always use "invoke-command " in order to make the commands run remotely, do you agree? Once you confirm, I will write a blog on this .
2 questions now, in any case when I get into the sql drive on the command line, how do I revert back to my C: drive.
2nd, If session is established using either "Enter-PSSession ", or “New-PSSession”, shouldn’t we be able to issue commands without any invoke stuff

Thanks again

4

[blockquote]So it seems to me that even when we have a handle for a remote session, we should always use "invoke-command " in order to make the commands run remotely, do you agree? Once you confirm, I will write a blog on this .
2 questions now, in any case when I get into the sql drive on the command line, how do I revert back to my C: drive.
2nd, If session is established using either "Enter-PSSession ", or “New-PSSession”, shouldn’t we be able to issue commands without any invoke stuff
[/blockquote]

One you have a reference to a session, yes, you would use Invoke-Command to run commands in it, passing the session reference to the -Session parameter. Yes. Keep in mind you can have hundreds of open sessions at once; Invoke-Command has to know which one you want it to use, and it can accept multiples as well.

To get back to your C: drive:

C:

Just like in Cmd.exe. Or, use Set-Location.

If you establish a session by using New-PSSession, you get a reference to it. You would use Invoke-Command, passing the session object, to run commands in it.

Enter-PSSession can either start a new session and enter it (when you use -ComputerName) or it can enter an existing session (when you use -Session). Same as Invoke-Command. Enter-PSSession is a bit like a “Using” in C#. Once you’ve entered the session, any commands you run are implicitly sent via Invoke-Command. So it saves you some typing if you plan to run a lot of commands.

You might consider going through “Secrets of PowerShell Remoting” or the Remoting chapter in “Learn Windows PowerShell in a Month of Lunches.” Part of what I think you’re struggling with is that you’re piecing together your information, sometimes from poor sources, and you’re just not getting a good fundamental look at what Remoting is.

5

Agree, and will look into those resources, TIME issue :slight_smile:
So you say
[blockquote]any commands you run are implicitly sent via Invoke-Command. So it saves you some typing [/blockquote]
which was not the case, after doing
[blockquote]Enter-PSSession [/blockquote]
always
[blockquote]Import-Module sqlps -DisableNameChecking[/blockquote]
failed, it only worked as I indicated with the Invoke-Command

Would it be because I am not setting the "Set-ExecutionPolicy -ExecutionPolicy Unrestricted " or am I missing something in my code?

So many thanks for your clear response

6

Fantastic remoting mini-guide “Secrets of PowerShell Remoting”, I went through all pages. In this case, I understood why my initial script file in jenkins that usied the Enter-PSSession could not run the Import-Module

I have just noticed that I have followed a course on pluralsight 2 years back “PowerShell v3/v4 Essentials for IT Admins Part 3”, how funny is this world, I watched all topics except the 1st one “PowerShell Remoting”