Database Administrators Asked on November 23, 2021
I’m trying to get some code to run with the time tested ‘execute, google the red text, do what SO says, repeat’ method and I’ve noticed that the error handler’s line counting is often wrong.
For exaple, in this stored procedure it will be correct:
create procedure why_it_be_like_dis
as
select * from INFORMATION_SCHEMA.tables where column_name like '%bananna%'
But in a real world example, it will be off:
Question: Why don’t the line numbers always match?
error message:
Msg 207, Level 16, State 1, Procedure delete from_and_load, Line 21 [Batch Start Line 7]
Invalid column name ‘SRGY_STM_LKP_ID’.
full sp:
USE [SRGRY_DMART_ETL]
GO
/****** Object: StoredProcedure [dbo].[delete from_and_load] Script Date: 7/22/2020 8:20:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[delete from_and_load]
AS
BEGIN
DECLARE @btch_start DATETIME
,@btch_end DATETIME
SELECT @btch_start = getdate()
EXEC [dbo].[snapshot_constraints]
EXEC dbo.drop_all_constraints
delete from [dbo].[CASE_2_PAT_CASE_BRG];
delete from [dbo].[CASE_2_PVDR_BRG];
delete from [dbo].[CASE_2_DGNS_BRG];
delete from [dbo].[CASE_2_PCD_BRG];
delete from [dbo].[CASE_2_DLAY_RSN_BRG];
DELETE
FROM [dbo].[CASE_FCT] where SRGY_STM_LKP_ID in (111,333);
DELETE FROM [dbo].[PAT_CASE_FCT] where SRGY_STM_LKP_ID in (111,333);
DELETE FROM [dbo].[PVDR_RL_DIM] where STM_LKP_ID in (111,333);
DELETE
FROM [dbo].[ADM_TP_DIM] where SRGY_STM_LKP_ID in (111,333)
DELETE
FROM [dbo].[ANES_TP_DIM] where SRGY_STM_LKP_ID in (111,333)
DELETE
FROM [dbo].[CNCL_RSN_DIM] where SRGY_STM_LKP_ID in (111,333);
DELETE
FROM [dbo].[DLAY_IND_DIM] where SRGY_STM_LKP_ID in (111,333);
delete from [dbo].[LOC_IDN_ARR] where STM_LKP_ID in (111,333);
delete from [dbo].[PTNT_IDN_ARR] where STM_LKP_ID in (111,333);
-- V6 - delete from / DELETE { From Sooraj }
delete from DBO.PTNT_INSR_ARR where STM_LKP_ID in (111,333)
DELETE
FROM [dbo].[PVDR_DIM] where STM_LKP_ID in (111,333) -- YOU COULD TRY THE DEFAULT INSERT at a streatch
DELETE
FROM [dbo].[PTNT_DIM] where STM_LKP_ID in (111,333)
DELETE
FROM [dbo].[LOC_DIM] where STM_LKP_ID in (111,333)
-- ################## Abraham's Improvised Idea of SCM OR LOC ID ################## END
-- -- v5 SP list from Towsif
PRINT '[dbo].[PROC_PATIENT_INSERT]'
EXEC [dbo].[PROC_PATIENT_INSERT]
PRINT '[dbo].[PROC_PTNTINSR_ARR_INSERT]'
EXEC [dbo].[PROC_PTNTINSR_ARR_INSERT]
PRINT '[dbo].[PROC_PVDR_DIM_INSERT]'
EXEC [dbo].[PROC_PVDR_DIM_INSERT]
PRINT 'dbo.PROC_LOC_FCY_HIER_INSERT'
EXEC dbo.PROC_LOC_FCY_HIER_INSERT
PRINT '[dbo].[PROC_LOC_IDN_ARR_INSERT]'
EXEC [dbo].[PROC_LOC_IDN_ARR_INSERT];
PRINT '[dbo].[PROC_DLAY_IND_DIM_INSERT]'
EXEC [dbo].[PROC_DLAY_IND_DIM_INSERT];
PRINT '[dbo].[PROC_CNCL_RSN_DIM_INSERT]'
EXEC [dbo].[PROC_CNCL_RSN_DIM_INSERT];
PRINT '[dbo].[PROC_ANES_TP_DIM_INSERT]'
EXEC [dbo].[PROC_ANES_TP_DIM_INSERT];
PRINT '[dbo].[PROC_ADM_TP_DIM_INSERT]'
EXEC [dbo].[PROC_ADM_TP_DIM_INSERT];
PRINT '[dbo].[PROC_PVDR_RL_DIM_INSERT]'
EXEC [dbo].[PROC_PVDR_RL_DIM_INSERT];
PRINT '[dbo].[PROC_PAT_CASE_FCT_INSERT]'
EXEC [dbo].[PROC_PAT_CASE_FCT_INSERT];
PRINT '[dbo].[PROC_CASE_FCT_INSERT]'
EXEC [dbo].[PROC_CASE_FCT_INSERT];
PRINT '[dbo].[PROC_PTNT_IDN_ARR_INSERT]'
EXEC [dbo].[PROC_PTNT_IDN_ARR_INSERT];-- This should execute after PTNT_DIM has been loaded.”
PRINT '[dbo].[PROC_PTNT_DIM_EMPI_UPDATE]'
EXEC [dbo].[PROC_PTNT_DIM_EMPI_UPDATE];
PRINT '[dbo].[PROC_CASE_2_DLAY_RSN_BRG_INSERT]'
EXEC [dbo].[PROC_CASE_2_DLAY_RSN_BRG_INSERT];
PRINT '[dbo].[PROC_CASE_PCD_BRG_INSERT]'
EXEC [dbo].[PROC_CASE_PCD_BRG_INSERT];
PRINT '[dbo].[PROC_CASE_DGNS_BRG_INSERT]'
EXEC [dbo].[PROC_CASE_DGNS_BRG_INSERT];
PRINT '[dbo].[PROC_CASE_PVDR_BRG_INSERT]'
EXEC [dbo].[PROC_CASE_PVDR_BRG_INSERT];
PRINT '[dbo].[PROC_CASE_2_PAT_CASE_BRG_INSERT]'
EXEC [dbo].[PROC_CASE_2_PAT_CASE_BRG_INSERT];
PRINT 'dbo.PROC_CASE_VST_IDN_ARR_INSERT'
EXEC dbo.PROC_CASE_VST_IDN_ARR_INSERT;
PRINT 'dbo.PROC_SVC_DIM_INSERT'
EXEC dbo.PROC_SVC_DIM_INSERT;
PRINT 'dbo.PROC_GRP_DIM_INSERT'
EXEC dbo.PROC_GRP_DIM_INSERT;
PRINT '[dbo].[PROC_PVDR_2_SVC_BRG_INSERT]'
EXEC [dbo].[PROC_PVDR_2_SVC_BRG_INSERT];
PRINT '[dbo].[PROC_PVDR_2_GRP_BRG_INSERT]'
EXEC [dbo].[PROC_PVDR_2_GRP_BRG_INSERT]
PRINT 'dbo.[PROC_BLC_DIM_INSERT]'
EXEC dbo.[PROC_BLC_DIM_INSERT]
--print '[dbo].[PROC_BLC_SHD_BRG_INSERT]'
--exec [dbo].[PROC_BLC_SHD_BRG_INSERT]
PRINT '[dbo].[PROC_BLC_2_OWN_BRG_INSERT]'
EXEC [dbo].[PROC_BLC_2_OWN_BRG_INSERT]
--PRINT 'dbo.PROC_SCM_HH_PAT_CASE_INS'
--EXEC dbo.PROC_SCM_HH_PAT_CASE_INS
PRINT 'dbo.PROC_SCM_SIUH_PAT_CASE_INS'
EXEC dbo.PROC_SCM_SIUH_PAT_CASE_INS
EXEC dbo.add_all_constraints
---exec dbo.proc_record_counts
--exec dbo.proc_column_counts
SELECT @btch_end = getdate()
INSERT INTO dbo.BTCH_LOG (
BTCH_STRT_TS
,BTCH_END_TS
)
VALUES (
@btch_start
,@btch_end
)
--exec dbo.proc_etl_stat_log_ins
END
Msg 207, Level 16, State 1, Procedure delete from_and_load, Line 21 [Batch Start Line 7] Invalid column name 'SRGY_STM_LKP_ID'.
Note the error message says "Batch start line...". The line number for compilation errors is relative to the start of the batch, not the entire script. If you add the error line and batch start line numbers (21 + 7), the result of 28 is the line number within the script.
Answered by Dan Guzman on November 23, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP