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:
Please suggest the solution of this timeout error, if anyone has found it.
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: