使用 Azure 发布管道在存储过程中获取"无效的对象名称"

Question

我正在尝试使用Azure发布管道将数据库更改部署到Windows服务器。我正在使用包含表数据的DACPAC文件。

这是生成我在构建管道中运行的DACPAC的代码:

sqlpackage `
/Action:Extract `
/SourceConnectionString:"Data Source=localhost;Initial Catalog=Shipping;Integrated Security=False;Persist Security Info=True;User ID=username;Password=password" `
/TargetFile:"$PublishDir/Shipping.dacpac" `
/p:IgnoreUserLoginMappings=True `
/p:VerifyExtraction=False `
/p:ExtractAllTableData=True

在发布管道中，我有一个部署组作业，它包含以下步骤:

<

1. 禁用触发器/gh><
2. 禁用约束/gh>
3. 部署DACPAC
4. 启用触发
<
5. 启用约束/gh>

我将SQL Server Deployment任务用于部署DACPAC步骤。每次到达该步骤时，都会抛出以下错误:

*** Could not deploy package.
Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 208, Level 16, State 1, Procedure vGetTransportPackages, Line 31 Invalid object name 'FreightDataStore.TP.TransportPackage'.
Error SQL72045: Script execution error.  The executed script:
CREATE VIEW [fds].[vGetTransportPackages] AS WITH PACKAGES AS (SELECT DISTINCT tp.transportPackageID AS TransportPackageID, tp.tpID AS CarTransportPackageNumber, tp.airWayBillNo AS AirwayBillNumber, tp.pod AS DestinationPort,
(SELECT TOP 1 tl.LocationName FROM fds.TransportLocation AS tl WHERE  tl.UnLocCode = tp.pod) AS DestinationPortName, pod.countryCode AS DestinatonPortCountry, (SELECT TOP 1 c.CountryName FROM   ref.Countries AS c WHERE  c.CountryCode = pod.countryCode) AS DestinationPortCountryName, tp.poe AS OriginPort, (SELECT TOP 1 tl.LocationName FROM   fds.TransportLocation AS tl WHERE  tl.UnLocCode = tp.poe

我对数据库部署比较陌生。我需要更改生成DACPAC的方式吗?

Answer 1

我找到了问题所在。我没有在Shipping数据库所依赖的服务器上安装其他数据库。一旦我安装了FreightDataStore和其他几个数据库，我就能够成功地部署DACPAC。