英文:
DACPAC Deployment through powershell script is failing with timeout error
问题
我们已经创建了PowerShell脚本来部署我们的dacpac包。此脚本使用Microsoft.SqlServer.Dac.dll库。当我使用这个PowerShell脚本部署我的dacpac时，我遇到了以下错误：
.Net SqlClient数据提供程序：Msg -2，级别11，状态0，行0 执行超时已过期。操作完成之前已经过去了超时时间，或者服务器未响应。在批处理执行期间发生错误。
我在msdn文档中检查了上述dll的部署选项，并添加了以下内容：
$deployoptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
$deployoptions.CommandTimeout =60000
$deployoptions.LongRunningCommandTimeout=0
CommandTimeout部署选项的默认值为0，我添加了60000，但仍然没有效果。而LongRunningCommandTimeout部署选项无法被识别为有效选项，出现以下错误：
找不到属性'LongRunningCommandTimeout'在此对象上。请验证该属性是否存在并且可以设置。
我的其他发现：
- 在最新版本的sqlpackage.exe（即162.0.52.1）中，部署相同的dacpac包时，我没有遇到超时错误，但当我使用较低版本（低于162.0.52.1）的SQLPackage.exe时，我遇到了错误。
- 使用PowerShell脚本部署dacpac包，我对Microsoft.SqlServer.Dac.dll的所有版本都遇到了超时错误，这让我感到沮丧。因为我期望这个dll的最新版本（即162.0.52.1）应该像SQLPackage.exe一样修复了这个问题。
如果有人找到了超时错误的解决方案，请提出建议。
英文:
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:
- 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.
- 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.
答案1
得分: 0
Microsoft.SqlServer.Dac.dll 版本 16.0.52.1 具有部署选项 LongRunningCommandTimeout，可用于解决 dacpac 部署的超时错误。
然而，当我尝试使用此 dll（版本 16.0.52.1）时，我确实注意到 PowerShell 的奇怪行为：
PowerShell 的问题：
打开 PowerShell 命令窗口，执行以下代码以列出 Microsoft.SqlServer.Dac.dll 提供的所有部署选项：
# Microsoft.SqlServer.Dac.dll 的版本为 15.0.4384.2
Add-Type -path "C:\Program Files\Microsoft SQL Server0\DAC\bin\Microsoft.SqlServer.Dac.dll"
$deployoptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
$deployoptions
输出：不包含 LongRunningCommandTimeout 和 DatabaseLockTimeout 部署选项
# Microsoft.SqlServer.Dac.dll 的版本为 16.0.52.1
Add-Type -path "C:\Program Files\Microsoft SQL Server0\DAC\bin\Microsoft.SqlServer.Dac.dll"
$deployoptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
$deployoptions
输出：仍然不包含 LongRunningCommandTimeout 和 DatabaseLockTimeout 部署选项，即使我导入了 16.0.52.1 版本的 dll。
因此，PowerShell 一直显示首次导入的 dll 的相同部署选项，如果此后导入另一个版本的 dll，我们不会获得刷新后的部署选项。因此，如果我们假装通过导入不同版本的 dll 来获取更新的部署选项，那是一个错误。
解决方案： 我们可以使用命令 "powershell" 刷新 PowerShell 会话，这样我们就可以获取导入的 Microsoft.SqlServer.Dac.dll 的正确部署选项。
Add-Type -path "C:\Program Files\Microsoft SQL Server0\DAC\bin\Microsoft.SqlServer.Dac.dll"
$deployoptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
$deployoptions
输出：不包含 LongRunningCommandTimeout 和 DatabaseLockTimeout 部署选项
Powershell
输出：PowerShell 会话已刷新。
Add-Type -path "C:\Program Files\Microsoft SQL Server0\DAC\bin\Microsoft.SqlServer.Dac.dll"
$deployoptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
$deployoptions
输出：现在包含 LongRunningCommandTimeout 和 DatabaseLockTimeout 部署选项
因此，结论是：
- 我们需要使用 Microsoft.SqlServer.Dac.dll 版本 160.0.52.1 才能获得额外的部署选项 LongRunningCommandTimeout。
- 我们需要刷新 PowerShell 会话，然后导入 Microsoft.SqlServer.Dac.dll，以获取与导入的 dll 相关的实际部署选项。
英文:
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 Server0\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 Server0\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 Server0\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 Server0\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:
- We need to use version 160.0.52.1 of Microsoft.SqlServer.Dac.dll to get additional deployment option LongRunningCommandTimeout.
- We need to refresh powershell session and then import Microsoft.SqlServer.Dac.dll to get actual deployment options related to imported dll.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论