sql prompt crashing on formatting script

Provides intelligent code completion for SQL Server editors.

Moderators: JonathanWatts, David Atkinson, Anu Deshpande, Paul Stephenson, Michelle Taylor, Mike Upton, justin.caldicott, Aaron Law

sql prompt crashing on formatting script

Postby gvarol@corelogic.com » Fri Apr 13, 2012 2:48 am

Formatting this script is crashing sql prompt and ssms with it. I have sql prompt version 5.3.0.3
Please advise, thank you




USE [master]
GO
IF OBJECT_ID('dbo.sp_session') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_session AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* *****************************************************************************************************************************
2011-2012, Gokhan Varol, gvarol@corelogic.com
sp_session will create a table/object comparison script by full outer joining two objects and taking nullability into consideration
and will have a pivot (@ResultToColumnValuePair) and regular option (this one will doiuble the number of columns in the output) for output
Executing \"EXEC sp_session\" without parameters will print the full execution script with default parameters without taking any action.

sp_session is free to download and use for personal, educational, and internal
corporate purposes, provided that this header is preserved. Redistribution or sale of sp_session,
in whole or in part, is prohibited without the author's express written consent.
***************************************************************************************************************************** */
ALTER PROCEDURE dbo.sp_session @SPID INT = @@SPID
AS
SET NOCOUNT ON


DECLARE @InputBuffer TABLE (EventType VARCHAR(30),
Parameters INT,
EventInfo VARCHAR(4000))
DECLARE @SQL VARCHAR(MAX) = 'DBCC INPUTBUFFER(' + CAST(@SPID AS VARCHAR) + ') WITH NO_INFOMSGS'

