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

  • 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!

  • 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

  • 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).

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

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