?
Solved

not able to get full text of query

Posted on 2013-01-18
2
Medium Priority
?
711 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 2000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Detach & Attach 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.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

771 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