TransWikia.com

Why does SSMS's line counting often get messed up?

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%'

enter image description here

But in a real world example, it will be off:

enter image description here

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

One Answer

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP