Comments
4 comments
-
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? -
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 Drozdz14: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;
-
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. -
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?
Add comment
Please sign in to leave a comment.
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