0

We have created powershell script to deploy our dacpac packages. This script uses Microsoft.SqlServer.Dac.dll library. When I am deploying my dacpac using this powershell script, I am getting following Bug:

.Net SqlClient Data Provider: Msg -2, Level 11, State 0, Line 0 Execution Timeout 
 Expired.  The timeout period elapsed prior to completion of the operation or the server 
 is not responding. An error occurred while the batch was being executed.

I checked the deployment options for above dll in msdn documentation and added following:

$deployoptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
$deployoptions.CommandTimeout =60000
$deployoptions.LongRunningCommandTimeout=0

CommandTimeout deployment option has default value as 0 and i added 60000, but still no effect. whereas LongRunningCommandTimeout deployment option is not getting recognized as valid option and getting following error:

The property 'LongRunningCommandTimeout' cannot be found on this object. Verify that the 
property exists and can be set.

My other findings:

  1. In the latest version of sqlpackage.exe (i.e. 162.0.52.1) I did not get the timeout error while deploying the same dacpac package, but I got the error when I used lower versions (lower than 162.0.52.1) of SQLPackage.exe.
  2. Using powershell script to deploy the dacpac package, I am getting timeout error for all versions of Microsoft.SqlServer.Dac.dll, which was disheartening for me. Because I was expecting that the latest version (i.e. 162.0.52.1) of this dll should have the fix, just like SQLPackage.exe.

Please suggest the solution of this timeout error, if anyone has found it.

Murali Dhar Darshan
  • 285
  • 1
  • 17
  • Check the SQL Server logs using SQL Server Management Studio in the explorer under Management. – jdweng Jun 08 '23 at 12:32
  • jdweng, I did not find anything useful there. In SQL Server Logs, nothing is captured related to dacpac deployment. Under Windows NT>Application, there are just info details about the execution. – Murali Dhar Darshan Jun 08 '23 at 13:00
  • There should off been a login in the log file at time you ran code. If not, then you are not connecting to the database. I would look at the connection string. This is from 2019 and is old so it may not be applicable : https://developercommunity.visualstudio.com/t/azure-sql-dacpac-deployment-using-managed-service/665537?force_isolation=true – jdweng Jun 08 '23 at 13:56
  • The same powershell script has been successfully deploying smaller dacpac packages for ther databases in same instance. But for this specific one, there are lot of post deployment scripts, and thats where it gives timeout error. – Murali Dhar Darshan Jun 08 '23 at 15:28
  • See : https://www.sqlsmarts.com/azure-devops-sqlpackage-deployment-timeouts/?force_isolation=true – jdweng Jun 08 '23 at 16:49
  • I looked into above URL, which mainly talks about the deploymentoption LongRunningCommandTimeout and that it should set to 0. I tried to set but got above mentioned error message. But new info is that error was there when I tried it with powershell-ise, but when I tried it with powershell, then it worked. – Murali Dhar Darshan Jun 13 '23 at 09:09

1 Answers1

0

Microsoft.SqlServer.Dac.dll with version 16.0.52.1 has deployment option LongRunningCommandTimeout which can be used to resolve timeout error for dacpac deployemnt.

However, when I was trying to utilize this dll (with version 16.0.52.1), I did witness weird behavior of powershell:

Problem with powershell: Open powershell command window and Execute below code to list out all deployment options provided by Microsoft.SqlServer.Dac.dll :

#The version of Microsoft.SqlServer.Dac.dll is 15.0.4384.2 
Add-Type -path "C:\Program Files\Microsoft SQL Server\150\DAC\bin\Microsoft.SqlServer.Dac.dll"               
$deployoptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
$deployoptions

Output: it does not contain LongRunningCommandTimeout    and 
DatabaseLockTimeout deployment options




#The version of Microsoft.SqlServer.Dac.dll is 16.0.52.1
Add-Type -path "C:\Program Files\Microsoft SQL Server\160\DAC\bin\Microsoft.SqlServer.Dac.dll"               
$deployoptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
$deployoptions

Output: it still does NOT contain LongRunningCommandTimeout    and 
DatabaseLockTimeout deployment options, even if I imported 
16.0.52.1 version of dll.

So, PowerShell keep showing same deployment options of the first imported dll and if we import another version of it thereafter, we do not get refreshed set of deployment options. So, if we pretend to get updated deployment options by importing different versions of this dll, then its a mistake.

Solution: We can refresh the powershell session using command powershell and thats how we get correct deployment option of imported Microsoft.SqlServer.Dac.dll

Add-Type -path "C:\Program Files\Microsoft SQL Server\150\DAC\bin\Microsoft.SqlServer.Dac.dll"               
$deployoptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
$deployoptions

Output: it does not contain LongRunningCommandTimeout    and 
DatabaseLockTimeout deployment options



Powershell
 
Output: Powershell session is refreshed.




Add-Type -path "C:\Program Files\Microsoft SQL Server\160\DAC\bin\Microsoft.SqlServer.Dac.dll"               
$deployoptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
$deployoptions
 
Output: Now it contains LongRunningCommandTimeout    and 
DatabaseLockTimeout deployment options

So the conclusion is:

  1. We need to use version 160.0.52.1 of Microsoft.SqlServer.Dac.dll to get additional deployment option LongRunningCommandTimeout.
  2. We need to refresh powershell session and then import Microsoft.SqlServer.Dac.dll to get actual deployment options related to imported dll.
Murali Dhar Darshan
  • 285
  • 1
  • 17