Solved

not able to get full text of query

Posted on 2013-01-18
2
674 Views
Last Modified: 2013-01-30
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
Comment
Question by:25112
  • 2
2 Comments
 
LVL 8

Expert Comment

by:venk_r
ID: 38795091
Is this on sql 2005 or 2008?
0
 
LVL 8

Accepted Solution

by:
venk_r earned 500 total points
ID: 38795184
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question