INSERT @InputBuffer
EXEC (@SQL);
WITH msource
AS (SELECT es.session_id,
rs.blocking_session_id,
GETDATE() AS [current_time],
es.login_time,
(SELECT Outval FROM Util.dbo.GetDateTimeDifferenceInline (es.login_time, GETDATE ())) AS login_duration,
es.host_name,
es.program_name,
es.client_interface_name,
es.login_name,
DB_NAME(rs.database_id) AS database_name,
es.nt_domain,
es.nt_user_name,
es.original_login_name,
rs.command,
es.STATUS AS session_status,
rs.STATUS AS request_status,
rs.wait_type,
rs.wait_time,
rs.last_wait_type,
rs.wait_resource,
es.context_info,
es.cpu_time AS session_cpu_time,
rs.cpu_time AS request_cpu_time,
es.reads AS session_reads,
rs.reads AS request_reads,
es.writes AS session_writes,
rs.writes AS request_writes,
es.logical_reads AS session_logical_reads,
rs.logical_reads AS request_logical_reads,
es.total_elapsed_time AS session_total_elapsed_time,
rs.total_elapsed_time AS request_total_elapsed_time,
rs.percent_complete AS request_percent_complete,
rs.estimated_completion_time,
rs.granted_query_memory AS request_granted_query_memory,
qg.requested_memory_kb,
qg.granted_memory_kb,
qg.required_memory_kb,
qg.used_memory_kb,
qg.max_used_memory_kb,
qg.query_cost,
qg.timeout_sec,
es.memory_usage,
es.total_scheduled_time,
rs.open_transaction_count,
rs.open_resultset_count,
es.endpoint_id AS session_endpoint_id,
cs.endpoint_id AS connection_endpoint_id,
es.last_request_start_time,
es.last_request_end_time,
(SELECT Outval
FROM Util.dbo.GetDateTimeDifferenceInline(es.last_request_start_time,
CASE WHEN es.last_request_end_time > es.last_request_start_time
THEN es.last_request_end_time
ELSE GETDATE()
END)) AS last_request_duration,
es.is_user_process,
es.text_size AS session_text_size,
rs.text_size AS request_text_size,
es.language AS session_language,
rs.LANGUAGE AS request_language,
es.date_format AS session_date_format,
rs.date_format AS request_date_format,
es.date_first AS session_date_first,
rs.date_first AS request_date_first,
es.quoted_identifier AS session_quoted_identifier,
rs.quoted_identifier AS request_quoted_identifier,
es.arithabort AS session_arithabort,
rs.arithabort AS request_arithabort,
es.ansi_null_dflt_on AS session_ansi_null_dflt_on,
rs.ansi_null_dflt_on AS request_ansi_null_dflt_on,
es.ansi_defaults AS session_ansi_defaults,
rs.ansi_defaults AS request_ansi_defaults,
es.ansi_warnings AS session_ansi_warnings,
rs.ansi_warnings AS request_ansi_warnings,
es.ansi_padding AS session_ansi_padding,
rs.ansi_padding AS request_ansi_padding,
es.ansi_nulls AS session_ansi_nulls,
rs.ansi_nulls AS request_ansi_nulls,
es.concat_null_yields_null AS session_concat_null_yields_null,
rs.concat_null_yields_null AS request_concat_null_yields_null,
es.transaction_isolation_level AS session_transaction_isolation_level,
rs.transaction_isolation_level AS request_transaction_isolation_level,
es.lock_timeout AS session_lock_timeout,
rs.lock_timeout AS request_lock_timeout,
es.deadlock_priority AS session_deadlock_priority,
rs.deadlock_priority AS request_deadlock_priority,
es.row_count AS session_row_count,
rs.row_count AS request_row_count,
es.prev_error AS session_prev_error,
rs.prev_error AS request_prev_error,
es.original_security_id,
es.last_successful_logon,
es.last_unsuccessful_logon,
es.unsuccessful_logons,
es.group_id AS session_group_id,
rs.group_id AS request_group_id,
rs.request_id AS request_request,
qg.request_id AS memory_grant_request_id,
rs.start_time AS request_start_time,
(SELECT Outval FROM Util.dbo.GetDateTimeDifferenceInline (rs.start_time, GETDATE ())) AS request_duration,
rs.statement_start_offset,
rs.statement_end_offset,
rs.database_id,
rs.user_id,
cs.connection_id AS connection_connection_id,
rs.connection_id AS request_connection_id,
rs.transaction_id,
rs.scheduler_id AS request_scheduler_id,
qg.scheduler_id AS memory_grant_scheduler_id,
rs.task_address,
rs.nest_level,
rs.executing_managed_code,
rs.query_hash,
rs.query_plan_hash,
qg.dop,
qg.request_time AS memory_grant_request_time,
(SELECT Outval FROM Util.dbo.GetDateTimeDifferenceInline (qg.request_time, GETDATE ())) AS memory_grant_request_duration,
qg.grant_time AS memory_grant_time,
(SELECT Outval FROM Util.dbo.GetDateTimeDifferenceInline (qg.grant_time, GETDATE ())) AS memory_grant_duration,
qg.resource_semaphore_id,
qg.queue_id,
qg.wait_order,
qg.is_next_candidate,
qg.wait_time_ms,
rs.sql_handle AS request_sql_handle,
qg.sql_handle AS memory_grant_sql_handle,
rs.plan_handle AS request_plan_handle,
qg.plan_handle AS memory_grant_plan_handle,
qg.group_id,
qg.pool_id,
qg.is_small,
qg.ideal_memory_kb,
cs.most_recent_session_id,
cs.connect_time,
(SELECT Outval FROM Util.dbo.GetDateTimeDifferenceInline (cs.connect_time, GETDATE ())) AS connect_duration,
cs.net_transport,
cs.protocol_type,
cs.protocol_version,
cs.encrypt_option,
cs.auth_scheme,
cs.node_affinity,
cs.num_reads,
cs.num_writes,
cs.last_read,
(SELECT Outval FROM Util.dbo.GetDateTimeDifferenceInline (cs.last_read, GETDATE ())) AS last_read_duration,
cs.last_write,
(SELECT Outval FROM Util.dbo.GetDateTimeDifferenceInline (cs.last_write, GETDATE ())) AS last_write_duration,
cs.net_packet_size,
cs.client_net_address,
cs.client_tcp_port,
cs.local_net_address,
cs.local_tcp_port,
cs.parent_connection_id,
cs.most_recent_sql_handle,
es.host_process_id,
es.client_version,
es.security_id
FROM sys.dm_exec_sessions es (NOLOCK)
INNER JOIN sys.dm_exec_connections cs (NOLOCK) ON cs.session_id = @SPID
INNER JOIN sys.dm_exec_requests rs (NOLOCK) ON es.session_id = @SPID
LEFT OUTER JOIN sys.dm_exec_query_memory_grants qg (NOLOCK) ON qg.session_id = @SPID
WHERE rs.session_id = @SPID)
/*
SELECT *
INTO #temp
FROM msource

EXEC dbo.sp_unpivot
@TableName = '#temp',
@IncludeColumnId = 0,
@UseSQLVariant = 1,
@OrderByColumnName = 0,
@IncludeColumnList = NULL,
@ExcludeColumnList = NULL
GO
*/
SELECT upv.ColumnName,
upv.ColumnValue
FROM msource up
CROSS APPLY(SELECT ColumnName, ColumnValue
FROM (VALUES('session_id', CAST(up.session_id AS sql_variant)),
('blocking_session_id', CAST(up.blocking_session_id AS sql_variant)),
('current_time', CAST(up.current_time AS sql_variant)),
('login_time', CAST(up.login_time AS sql_variant)),
('login_duration', CAST(up.login_duration AS sql_variant)),
('host_name', CAST(up.host_name AS sql_variant)),
('program_name', CAST(up.program_name AS sql_variant)),
('client_interface_name', CAST(up.client_interface_name AS sql_variant)),
('login_name', CAST(up.login_name AS sql_variant)),
('database_name', CAST(up.database_name AS sql_variant)),
('nt_domain', CAST(up.nt_domain AS sql_variant)),
('nt_user_name', CAST(up.nt_user_name AS sql_variant)),
('original_login_name', CAST(up.original_login_name AS sql_variant)),
('command', CAST(up.command AS sql_variant)),
('session_status', CAST(up.session_status AS sql_variant)),
('request_status', CAST(up.request_status AS sql_variant)),
('wait_type', CAST(up.wait_type AS sql_variant)),
('wait_time', CAST(up.wait_time AS sql_variant)),
('last_wait_type', CAST(up.last_wait_type AS sql_variant)),
('wait_resource', CAST(up.wait_resource AS sql_variant)),
('context_info', CAST(up.context_info AS sql_variant)),
('session_cpu_time', CAST(up.session_cpu_time AS sql_variant)),
('request_cpu_time', CAST(up.request_cpu_time AS sql_variant)),
('session_reads', CAST(up.session_reads AS sql_variant)),
('request_reads', CAST(up.request_reads AS sql_variant)),
('session_writes', CAST(up.session_writes AS sql_variant)),
('request_writes', CAST(up.request_writes AS sql_variant)),
('session_logical_reads', CAST(up.session_logical_reads AS sql_variant)),
('request_logical_reads', CAST(up.request_logical_reads AS sql_variant)),
('session_total_elapsed_time', CAST(up.session_total_elapsed_time AS sql_variant)),
('request_total_elapsed_time', CAST(up.request_total_elapsed_time AS sql_variant)),
('request_percent_complete', CAST(up.request_percent_complete AS sql_variant)),
('estimated_completion_time', CAST(up.estimated_completion_time AS sql_variant)),
('request_granted_query_memory', CAST(up.request_granted_query_memory AS sql_variant)),
('requested_memory_kb', CAST(up.requested_memory_kb AS sql_variant)),
('granted_memory_kb', CAST(up.granted_memory_kb AS sql_variant)),
('required_memory_kb', CAST(up.required_memory_kb AS sql_variant)),
('used_memory_kb', CAST(up.used_memory_kb AS sql_variant)),
('max_used_memory_kb', CAST(up.max_used_memory_kb AS sql_variant)),
('query_cost', CAST(up.query_cost AS sql_variant)),
('timeout_sec', CAST(up.timeout_sec AS sql_variant)),
('memory_usage', CAST(up.memory_usage AS sql_variant)),
('total_scheduled_time', CAST(up.total_scheduled_time AS sql_variant)),
('open_transaction_count', CAST(up.open_transaction_count AS sql_variant)),
('open_resultset_count', CAST(up.open_resultset_count AS sql_variant)),
('session_endpoint_id', CAST(up.session_endpoint_id AS sql_variant)),
('connection_endpoint_id', CAST(up.connection_endpoint_id AS sql_variant)),
('last_request_start_time', CAST(up.last_request_start_time AS sql_variant)),
('last_request_end_time', CAST(up.last_request_end_time AS sql_variant)),
('last_request_duration', CAST(up.last_request_duration AS sql_variant)),
('is_user_process', CAST(up.is_user_process AS sql_variant)),
('session_text_size', CAST(up.session_text_size AS sql_variant)),
('request_text_size', CAST(up.request_text_size AS sql_variant)),
('session_language', CAST(up.session_language AS sql_variant)),
('request_language', CAST(up.request_language AS sql_variant)),
('session_date_format', CAST(up.session_date_format AS sql_variant)),
('request_date_format', CAST(up.request_date_format AS sql_variant)),
('session_date_first', CAST(up.session_date_first AS sql_variant)),
('request_date_first', CAST(up.request_date_first AS sql_variant)),
('session_quoted_identifier', CAST(up.session_quoted_identifier AS sql_variant)),
('request_quoted_identifier', CAST(up.request_quoted_identifier AS sql_variant)),
('session_arithabort', CAST(up.session_arithabort AS sql_variant)),
('request_arithabort', CAST(up.request_arithabort AS sql_variant)),
('session_ansi_null_dflt_on', CAST(up.session_ansi_null_dflt_on AS sql_variant)),
('request_ansi_null_dflt_on', CAST(up.request_ansi_null_dflt_on AS sql_variant)),
('session_ansi_defaults', CAST(up.session_ansi_defaults AS sql_variant)),
('request_ansi_defaults', CAST(up.request_ansi_defaults AS sql_variant)),
('session_ansi_warnings', CAST(up.session_ansi_warnings AS sql_variant)),
('request_ansi_warnings', CAST(up.request_ansi_warnings AS sql_variant)),
('session_ansi_padding', CAST(up.session_ansi_padding AS sql_variant)),
('request_ansi_padding', CAST(up.request_ansi_padding AS sql_variant)),
('session_ansi_nulls', CAST(up.session_ansi_nulls AS sql_variant)),
('request_ansi_nulls', CAST(up.request_ansi_nulls AS sql_variant)),
('session_concat_null_yields_null', CAST(up.session_concat_null_yields_null AS sql_variant)),
('request_concat_null_yields_null', CAST(up.request_concat_null_yields_null AS sql_variant)),
('session_transaction_isolation_level', CAST(up.session_transaction_isolation_level AS sql_variant)),
('request_transaction_isolation_level', CAST(up.request_transaction_isolation_level AS sql_variant)),
('session_lock_timeout', CAST(up.session_lock_timeout AS sql_variant)),
('request_lock_timeout', CAST(up.request_lock_timeout AS sql_variant)),
('session_deadlock_priority', CAST(up.session_deadlock_priority AS sql_variant)),
('request_deadlock_priority', CAST(up.request_deadlock_priority AS sql_variant)),
('session_row_count', CAST(up.session_row_count AS sql_variant)),
('request_row_count', CAST(up.request_row_count AS sql_variant)),
('session_prev_error', CAST(up.session_prev_error AS sql_variant)),
('request_prev_error', CAST(up.request_prev_error AS sql_variant)),
('original_security_id', CAST(up.original_security_id AS sql_variant)),
('last_successful_logon', CAST(up.last_successful_logon AS sql_variant)),
('last_unsuccessful_logon', CAST(up.last_unsuccessful_logon AS sql_variant)),
('unsuccessful_logons', CAST(up.unsuccessful_logons AS sql_variant)),
('session_group_id', CAST(up.session_group_id AS sql_variant)),
('request_group_id', CAST(up.request_group_id AS sql_variant)),
('request_request', CAST(up.request_request AS sql_variant)),
('memory_grant_request_id', CAST(up.memory_grant_request_id AS sql_variant)),
('request_start_time', CAST(up.request_start_time AS sql_variant)),
('request_duration', CAST(up.request_duration AS sql_variant)),
('statement_start_offset', CAST(up.statement_start_offset AS sql_variant)),
('statement_end_offset', CAST(up.statement_end_offset AS sql_variant)),
('database_id', CAST(up.database_id AS sql_variant)),
('user_id', CAST(up.user_id AS sql_variant)),
('connection_connection_id', CAST(up.connection_connection_id AS sql_variant)),
('request_connection_id', CAST(up.request_connection_id AS sql_variant)),
('transaction_id', CAST(up.transaction_id AS sql_variant)),
('request_scheduler_id', CAST(up.request_scheduler_id AS sql_variant)),
('memory_grant_scheduler_id', CAST(up.memory_grant_scheduler_id AS sql_variant)),
('task_address', CAST(up.task_address AS sql_variant)),
('nest_level', CAST(up.nest_level AS sql_variant)),
('executing_managed_code', CAST(up.executing_managed_code AS sql_variant)),
('query_hash', CAST(up.query_hash AS sql_variant)),
('query_plan_hash', CAST(up.query_plan_hash AS sql_variant)),
('dop', CAST(up.dop AS sql_variant)),
('memory_grant_request_time', CAST(up.memory_grant_request_time AS sql_variant)),
('memory_grant_request_duration', CAST(up.memory_grant_request_duration AS sql_variant)),
('memory_grant_time', CAST(up.memory_grant_time AS sql_variant)),
('memory_grant_duration', CAST(up.memory_grant_duration AS sql_variant)),
('resource_semaphore_id', CAST(up.resource_semaphore_id AS sql_variant)),
('queue_id', CAST(up.queue_id AS sql_variant)),
('wait_order', CAST(up.wait_order AS sql_variant)),
('is_next_candidate', CAST(up.is_next_candidate AS sql_variant)),
('wait_time_ms', CAST(up.wait_time_ms AS sql_variant)),
('request_sql_handle', CAST(up.request_sql_handle AS sql_variant)),
('memory_grant_sql_handle', CAST(up.memory_grant_sql_handle AS sql_variant)),
('request_plan_handle', CAST(up.request_plan_handle AS sql_variant)),
('memory_grant_plan_handle', CAST(up.memory_grant_plan_handle AS sql_variant)),
('group_id', CAST(up.group_id AS sql_variant)),
('pool_id', CAST(up.pool_id AS sql_variant)),
('is_small', CAST(up.is_small AS sql_variant)),
('ideal_memory_kb', CAST(up.ideal_memory_kb AS sql_variant)),
('most_recent_session_id', CAST(up.most_recent_session_id AS sql_variant)),
('connect_time', CAST(up.connect_time AS sql_variant)),
('connect_duration', CAST(up.connect_duration AS sql_variant)),
('net_transport', CAST(up.net_transport AS sql_variant)),
('protocol_type', CAST(up.protocol_type AS sql_variant)),
('protocol_version', CAST(up.protocol_version AS sql_variant)),
('encrypt_option', CAST(up.encrypt_option AS sql_variant)),
('auth_scheme', CAST(up.auth_scheme AS sql_variant)),
('node_affinity', CAST(up.node_affinity AS sql_variant)),
('num_reads', CAST(up.num_reads AS sql_variant)),
('num_writes', CAST(up.num_writes AS sql_variant)),
('last_read', CAST(up.last_read AS sql_variant)),
('last_read_duration', CAST(up.last_read_duration AS sql_variant)),
('last_write', CAST(up.last_write AS sql_variant)),
('last_write_duration', CAST(up.last_write_duration AS sql_variant)),
('net_packet_size', CAST(up.net_packet_size AS sql_variant)),
('client_net_address', CAST(up.client_net_address AS sql_variant)),
('client_tcp_port', CAST(up.client_tcp_port AS sql_variant)),
('local_net_address', CAST(up.local_net_address AS sql_variant)),
('local_tcp_port', CAST(up.local_tcp_port AS sql_variant)),
('parent_connection_id', CAST(up.parent_connection_id AS sql_variant)),
('most_recent_sql_handle', CAST(up.most_recent_sql_handle AS sql_variant)),
('host_process_id', CAST(up.host_process_id AS sql_variant)),
('client_version', CAST(up.client_version AS sql_variant)),
('security_id', CAST(up.security_id AS sql_variant)))
AS dData(ColumnName, ColumnValue)) upv



