problem with EnumObjects in powershell/SMO script

• Rob 8503

  Old Hand

  Points: 320

  More actions

  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

• ssaari 68644

  SSC Journeyman

  Points: 77

  More actions

  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.