Can't get databases or its objects with powershell

Question

I have Powershell installed both in my server and development pc. In my dev machine apparently have a most recent version of PowerShell installed or some its components of SQL Server 'sqlps'. My current problem is that I can't list databases as I do in my dev machine like this:

PS SQLSERVER:\> ls SQLSERVER:\\SQL\server\instance\Databases\

And I get the following:

Get-ChildItem : No se encuentra la ruta de acceso 'SQLSERVER:\SQL\serverr2\serverr2\Databases' porque no existe.
En línea: 1 Carácter: 4
+ dir <<<<  SQLSERVER:\SQL\server\instance\Databases
    + CategoryInfo          : ObjectNotFound: (SQLSERVER:\SQL\server\instance\Databases:String) [Get-ChildItem], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand

Obviously I have no way to get Child-Items like the Stored Procedures, Views, etc.

However, I'm able to list the installed instances, and also workaround this issue by listing databases with this:

DIR SQLSERVER:\\SQL\serverr2 | SELECT Databases -ExpandProperty Databases | SELECT Name,Owner,Urn

But still need to get another objects like stored procedures, views, functions,etc.

This is the info provided by the Get-Host cmdlet

Name             : ConsoleHost
Version          : 2.0
InstanceId       : c1976472-19c0-439e-a4f6-debe59a18616
UI               : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture   : es-MX
CurrentUICulture : es-ES
PrivateData      : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy
IsRunspacePushed : False
Runspace         : System.Management.Automation.Runspaces.LocalRunspace

and the @@version of SQL Server is Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)

Edit: According to $PSVersionTable.PSVersion in my server I have installed PowerShell 2.0. Also I found that I'm unable to Import-Module sqlps but for some reason I'm capable to dir as I already described (with the current problems obviously).

Answer 1

I found the answer in serverfault:

https://serverfault.com/questions/355014/sql-server-powershell-cannot-find-path-error

I have to run a script to properly load the Snapins in PowerShell:

#
# Add the SQL Server provider.
#

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Provider is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}


#
# Set mandatory variables for the SQL Server rovider
#
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

#
# Load the snapins, type data, format data
#
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml 
update-FormatData -prependpath SQLProvider.Format.ps1xml 
Pop-Location

Once loaded I can list every object in that database instance

Answer 2

Some of your question is kind of confusing to me so I am asking some clarifying questions. It looks like you tried to hide your server and instance names in some of the messages, but did not in other places.

From the PowerShell error message it shows you were going after a server named 'serverr2' and the instance name of 'serverr2'. My first thought is you have the instance name wrong. Is the instance name serverr2?

Get-ChildItem : No se encuentra la ruta de acceso 'SQLSERVER:\SQL\serverr2\serverr2\Databases' porque no existe.
En línea: 1 Carácter: 4
+ dir <<<<  SQLSERVER:\SQL\server\instance\Databases
    + CategoryInfo          : ObjectNotFound: (SQLSERVER:\SQL\server\instance\Databases:String) [Get-ChildItem], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand

---

If you CD into your server you should be able to run Get-ChildItem to get the correct instance name. For example on my machine I have 2 instances, INST1 and INST2.

C:\Users\BIGRED-7> import-module sqlps
SQLSERVER:\>
SQLSERVER:\> cd SQL\BIGRED-7-PC
SQLSERVER:\SQL\BIGRED-7-PC> ls

Instance Name
-------------
INST1
INST2

SQLSERVER:\SQL\BIGRED-7-PC>

---

The following block of code works because it enumerates every single property for every instance on your server, including the databases. The fact that this works for you tells me you have the servername correct.

DIR SQLSERVER:\\SQL\serverr2 | SELECT Databases -ExpandProperty Databases | SELECT Name,Owner,Urn