Solved

not able to get full text of query

Posted on 2013-01-18
2
693 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

690 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