Error scripting database objects (view/table) that have a trigger

• Lowell

  SSC Guru

  Points: 323522

  More actions

  June 3, 2016 at 7:08 am

  #314356

  I'm getting an error using the script i've added a link to below as well, in case the forum doinks up some of the code.

  this script, in a larger context, scripts all the objects out in a given database...it scripts each object out to a folder by object type and individual file per object.

  i have an issue if the table or view has a trigger on it.

  

  I thought that by making sure $ScriptOptions.Triggers = $true; was in my options, i'd be fine, but whether i toggle that to true or false, i cannot get tables or views, featuring a trigger, to script yet.

  the error is on the line $scrp.Script($URNCollection), so it's probably an overlooked option that is incompatible with my "default" options, maybe? i'm kinda stuck and need a bit of peer review.

  I KNOW it's triggers, because I've got a verbose statement that echos out exactly what object is being scripted at the moment.

  the error specifically is

  Exception calling "Script" with "1" argument(s): "Script failed for Server 'HOL-WKS-444'. "

  At C:\Users\lizaguirre\Documents\ScriptOneDatabase.ps1:60 char:24

  + $scrp.Script <<<< ($URNCollection)

  + CategoryInfo : NotSpecified: (:) [], MethodInvocationException

  + FullyQualifiedErrorId : DotNetMethodException

  ScriptOneDatabase.ps1.txt

  ###################################################################################################

  ## Function Declarations

  ###################################################################################################

  function get-databaseobjectscripts($db) {

  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

  #$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver

  $srv.Databases | Where-Object {$_.name -eq $db} | foreach-object{$_.name | Out-Null

  $database = $_

  $databaseName = $_.name

  write-verbose -Message "scripting [$databasename] in [$sqlserver]" -verbose

  $ScriptOptions = new-object ("Microsoft.SqlServer.Management.Smo.ScriptingOptions")

  $ScriptOptions.ExtendedProperties = $true # yes, we want these

  $ScriptOptions.DRIAll= $true # and all the constraints

  $ScriptOptions.ClusteredIndexes = $true;

  $ScriptOptions.NonClusteredIndexes = $true;

  $ScriptOptions.Indexes= $true # Yup, these would be nice

  $ScriptOptions.ScriptBatchTerminator = $true # this only goes to the file

  $ScriptOptions.IncludeHeaders = $true; # of course

  $ScriptOptions.ToFileOnly = $true # no need of string output as well

  $ScriptOptions.IncludeIfNotExists = $true # not necessary but makes script more versatile

  $ScriptOptions.Triggers = $true;

  $scrp=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ($Database.parent)

  $scrp.options=$ScriptOptions

  $database.EnumObjects([long]0x1FFFFFFF -band [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::all) | `

  Where-Object {('sys','information_schema') -notcontains $_.Schema} | Foreach-Object {

  $urn= [Microsoft.SqlServer.Management.Sdk.Sfc.Urn] $_.URN

  write-verbose -Message "scripting $urn.name $urn.type" -verbose

  if(('StoredProcedure','View','UserDefinedFunction') -contains $urn.type -and $urn.IsEncrypted -eq $true){

  $fullPath="$currentPath\$($urn.type)"

  $fileName = "$fullPath\$($urn.GetAttribute('Schema')-replace '[\\\/\:\.]','-')-$($urn.GetAttribute('Name') -replace '[\\\/\:\.]','-')(encrypted).sql"

  New-Item fileName -type file

  #save a file

  }

  else {

  if (('ExtendedStoredProcedure','ServiceBroker') -notcontains $urn.type)

  {

  $currentPath="$directoryname\$($ServerName -replace '[\\\/\:\.]','-' )\$($urn.GetAttribute('Name','Database') -replace '[\\\/\:\.]','-')"

  if ( $ServiceBrokerTypes -contains $urn.type)

  {$fullPath="$currentPath\ServiceBroker\$($urn.type)"}

  else

  {$fullPath="$currentPath\$($urn.type)"}

  if (!(Test-Path -path $fullPath ))

  {

  Try { New-Item $fullPath -type directory | out-null }

  Catch [system.exception]{

  Write-Error "error while creating '$fullPath' "

  return

  }

  }

  $scrp.options.FileName = "$fullPath\$($urn.GetAttribute('Schema')-replace '[\\\/\:\.]','-')-$($urn.GetAttribute('Name') -replace '[\\\/\:\.]','-').sql"

  $UrnCollection = new-object ('Microsoft.SqlServer.Management.Smo.urnCollection')

  $URNCollection.add($urn)

  write-verbose "writing script to $($scrp.options.FileName)"

  [highlight="#ffff11"]$scrp.Script($URNCollection)[/highlight]

  }

  }

  }

  }

  }

  ###################################################################################################

  ## Execution Space is Below

  ###################################################################################################

  ##$servers = get-content C:\Data\PowerShell\computers.txt

  $servers = "HOL-WKS-444"

  $databaseToScript = "DBA_Utilities"

  $directoryname = "C:\Data\PowerShell\"

  #make sure our prime directory exists

  if (!(Test-Path -path $directoryname)) {

  New-Item $directoryname -type directory

  }

  #make sure our sub directory exists

  if ($sqlserver.Trim() -ne "") {

  if($sqlserver.IndexOf("\") -gt 0) {

  $directoryname = "C:\Data\PowerShell" + "\" + $sqlserver.Replace("\","(") + ")\"

  $position =$sqlserver.IndexOf("\")

  $server = $sqlserver.SubString(0,$position)

  $serverfilename = $sqlserver.Replace("\","(") + ")"

  }

  else {

  $directoryname = "C:\Data\PowerShell" + "\" + $sqlserver + "\"

  $server = $sqlserver

  $serverfilename = $server

  }

  if (!(Test-Path -path $directoryname)) {

  New-Item $directoryname -type directory

  }

  }

  get-databaseobjectscripts $databaseToScript

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  June 3, 2016 at 11:51 am

  #1883172

  Hi Lowell, just tried your script on a 2014 instance with both $ScriptOptions.Triggers = $true & $ScriptOptions.Triggers = $false; both worked as expected.

  The trigger scripts are included in the create table scripts & for some reason are strings passed to sp_executesql calls.

  Had to make a couple of small changes for the script to run:

  the line if $servers = "servername" to $sqlserver = "servername" in the execution space

  and uncomment $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver at the top of the function

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  June 3, 2016 at 12:01 pm

  #1883175

  Worked with triggers $true on a 2008 & 2012 instance too.

  Maybe check PS & SMO versions?

  I don't have any triggers on views though, will give that a test. edit: yep, all good there too (2014).