SqlPackage.exe - "System.StackOverflowException"

Question

I have a series of PowerShell scripts that are run by a TFS agent are part of a build process. These are run on several servers(Windows Server 2012 R2) that publish a series of DACPAC's to a given set of Databases. Recently I updated all the TFS build agents to the latest version(TFS 2018)

Today I noticed that one of these servers in my build process is no longer running, in particular it is failing to run "SqlPackage.exe" due to a "System.StackOverflowException" error(So very appropriate for this site).

This same issue can be reproduced by running the power shell script by hand, but only on this one server, all the others run without issue. The script looks like this:

$arguments = '/a:Publish /pr:"' + $scriptPath + $database + ".publish.xml" + '" /sf:"' + $dacPac + '" /tcs:"Data Source=' + $servername + ';Persist Security Info=True;User ID=' + $username + ';Password=' + $password + ';Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True"'

Start-Process -FilePath "C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\SqlPackage.exe" -ArgumentList $arguments -NoNewWindow -PassThru -Wait

When run by hand, the debugged the exception is:

An unhandled exception of type 'System.StackOverflowException' occurred in Microsoft.SqlServer.TransactSql.ScriptDom.dll

I'm really not sure what configuration on this server would cause this sort of issue. Resource wise the server is very powerful with large amounts of available memory, the other servers run it just fine. I've tried various versions of "SqlPackage"(13, 14) but it doesn't seem to have any effect. I've swapped out the DacPac's but that doesn't seem to work either...

Has anyone seen this issue before? What sort of server configuration can cause this sort of issue?

Update 1: hmmm, just switch to the new "14.0", "SqlPackage.exe" now I'm getting it on all my machines, I wonder if it has to do with any realated dll's such as the ones I installed in SSDT.

Actually now that I think about this, I think this issue started on the server when I first installed VS 2017, I wonder if that has any effect on "SqlPackage.exe"?

I also found this interesting post, I wonder if I can work around it this way...

Answer 1

I never figured out how to solve this for "SqlPackage", we ended up creating our own package deployer console app and calling that instead via a console app ("DacpacDeployUtility"):

static int Main(string[] args)
{
    try
    {
        string destinationServer = args[0];
        string destinationDatabase = args[1];
        string userID = args[2];
        string password = args[3];
        string publishFileFullPath = args[4];
        string dacpacFileFullPath = args[5];

        SetupRegistryQueryExecutionTimeout();
        PublishDacpacSimple(destinationServer, destinationDatabase, userID, password, publishFileFullPath, dacpacFileFullPath);

        return 0; //where 0 = success
    }
    catch (Exception ex)
    {
        Console.WriteLine("Error in Main: " + ex.Message + "\n" + ex.StackTrace);

        for (int i = 0; i < args.Length; i++)
        {
            Console.WriteLine("Value in args[" + i + "]: " + (i == 3 ? "**********" : args[i]));
        }

        Console.WriteLine("Failed to publish dacpac.");

        //Return error state
        return 1;
    }
}

private static void SetupRegistryQueryExecutionTimeout()
{
    //Fixes an annoying issue with slow sql servers: https://stackoverflow.com/a/26108419/2912011
    RegistryKey myKey = Registry.CurrentUser.OpenSubKey("Software\\Microsoft\\VisualStudio\\12.0\\SQLDB\\Database", true);
    if (myKey != null)
    {
        myKey.SetValue("QueryTimeoutSeconds", "0", RegistryValueKind.DWord);
        myKey.Close();
    }

    myKey = Registry.CurrentUser.OpenSubKey("Software\\Microsoft\\VisualStudio\\14.0\\SQLDB\\Database", true);
    if (myKey != null)
    {
        myKey.SetValue("QueryTimeoutSeconds", "0", RegistryValueKind.DWord);
        myKey.Close();
    }

    myKey = Registry.CurrentUser.OpenSubKey("Software\\Microsoft\\VisualStudio\\15.0\\SQLDB\\Database", true);
    if (myKey != null)
    {
        myKey.SetValue("QueryTimeoutSeconds", "0", RegistryValueKind.DWord);
        myKey.Close();
    }
}

private static void PublishDacpacSimple(string destinationServer, 
    string destinationDatabase, 
    string userID, 
    string password, 
    string publishFileFullPath, 
    string dacpacFileFullPath)
{
    string connectionString = BuildConnectionString(destinationServer, destinationDatabase, userID, password);

    XmlDocument xdoc = new XmlDocument();

    xdoc.Load(publishFileFullPath);

    DacServices ds = new DacServices(connectionString);
    using (DacPackage package = DacPackage.Load(dacpacFileFullPath))
    {
        var options = new DacDeployOptions();                
        
        options.CommandTimeout = 600;              

        ds.Message += (object sender, DacMessageEventArgs eventArgs) => Console.WriteLine(eventArgs.Message.Message);

        ds.Deploy(package, destinationDatabase, true, options);
    }
}

Then call that in the PowerShell script:

$DacPacDeployerPath = """" + $scriptPath + "..\..\..\DacpacDeployUtility\bin\release\EBMDacpacDeployUtility.exe"""

$Output = Start-Process -FilePath $DacPacDeployerPath -ArgumentList $arguments -NoNewWindow -PassThru -Wait