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

Question

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

Lowell

SSC Guru

Points: 323518
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!

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

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

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 · Answer 2

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