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;
/ comments
- Community
- DLM Automation
- Dependencies between objects
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...
0 votes
