6

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).

  • 1
    What does "unable to Import-Module" mean? Do you get an error message? If so, which one? What does Get-Module show? – Oliver Rahner Feb 11 '16 at 16:02
4

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

1

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
  • Sorry for confusing you. I intentionally changed the name "serverr2" to server\instance to have a "generic name" for the question, I forgot to change it in some places. Anyway I get those following messages with the correct server instance name. – Mr_LinDowsMac Feb 9 '16 at 1:08
  • When I cd into my server, and then list with DIR or LS, I don't get that Instances table. I get a large table with a lot of properties. I clarify that I'm not cd'ing into any instance. – Mr_LinDowsMac Feb 9 '16 at 1:20
  • All of the properties are there regardless of whether you see them or not. Are you using the default instance? If so your instance name should be DEFAULT. – cjsommer Feb 9 '16 at 15:11
  • So instead of ls SQLSERVER:\SQL\serverr2\serverr2\Databases try this instead ls SQLSERVER:\SQL\serverr2\default\Databases – cjsommer Feb 9 '16 at 15:13
  • Nope, didn't work. Can't open connection and asks me to provide a correct instance name. – Mr_LinDowsMac Feb 9 '16 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.