how to find out the sp query in the middle of execution

• balaji.ganga 68339

  Right there with Babe

  Points: 755

  More actions

  March 16, 2011 at 1:46 am

  #96187

  Hi,

  i 'm having sp. it consits of update,select, insert query. i have executed this sp with parameter.

  example: sp_name : exec abc '03/15/2011'

  I just want to know, which query is executing in this sp.

  i used to print ''.. but it shows the same output.. exec abc '03/15/2011' result only.

  i don't want that result.

  Could anyone knows, help me

  Regards

  Balaji G

• grahamc

  SSCertifiable

  Points: 6762

  More actions

  March 16, 2011 at 4:59 am

  #1299268

  You will need to either put "breaks" into the stored procedure or run the statements separately.

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  March 17, 2011 at 10:52 am

  #1300076

  You can get this via fn_get_sql (if you have the right SP, it's one of the hotfixes on sp2 or included in SP3). You can also use profiler.

  http://phelabaum.com/archive/2010/03/dbcc-inputbuffer-vs-fn-get-sql/[/url]

  If you're on 2005 or later, you can get this info via DMV's.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• george sibbald

  SSC Guru

  Points: 104210

  More actions

  March 17, 2011 at 11:11 am

  #1300087

  here's some code what I pinched from somewhere -

  --How to isolate the current running commands in SQL Server. This query isolates the SQL in the batch

  -- actually running at this point rather than the last command to execute

  SELECT SDER.[session_id], SDER.[request_id],SDER.[statement_start_offset],

  SDER.[statement_end_offset],

  CASE

  WHEN SDER.[statement_start_offset] > 0 THEN

  --The start of the active command is not at the beginning of the full command text

  CASE SDER.[statement_end_offset]

  WHEN -1 THEN

  --The end of the full command is also the end of the active statement

  SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, 2147483647)

  ELSE

  --The end of the active statement is not at the end of the full command

  SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, (SDER.[statement_end_offset] - SDER.[statement_start_offset])/2)

  END

  ELSE

  --1st part of full command is running

  CASE SDER.[statement_end_offset]

  WHEN -1 THEN

  --The end of the full command is also the end of the active statement

  RTRIM(LTRIM(DEST.[text]))

  ELSE

  --The end of the active statement is not at the end of the full command

  LEFT(DEST.TEXT, (SDER.[statement_end_offset]/2) +1)

  END

  END AS [executing statement],

  DEST.[text] AS [full statement code]

  FROM sys.[dm_exec_requests] SDER CROSS APPLY sys.[dm_exec_sql_text](SDER.[sql_handle]) DEST

  WHERE SDER.session_id > 50

  ORDER BY SDER.[session_id], SDER.[request_id]

  ---------------------------------------------------------------------