patternsqlMinor
Can't get databases or its objects with powershell
Viewed 0 times
candatabasesobjectswithpowershellitsget
Problem
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:
And I get the following:
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:
But still need to get another objects like stored procedures, views, functions,etc.
This is the info provided by the Get-Host cmdlet
and the @@version of SQL Server is
Edit: According to
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.GetChildItemCommandObviously 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,UrnBut 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.LocalRunspaceand 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 obvSolution
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:
Once loaded I can list every object in that database instance
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-LocationOnce loaded I can list every object in that database instance
Code Snippets
#
# 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-LocationContext
StackExchange Database Administrators Q#128631, answer score: 4
Revisions (0)
No revisions yet.