Post reply

problem with EnumObjects in powershell/SMO script

  • August 17, 2017 at 2:23 pm

    #104463

    I have a code like this

    $objectTypeFlags = [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::All -bxor (
       [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::Certificate +
       [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::DatabaseRole +
       [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ExtendedStoredProcedure +
       [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::MessageType +
       [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceBroker +
       [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceContract +
       [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceQueue +
       [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceRoute +
       [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::SqlAssembly)

    #and we store them in a datatable
    $d = New-Object ("System.Data.DataTable")

    #get everything except the information schema, system views, and some other extra items

    $objectTypeFlags
    #Break

    $d = $db.EnumObjects($objectTypeFlags) |
      Where-Object {
       $_.Schema -ne "sys" -and
       $_.Name -ne "sys" -and
       $_.Schema -ne "information_schema" -and
       $_.Name -ne "information_schema" -and
       $_.Schema -notlike "db_*" -and
       $_.Name -notlike "db_*" -and
       $_.Name -notlike "sp_*diagram*" -and
       $_.Name -ne "fn_diagramobjects" -and
       $_.Name -ne "sysdiagrams" -and
       $_.Schema -ne "guest" -and
       $_.Name -ne "guest" }

    that gives me this error:

    Exception calling "EnumObjects" with "1" argument(s): "Enum objects failed for Database 'UDW_DATA_DICTIONARY'. "
    At C:\scripts\GenerateSqlScripts.ps1:156 char:1
    + $d = $db.EnumObjects($objectTypeFlags) |
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo    : NotSpecified: (:) [], MethodInvocationException
      + FullyQualifiedErrorId : FailedOperationException

    the variable $objectTypeFlags=35184237809365
    line 156 where the error occurs is 
    $d = $db.EnumObjects($objectTypeFlags) |

    also find attached the entire script that i run like this

    .\GenerateSqlScripts.ps1 udwsqldev02.usask.ca UDW_DATA_DICTIONARY C:\scripts\udwsqldev02\udw_data_dictionary

  • September 20, 2017 at 10:13 pm

    #1960304

    May I please ask, did this ever run successfully? Dumb question: Are you running as a user that has rights to all database objects?

    I would comment out all but one or two flags and uncomment one at a time to reveal what exactly breaks. It might help to add $_.name to the output to see what's executing.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply