gotchaMinor
Why does SSMS's line counting often get messed up?
Viewed 0 times
whycountingssmslineoftengetdoesmessed
Problem
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:
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 }
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 }
Solution
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.
[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.
Context
StackExchange Database Administrators Q#271431, answer score: 7
Revisions (0)
No revisions yet.