How can we help you today? How can we help you today?

NoErrorHandling line option not being utilized

I am having issues with passing the NoErrorHandling option to a SQL Compare (Version 14.2.9.15508 Professional) script I am writing in PowerShell. I have both the NoErrorHandling and NoTransactions options included, and while there is no rollback occurring after the error occurs, it does stop the process and prevent any additional deployment from occurring when synchronizing to the target database.

The specific error returned is
"Synonym '' refers to an invalid object.
System.Data.SqlClient"

$MyParameters = @{
    'Server1' = '';
    'Database1' = '';
    'Server2' = '';
    'Database2' = '';
    'options' = 'cs,nt,dp2k,nc,isoa,icn,iscn,idsn,ito,ie,ifg,if,inwn,infr,ipi,ip,irpt,iq,isb,ist,itst,iup,iu,iw,iweo,iwn,neh,ndl'; # Panther DBA Defaults
        # cs - CaseSensitiveObjectDefinition
        # nt - NoTransactions
        # dp2k - DecryptPost2KEncryptedObjects
        # nc - DoNotOutputCommentHeader
        # isoa - IgnoreSchemaObjectAuthorization
        # icn - IgnoreConstraintNames
        # iscn - IgnoreSystemNamedConstraintNames
        # idsn - IgnoreDatabaseAndServerName
        # ito - IgnoreTriggerOrder
        # ie - IgnoreExtendedProperties
        # ifg - IgnoreFileGroups
        # if - IgnoreFillFactor
        # inwn - IgnoreNocheckAndWithNocheck
        # infr - IgnoreNotForReplication
        # ipi - IgnorePerformanceIndexes
        # ip - IgnorePermissions
        # irpt - IgnoreReplicationTriggers
        # iq - IgnoreQuotedIdentifiersAndAnsiNullSettings
        # isb - IgnoreSquareBrackets
        # itst - IgnoretSQLt
        # ist - IgnoreStatistics
        # iup - IgnoreUserProperties
        # iu - IgnoreUsersPermissionsAndRoleMemberships
        # iw - IgnoreWhiteSpace
        # iweo - IgnoreWithElementOrder
        # iwn - IgnoreWithNoCheck 
        # neh - NoErrorHandling
        # ndl - NoDeploymentLogging
    'filter' = ''; 
    'ScriptFile' = ""; #write to script file
    'force' = $null; #force overwrite
    'sync' = $null;
    'Include' = 'Identical';
}

# Helper function - translate parameters to XML Format
Function SaveParamsAsSQLCompareXML([hashtable]$TheHashTableParameters,[string]$WhereToStoreIt)
{
    $xmlDoc = [System.Xml.XmlDocument]'<?xml version="1.0"?>';
    $commandline = $xmlDoc.AppendChild($xmlDoc.CreateElement('commandline'));
    $TheHashTableParameters.GetEnumerator() | ForEach-Object {
        $Element = $commandline.AppendChild($xmlDoc.CreateElement($_.Name));
        if ($null -ne $_.Value) # if it is a switch with a parameter
        {
            $Element.AppendChild($xmlDoc.CreateTextNode($_.Value));
        }
    }
    $xmlDoc.Save("$WhereToStoreIt"); #save it to the user temp area
}
SaveParamsAsSQLCompareXML $MyParameters "C:\Dev\Repos\DBATools\SQLCompareScripts\ParamFiles\Paramfile.xml";
.\SQLCompare.exe /Argfile:"C:\Dev\Repos\DBATools\SQLCompareScripts\ParamFiles\Paramfile.xml";
Stevie
0

Comments

4 comments

Sort by
  • Tianjiao_Li
    @Stevie

    If NoTransactions option isn't specified and the deployment script fails, the script is rolled back to the start of the failed transaction. If this option is specified, the script isn't rolled back as you've seen. This can be useful for detection of errors within a script.

    From the error you got, it looks like there are issues with your Synonyms and you might need to make sure they are not referring to any invalid object.

    Tianjiao_Li
    0
  • Stevie
    @Tianjiao_Li
    Thank you for your response. However, the 'NoTransactions' option has been specified in the parameters. While the operation is not being rolled back, it is not continuing after the error in the script has been reached, which is what I understood the 'NoErrorHandling' option was meant to work around.

    The script in question is comparing the schema at client instances which may very well have errors in their objects outside of DBA control.
    Stevie
    0
  • Tianjiao_Li
    @Stevie

    If you deploy using SQL Compare, the deployment is always stopped when an error is encountered. https://documentation.red-gate.com/sc/setting-up-the-comparison/setting-project-options

    You could export the deployment script and run it in SSMS in your case.
    Tianjiao_Li
    0
  • Phil_Fact0r
    (I'm only a FoRG, not a Redgate employee.) The Error handling system in a SQL Compare script is there to prevent the script from carrying on after an error. Generally, you really shouldn't want it to carry on. The option removes this system in the scripts that catches those errors that do not otherwise automatically stop the batch or the entire script from being executed. The idea is to ensure that even an apparently trivial error rolls the script back. Unfortunately, some SQL Server errors can't be caught this way. The only way to prevent more serious errors, up to level 20 stopping the script is  to use a try...catch  block for every block or batch, but even then, you won't catch all errors, and I doubt whether it would have caught the one you had. . An object name resolution error is not caught by the TRY...CATCH construct.
    Phil_Fact0r
    0

Add comment

Please sign in to leave a comment.