SELECT rs.session_id,
ib.EventInfo AS [InputBuffer],
sm.SQLStmt AS [Statement],
st.text AS [Batch],
(SELECT Outval FROM Util.dbo.CastXMLInline (ib.EventInfo)) AS InputBufferXML,
(SELECT Outval FROM Util.dbo.CastXMLInline (sm.SQLStmt)) AS StatementXML,
(SELECT Outval FROM Util.dbo.CastXMLInline (st.text)) AS BatchXML,
qp.query_plan
FROM sys.dm_exec_requests rs (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(rs.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(rs.sql_handle) st
LEFT OUTER JOIN @InputBuffer ib ON 1 = 1
CROSS APPLY (SELECT CASE WHEN rs.statement_start_offset IN (0, -1)
AND rs.statement_start_offset = 0 THEN NULL
WHEN (rs.statement_end_offset > rs.statement_start_offset
OR rs.statement_end_offset = -1)
AND st.text <> ''
THEN LTRIM(RTRIM(SUBSTRING(st.text, rs.statement_start_offset / 2, (CASE WHEN rs.statement_end_offset = -1 THEN 9999999
ELSE rs.statement_end_offset
END - rs.statement_start_offset) / 2 + 1)))
ELSE NULL
END AS SQLStmt) sm
WHERE rs.session_id = @SPID;
GO
EXEC sys.sp_MS_marksystemobject
sp_session
GO
gvarol@corelogic.com
 
Posts: 63
Joined: Wed Mar 09, 2011 9:43 pm
Location: USA

Postby Brian Donahue » Mon Apr 16, 2012 4:53 pm

Thanks for reporting this. The query does the same thing on my installation and the debugger tells me it's a stack overflow. It's just a case where a lot of recursive functions are executed, probably because of a complex chain of SQL object dependencies.

I'll log a bug about it but I'm not sure it can be fixed easily.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Prompt 5

Who is online

Users browsing this forum: No registered users and 0 guests