All geek questions in one place

The origin of the warning message on sql server

I get warnings like these when starting a stored procedure:

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

I know what a warning means , but what stored procedure and line number call it?

The fact is that the stored procedure is gigantic and calls dozens of other procedures. Therefore, it is very difficult to localize the problem when SQL Server does not give you the line number and the name of the procedure from which the warning occurs. This is used by SQL Server 2008.

+4
source share
3 answers

You can use Extended Events for this.

1) Create and run a 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 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
+3
source

you have ansi warnings and a collection (sum, max, min, ...) in a column that contains a null value.

You can disable ansi_warnings, but it's better to remove zeros

eg. amount (COALESCE (Col, 0))

+4
source

Capture the main process in the trace file .

0
source

Source: https://habr.com/ru/post/1342802/

More articles:


All Articles