Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    5

    Unanswered: Currently processed SQL Query.

    Is there any way to obtain the SQL query that is currently executed from
    within SQL Server?

    Thanks.

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Post Re: Currently processed SQL Query.

    Q1 Is there any way to obtain the SQL query that is currently executed from within SQL Server? Thanks.
    A1 Yes several ways, for example, you may execute sp_Who in query analyzer (check the status and cmd columns of the result set); or see / refresh "current activity" in EM, or even use profiler. Example for sp_Who:

    exec sp_Who

  3. #3
    Join Date
    Nov 2002
    Posts
    5
    That, i exactly want to have is a string variable with the sql query that is
    currently executed, in order to use it in a trigger. Is there such a global
    variable in SQL Server or can i obtain this from a function or smth? Can
    i do this with sp_who? If yes how can i retrieve the cmd column of it's resulting set?

    Thank you very much and sorry for not explaining well my question in
    my first post?

  4. #4
    Join Date
    Oct 2002
    Posts
    369

    Question


    Q1 That, i exactly want to have is a string variable with the sql query that is currently executed, in order to use it in a trigger. Is there such a global variable in SQL Server or can i obtain this from a function or smth?
    Q2 Can i do this with sp_who? If yes how can i retrieve the cmd column of it's resulting set?
    Q3 Thank you very much and sorry for not explaining well my question in my first post?
    A1 There is no global variable I know of that holds the currently executing stored procedure / tsql as a whole. The best option for capturing such information in its entirety on the fly is probably to use profiler (it should be possible with trace flags as well, but that would be more involved). Note: It is possible for stored procedures to have large output variables (nvarchar / varchar) that could potentially hold and pass 4000 to 8000 chars to a calling procedure (which may or may not address your needs, but you would in any case have issues with procedures exceeding 8000 chars in length using that approach).

    A2 Not with any current release, it is possible to use sp_who to identify the desired currently active connection (and then view the input buffer for that connection, to see the 'currently executing string'), however, that only gives the first 255 char currently in the input buffer.

    A3 You are welcome.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •