PowerShell on SQL Server Problem

Other Tech Topics
1

Hi all,

I'm using PowerShell ISE to perform some maintenance tasks on SQL Server 2014.

This script works fine:

Import-Module "SQLPS" Get-ChildItem -Path SQLSERVER:\SQL\MYSERVERNAME\Default\Databases\MYDBNAME

It gives me a breakdown of the database items, as below:

ApplicationRoles
Assemblies
AsymmetricKeys
Certificates
ColumnEncryptionKeys
ColumnMasterKeys
DatabaseAuditSpecifications
DatabaseScopedCredentials
Defaults
ExtendedProperties
ExtendedStoredProcedures
ExternalDataSources
ExternalFileFormats
Federations
FileGroups
FullTextCatalogs
FullTextStopLists
LogFiles
PartitionFunctions
PartitionSchemes
PlanGuides
Roles
Rules
Schemas
SearchPropertyLists
SecurityPolicies
Sequences
ServiceBroker
StoredProcedures
SymmetricKeys
Synonyms
Tables
Triggers
UserDefinedAggregates
UserDefinedDataTypes
UserDefinedFunctions
UserDefinedTableTypes
UserDefinedTypes
Users
Views
XmlSchemaCollections

My Problem
I can drill into any of the above items and get the expected results (i.e. Users, Tables, Views) except for StoredProcedures - where I get an error.

This is what I run (the only difference from the first script is I'm adding \StoredProcedures\ to the end).

Import-Module "SQLPS" Get-ChildItem -Path SQLSERVER:\SQL\MYSERVERNAME\Default\Databases\MYDBNAME\StoredProcedures\

The error I get is:

Get-ChildItem : Cannot find path 'SQLSERVER:\SQL\MYSERVERNAME\Default\Databases\MYDBNAME\StoredProcedures' because it does not exist.
At line:1 char:1
Get-ChildItem -Path SQLSERVER:\SQL\MYSERVERNAME\Default\Databases\MYDBNAME ...

    + CategoryInfo          : ObjectNotFound: (SQLSERVER:\SQL\...oredProcedures\:String) [Get-ChildItem], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand

Any help much appreciated :grinning:

2

Mistyped the server name and/or database name?
permission issues on database that is failing?

3

Spelling is all fine.
Shouldn't be any permission problems (I'm sysadmin) and I can pull up the details of everthing else on the database, such as MYDBNAME\Tables, MYDBNAME\Views, MYDBNAME\Users.

I just can't get into MYDBNAME\StoredProcedures

4

can you actually manually see stored procedures on the sql server and database?

5

Yes.

6

Hmm, still experiencing the same thing until now?

7

Mine has suddenly started working now too. I've recently had a rebuild on my PC, so had to reinstall SSMS 2016, but now all appears ok. Odd.