locked
How to get the name of the script that is currently running RRS feed

  • Question

  • I'd like to get the name of the sql query when it that is running
    so I can use this information and save in a table.

    i.e.

    /*** Script Name test.sql ***/
    DECLARE @current_script_name VARCHAR(60);

    SET @current_script_name = SELECT ??? FROM ????

    The value that would be returned is test.sql or test

    I am not familiar enough to know whether dynamic views would
    work for this or if I have to use a filename?

    Thanks for your help.

    Brian
    Friday, October 16, 2009 7:48 PM

Answers

  • SQL Server knows nothing of a *file* that you pasted into SSMS or passed to OSQL.EXE/SQLCMD.EXE. 
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Marked as answer by Kalman Toth Friday, October 16, 2009 8:03 PM
    Friday, October 16, 2009 7:50 PM
  • OK thanks, no need to try writing the complex script, I understand the answer.

    • Marked as answer by Brian Dowling Friday, October 16, 2009 8:13 PM
    Friday, October 16, 2009 8:13 PM

All replies

  • SQL Server knows nothing of a *file* that you pasted into SSMS or passed to OSQL.EXE/SQLCMD.EXE. 
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Marked as answer by Kalman Toth Friday, October 16, 2009 8:03 PM
    Friday, October 16, 2009 7:50 PM
  • Is there any way of getting the name of the current job that is running
    within a script - other than just the ID of the job?

    Friday, October 16, 2009 8:05 PM
  • I don't understand.  Can you give an example of how this might work?

    There is no such thing as a script to SQL Server.  It doesn't see that level of detail - it only sees the Transact-SQL statements that get sent to it.

    In other words, you won't be able to select from anything to get this information.

    You can probably write a complex SQL statement to return the name of a SQL Server Agent job that is currently executing the statement, but I'd have to try it first.
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Friday, October 16, 2009 8:08 PM
  • OK thanks, no need to try writing the complex script, I understand the answer.

    • Marked as answer by Brian Dowling Friday, October 16, 2009 8:13 PM
    Friday, October 16, 2009 8:13 PM