Red Gate forums :: View topic - sql prompt crashing on formatting script
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt 5
SQL Prompt 5 forum

sql prompt crashing on formatting script

Search in SQL Prompt 5 forum
Post new topic   Reply to topic
Jump to:  
Author Message
gvarol@corelogic.com



Joined: 09 Mar 2011
Posts: 63
Location: USA

PostPosted: Fri Apr 13, 2012 2:48 am    Post subject: sql prompt crashing on formatting script Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6648

PostPosted: Mon Apr 16, 2012 4:53 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group