👁 Image
Microsoft.SqlServer.Dacpacs.Msdb 170.0.3

Microsoft.SqlServer.Dacpacs.Msdb contains a .dacpac file for the SQL Server msdb objects. Referencing this package in a SQL database project allows you to build and validate database projects with msdb object references that target SQL Server.

📗 SQL projects database DevOps documentation

🧑‍💻 Get help and support

Usage

The reference to this package can be added to your SQL database project (.sqlproj) as follows:

...
 <ItemGroup>
 <PackageReference Include="Microsoft.SqlServer.Dacpacs.Msdb" Version="170.0.3" />
 </ItemGroup>
</Project>

An example of a SQL Server msdb objects referenced in a SQL database project is shown below:

CREATE VIEW dbo.v_JobLastRunStatus
AS
WITH JobHistory AS (
 SELECT
 j.job_id,
 j.name AS job_name,
 h.run_date,
 h.run_time,
 h.run_status,
 h.message,
 ROW_NUMBER() OVER (
 PARTITION BY j.job_id
 ORDER BY
 h.run_date DESC,
 h.run_time DESC
 ) AS rn
 FROM msdb.dbo.sysjobs j
 JOIN msdb.dbo.sysjobhistory h
 ON j.job_id = h.job_id
 WHERE h.step_id = 0
)
SELECT
 job_name,
 run_status,
 CASE run_status
 WHEN 0 THEN 'Failed'
 WHEN 1 THEN 'Succeeded'
 WHEN 2 THEN 'Retry'
 WHEN 3 THEN 'Canceled'
 ELSE 'Unknown'
 END AS run_status_desc,
 run_date,
 run_time,
 message
FROM JobHistory
WHERE rn = 1;

Related packages