Origin of warning message in sql server

Question

I'm getting warnings like these when running a stored procedure:

Warning: Null value is eliminated by an aggregate or other SET operation.

I know what the warning means, but what stored procedure and line number is causing it?

The thing is that the stored procedure is gigantic and calls a dozen other procedures. So it becomes very hard to localize the problem when SQL Server doesn't give you the line number and procedure name where the warning originates. This is using SQL Server 2008.

Answer 1

you have ansi warnings on and an aggregate (sum, max, min, ...) on a column which contains a null value.

You can set ansi_warnings off but better to remove the nulls

e.g. sum(coalesce(col,0))

Answer 2

You can use Extended Events for this.

1) Create and Start the Session

/*Create Extended Events Session*/

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='WarningLogger')
    DROP EVENT SESSION [WarningLogger] ON SERVER;
CREATE EVENT SESSION [WarningLogger]
ON SERVER
ADD EVENT sqlserver.error_reported(
     ACTION (sqlserver.plan_handle, sqlserver.sql_text, sqlserver.tsql_stack)
     WHERE (([severity]=(10))))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 1 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF)

/*Start the Session*/
ALTER EVENT SESSION [WarningLogger] ON SERVER STATE = START 

2) Test

CREATE PROC #baz 
AS 
declare @g int
select sum(@g)
waitfor delay '00:00:02'

Go
CREATE PROC #bar AS EXEC #baz

GO
CREATE PROC #foo AS EXEC #bar

GO
EXEC #foo

Get the Results

DECLARE 
    @session_name VARCHAR(200) = 'WarningLogger';


with pivoted_data AS(
    SELECT 
        MIN(event_name) as event_name, 
        MIN(event_timestamp) as event_timestamp, 
        unique_event_id, 
        CONVERT 
        ( 
            INT, 
            MIN 
            ( 
                CASE 
                    WHEN 
                        event_name = 'error_reported' and 
                        d_name = 'error' and 
                        d_package IS NULL 
                            THEN d_value
                END 
            ) 
        ) AS [error_reported.error],
        CONVERT 
        ( 
            VARCHAR(MAX), 
            MIN 
            ( 
                CASE 
                    WHEN 
                        event_name = 'error_reported' and 
                        d_name = 'message' and 
                        d_package IS NULL 
                            THEN d_value
                END 
            ) 
        ) AS [error_reported.message],
        CONVERT 
        ( 
            VARCHAR(MAX), 
            MIN 
            ( 
                CASE 
                    WHEN 
                        event_name = 'error_reported' and 
                        d_name = 'plan_handle' and 
                        d_package IS NOT NULL 
                            THEN d_value
                END 
            ) 
        ) AS [error_reported.plan_handle],
        CONVERT 
        ( 
            INT, 
            MIN 
            ( 
                CASE 
                    WHEN 
                        event_name = 'error_reported' and 
                        d_name = 'severity' and 
                        d_package IS NULL 
                            THEN d_value
                END 
            ) 
        ) AS [error_reported.severity],
        CONVERT 
        ( 
            VARCHAR(MAX), 
            MIN 
            ( 
                CASE 
                    WHEN 
                        event_name = 'error_reported' and 
                        d_name = 'sql_text' and 
                        d_package IS NOT NULL 
                            THEN d_value
                END 
            ) 
        ) AS [error_reported.sql_text],
        CONVERT 
        ( 
            INT, 
            MIN 
            ( 
                CASE 
                    WHEN 
                        event_name = 'error_reported' and 
                        d_name = 'state' and 
                        d_package IS NULL 
                            THEN d_value
                END 
            ) 
        ) AS [error_reported.state],
        CONVERT 
        ( 
            XML, 
            MIN 
            ( 
                CASE 
                    WHEN 
                        event_name = 'error_reported' and 
                        d_name = 'tsql_stack' and 
                        d_package IS NOT NULL 
                            THEN d_value
                END 
            ) 
        ) AS [error_reported.tsql_stack],
        CONVERT 
        ( 
            VARCHAR(MAX), 
            MIN 
            ( 
                CASE 
                    WHEN 
                        event_name = 'error_reported' and 
                        d_name = 'user_defined' and 
                        d_package IS NULL 
                            THEN d_value
                END 
            ) 
        ) AS [error_reported.user_defined]
    FROM 
    ( 
        SELECT 
            *, 
            CONVERT(VARCHAR(400), NULL) AS attach_activity_id 
        FROM 
        ( 
            SELECT 
                event.value('(@name)[1]', 'VARCHAR(400)') as event_name, 
                event.value('(@timestamp)[1]', 'DATETIME') as event_timestamp, 
                DENSE_RANK() OVER (ORDER BY event) AS unique_event_id, 
                n.value('(@name)[1]', 'VARCHAR(400)') AS d_name, 
                n.value('(@package)[1]', 'VARCHAR(400)') AS d_package, 
                n.value('((value)[1]/text())[1]', 'VARCHAR(MAX)') AS d_value, 
                n.value('((text)[1]/text())[1]', 'VARCHAR(MAX)') AS d_text 
            FROM 
            ( 
                SELECT 
                    ( 
                        SELECT 
                            CONVERT(xml, target_data) 
                        FROM sys.dm_xe_session_targets st 
                        JOIN sys.dm_xe_sessions s ON 
                            s.address = st.event_session_address 
                        WHERE 
                            s.name = @session_name 
                            AND st.target_name = 'ring_buffer' 
                    ) AS [x] 
                FOR XML PATH(''), TYPE 
            ) AS the_xml(x) 
            CROSS APPLY x.nodes('//event') e (event) 
            CROSS APPLY event.nodes('*') AS q (n) 
        ) AS data_data 
    ) AS activity_data 
    GROUP BY 
        unique_event_id 
),
        StackData AS
        ( SELECT 
            pivoted_data.*,
            frame_xml.value('(./@level)', 'int')      AS [frame_level],
            frame_xml.value('(./@handle)', 'varchar(MAX)') AS [sql_handle],
            frame_xml.value('(./@offsetStart)', 'int')     AS [offset_start],
            frame_xml.value('(./@offsetEnd)', 'int')       AS [offset_end]
        FROM pivoted_data CROSS APPLY [error_reported.tsql_stack].nodes('//frame') N (frame_xml)
        )

   SELECT unique_event_id, [frame_level], sd.[error_reported.message],event_timestamp,sd.[error_reported.sql_text],
        object_name(st.objectid, st.dbid) AS ObjectName,
        SUBSTRING(st.text, (sd.offset_start/2)+1, ((
                CASE sd.offset_end
                    WHEN -1
                    THEN DATALENGTH(st.text)
                    ELSE sd.offset_end
                END - sd.offset_start)/2) + 1) AS statement_text,
        qp.query_plan,
        qs2.creation_time,
        qs2.last_execution_time,
        qs2.execution_count
    FROM StackData AS sd CROSS APPLY sys.dm_exec_sql_text(CONVERT(VARBINARY(MAX),sd.sql_handle,1)) AS st
        LEFT OUTER JOIN sys.dm_exec_query_stats qs2
        ON  qs2.sql_handle = CONVERT(VARBINARY(MAX),sd.sql_handle,1) OUTER APPLY sys.dm_exec_query_plan(CONVERT(VARBINARY(MAX),qs2.plan_handle,1)) AS qp
        WHERE st.text NOT LIKE '%this_query%'
        ORDER BY unique_event_id ASC, [frame_level] DESC