What are the challenges you face when working across database platforms? Take the survey
Options

Dependencies between objects

drozdse1drozdse1 Posts: 3
edited January 31, 2017 8:37AM in DLM Automation
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

Comments

  • Options
    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?
    Software Engineer
    Redgate Software
  • Options
    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;
    
  • Options
    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.
    Software Engineer
    Redgate Software
  • Options
    saulcruzsaulcruz Posts: 19 Bronze 1
    edited February 1, 2018 10:13PM
    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?
Sign In or Register to comment.