Dependencies between objects

drozdse1

Dear Redgate Team

Is there a specific order of SQL objects (Tables, Views, Synonyms...) during the deployment process?

Does the DLM component validate the dependencies of objects during while it's deploying?

Assuming there is a view in a stored procedure. Does DLM create first the view and then the stored procedure?

Maybe my question seems to be stupid but we encountered situations where the deployment of a SP failed because the involved view was missing.

We just want to understand how DLM handles dependencies during the deployment.

Thank you
Sebastian

Rob C

Hi Sebastian,

DLM Automation uses the SQL Compare engine, which will calculate a deployment order based on the dependencies of the objects you are trying to deploy. If a stored procedure references a view then the view should be deployed first, followed by the stored procedure.

It's possible that it could be a bug. If you're not already on the latest version of DLM Automation (2.0.7.256), I'd recommend updating just in case the SQL Compare engine contains changes that fix the problem. Here's a download link - download

If that doesn't fix the problem, would you be open to providing the view and stored procedure definitions so that I can investigate?

drozdse1

Dear Robert

We have tested the attached DLM Automation version (2.0.7.256) and we still encounter the mentioned problem.

The problem seems to be when we have a direct database reference to the view (for example -> select * from database.schema.viewname) then the dependency manager is not able to deploy in the right order. Please find below the attached example code. As you can see the additional column 'Dummy' was not found during the deployment. It seems that the modified view (DWHWRK.[raw].[v_ArticleGroup]) was not created before procedure.

Thank you for your answer in advance
Best Regards

Sebastian Drozdz

14:02:38 Cleaning database 'DWHDeploy' on server 'XXXXXXX'.
14:03:17 WARNING: The error 'Invalid column name 'Dummy'.' occurred when executing the following SQL:
 
14:03:17 /*
 
14:03:17 ###############################################################################
 
14:03:17 Author:    
 
14:03:17 Version:      1.0
 
14:03:17 Created on:   18.08.2016
 
14:03:17 ###############################################################################
 
14:03:17 Data population for ArticleGroup, Product Group, Product House, Article Unit
 
14:03:17 ###############################################################################
 
14:03:17 Date         Who   Vers   Comments
 
14:03:17 ----------   ---   ----   --------
 
14:03:17 08.11.2016   ND    1.0    Initial creation
 
14:03:17 16.01.2017   SR    1.1    New Concept of ArticleGroup
 
14:03:17 26.01.2017   SR    1.2    Inclusion of Master Data Service
 
14:03:17 ###############################################################################
 
14:03:17 */ 
 
14:03:17 
 
14:03:17 CREATE PROCEDURE [int].[usp_ArticleGroup]
 
14:03:17     (
 
14:03:17       @i_Load_ID BIGINT ,
 
14:03:17       @o_Operation CHAR(1) OUTPUT ,
 
14:03:17       @o_OperationRowCount BIGINT OUTPUT
 
14:03:17     )
 
14:03:17 AS
 
14:03:17     BEGIN
 
14:03:17 
 
14:03:17         SET NOCOUNT ON;
 
14:03:17         SET XACT_ABORT ON;
 
14:03:17         BEGIN TRANSACTION;
 
14:03:17         BEGIN TRY
 
14:03:17    
 
14:03:17             --################################################
 
14:03:17             -- ETL code start here
 
14:03:17             -- | | | | | | | | | | | | | | | | | | | | | | | | 
 
14:03:17             -- v v v v v v v v v v v v v v v v v v v v v v v v
 
14:03:17             
 
14:03:17             -- ArticleGroups from Samba
 
14:03:17    INSERT  INTO [int].[ArticleGroup] WITH ( TABLOCK )
 
14:03:17                     ( [Load_ID] ,
 
14:03:17        [IsInferred] ,
 
14:03:17        [ArticleGroup_SID] ,
 
14:03:17        [ArticleGroupCode] ,
 
14:03:17        [ArticleGroupDescription] ,
 
14:03:17        [ProductGroup_CD] ,
 
14:03:17        [ProductGroupDescription] ,
 
14:03:17        [ProductHouse_CD] ,
 
14:03:17        [ProductHouseDescription] ,
 
14:03:17        [Unit_CD] ,
 
14:03:17        [UnitDescription] ,
 
14:03:17        [ArticleGroup_SmbID] ,
 
14:03:17        [AGRHierarchy_CD] ,
 
14:03:17        [PlanningGroup_CD] ,
 
14:03:17        [PlanningGroupDescription] ,
 
14:03:17        [PlanningSubGroup_CD] ,
 
14:03:17        [PlanningSubGroupDescription],
 
14:03:17        [Dummy]
 
14:03:17                     )
 
14:03:17                     SELECT  [Load_ID] = @i_Load_ID ,
 
14:03:17                             [IsInferred] = 0 ,
 
