Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 784
  • Last Modified:

not able to get full text of query

both the below statements are not bringing the full content of the query back..

(it brought 44k characters, but then truncated it) 43679 exactly..

SELECT DISTINCT cast(TEXT as varchar(max)) FROM sys.sysprocesses OUTER APPLY SYS.DM_EXEC_SQL_TEXT(0x01000E0036A22110001927D00300000000000000)

SELECT cast(TEXT as varchar(max)) FROM ::fn_get_sql(0x01000E0036A22110001927D00300000000000000)

for a query bigger than that, is it possible to capture it with the new DMVs or other SQL Server feature?

INPUTBUFFER also has that limitation, it seems like

thanks
0
25112
Asked:
25112
  • 2
1 Solution
 
venk_rCommented:
Is this on sql 2005 or 2008?
0
 
venk_rCommented:
You can use the below query to grab the sql .It will also give you other helpful informations  like login ,timetaken to run etc.

SELECT  p.session_id, start_time, p.status,p.cpu_time,p.total_elapsed_time, --original_login_name,
              (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,( (CASE WHEN statement_end_offset = -1
                   THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
                   ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,host_name,program_name        FROM sys.dm_exec_requests p
      CROSS APPLY sys.dm_exec_sql_text(sql_handle)  s2 inner join sys.dm_exec_sessions s on s.session_id=p.session_id
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now