14:03:17                             [ArticleGroup_SID] = k.[ArticleGroup_SID] ,
 
14:03:17        [ArticleGroupCode] = g.[ArticleGroupCode] ,
 
14:03:17        [ArticleGroupDescription] = g.[ArticleGroupDescription] ,
 
14:03:17        [ProductGroup_CD] = g.[ProductGroupCode] ,
 
14:03:17        [ProductGroupDescription] = g.[ProductGroupDescription] ,
 
14:03:17        [ProductHouse_CD] = g.[ProductHouse] ,
 
14:03:17        [ProductHouseDescription] = g.[ProductHouseDescription] ,
 
14:03:17        [Unit_CD] = g.[UnitCode] ,
 
14:03:17        [UnitDescription] = g.[UnitDescription] ,
 
14:03:17        [ArticleGroup_SmbID] = g.[ArticleGroup_SmbID] ,
 
14:03:17        [AGRHierarchy_CD] = g.[AGRHierarchy_CD] ,
 
14:03:17        [PlanningGroup_CD] = g.[PlanningGroup_CD] ,
 
14:03:17        [PlanningGroupDescription] = g.[PlanningGroupDescription] ,
 
14:03:17        [PlanningSubGroup_CD] = g.[PlanningSubGroup_CD] ,
 
14:03:17        [PlanningSubGroupDescription] = g.[PlanningSubGroupDescription],
 
14:03:17        [Dummy] = g.[Dummy]
 
14:03:17      FROM    DWHWRK.[raw].[v_ArticleGroup] AS g
 
14:03:17        INNER JOIN [key].[ArticleGroup] AS k ON g.[ArticleGroupCode] = k.[ArticleGroupCode]
 
14:03:17             
 
14:03:17             -- ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ 
 
14:03:17             -- | | | | | | | | | | | | | | | | | | | | | | | | 
 
14:03:17             -- ETL code end here
 
14:03:17             --################################################ 
 
14:03:17 
 
14:03:17             SET @o_OperationRowCount = @@ROWCOUNT;
 
14:03:17             SET @o_Operation = 'I';  -- I: Insert / U: Update / D: Delete / M: Merge
 
14:03:17    
 
14:03:17         END TRY
 
14:03:17         BEGIN CATCH
 
14:03:17             IF @@TRANCOUNT > 0
 
14:03:17                 BEGIN
 
14:03:17 
 
14:03:17                     DECLARE @l_ErrorSeverity INT = ERROR_SEVERITY();
 
14:03:17                     DECLARE @l_ErrorState INT = ERROR_STATE(); 
 
14:03:17                     DECLARE @l_ErrorMessage NVARCHAR(4000);
 
14:03:17 
 
14:03:17                     SET @l_ErrorMessage = N'Error message: '
 
14:03:17                         + CONVERT(NVARCHAR(4000), ERROR_MESSAGE()) + ' '
 
14:03:17                         + N'Error at line: '
 
14:03:17                         + CONVERT(NVARCHAR(4000), ERROR_LINE()) + ' '
 
14:03:17                         + N'Error number: '
 
14:03:17                         + CONVERT(NVARCHAR(4000), ERROR_NUMBER());
 
14:03:17      
 
14:03:17                     ROLLBACK TRANSACTION;
 
14:03:17                     RAISERROR(@l_ErrorMessage,@l_ErrorSeverity,@l_ErrorState);
 
14:03:17    
 
14:03:17                 END;
 
14:03:17         END CATCH;
 
14:03:17 
 
14:03:17         IF @@TRANCOUNT > 0
 
14:03:17             BEGIN
 
14:03:17  
 
14:03:17                 COMMIT TRANSACTION;
 
14:03:17 
 
14:03:17             END; 
 
14:03:17         SET XACT_ABORT OFF;
 
14:03:17 
 
14:03:17     END;

Rob C

Hi Sebastian,

Ah, this explains things and I'll try to explain what is going on. We are trying to build a series of scripts. Script files don't really have a concept of database name as they are just flat files representing individual objects. When you use three part naming, the parser assumes you are referring to an external dependency that doesn't need to be built and this can result in an incorrect deployment order.

To fix this, you'll need to adjust these references to remove the database name.

saulcruz

Rob C wrote: »

Hi Sebastian,

Ah, this explains things and I'll try to explain what is going on. We are trying to build a series of scripts. Script files don't really have a concept of database name as they are just flat files representing individual objects. When you use three part naming, the parser assumes you are referring to an external dependency that doesn't need to be built and this can result in an incorrect deployment order.

To fix this, you'll need to adjust these references to remove the database name.

How about the stored procedures that might be using other database tables for merging purposes, lookups (RI checks) during ETL process? how can we deal with cross-database objects mainly during DLM schema validation? I thought about synonyms but how are these handled during schema validation, do they fail?