none
Parameters were not supplied for the function 'sys.dm_exec_sql_text'. RRS feed

  • Question

  • When I try to run the following query: select * from sys.dm_exec_sql_text

    I get: Parameters were not supplied for the function 'sys.dm_exec_sql_text'.

    Version: Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Standard

    This works on all my other SQL 2005 boxes.
    • Moved by Tom PhillipsModerator Tuesday, November 10, 2009 1:41 PM TSQL Question (From:SQL Server Database Engine)
    Tuesday, November 10, 2009 1:16 PM

Answers

  • the DM needs the parameter it errors out to you

    sys.dm_exec_sql_text(sql_handle | plan_handle)

    http://technet.microsoft.com/en-us/library/ms181929.aspx

    typical usage
    select *
    from  sys.dm_exec_requests as req
    cross apply sys.dm_exec_sql_text (req.sql_handle)
    I would also check the history.  memory serves me correct, this DM changed on SPs
    http://technet.microsoft.com/en-us/library/ms181929(SQL.90).aspx


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    Tuesday, November 10, 2009 1:26 PM
    Moderator
  • <<Defaults the plan_handle to NULL I believe from memory

    Functions do not allow default values... stored procedures do.  You have to specify a parameter value to execute the function; however, specifiying a value of null is pointless, as the function will never return any rows.  You should be using this function along with other DMVs to get a query's text.


    SELECT *
    FROM sys.[dm_exec_connections]
    CROSS APPLY sys.dm_exec_sql_text([most_recent_sql_handle])


    Edit:
    -----------------------------
    Here is a sample of how a function still requires param values, even if a default is configured.


    USE [tempdb]
    GO
    
    IF object_id('tempdb.dbo.test') IS NOT NULL 
    BEGIN
    	DROP FUNCTION dbo.test;
    END
    GO
    
    CREATE FUNCTION dbo.test(@someid INT = 1) --Default value specified
    RETURNS TABLE
    RETURN(
    	SELECT @someid AS col
    )
    GO
    
    SELECT * FROM dbo.test() --throws an error when no parameters are specified
    





    http://jahaines.blogspot.com/
    Tuesday, November 10, 2009 3:20 PM
    Moderator

All replies

  • the DM needs the parameter it errors out to you

    sys.dm_exec_sql_text(sql_handle | plan_handle)

    http://technet.microsoft.com/en-us/library/ms181929.aspx

    typical usage
    select *
    from  sys.dm_exec_requests as req
    cross apply sys.dm_exec_sql_text (req.sql_handle)
    I would also check the history.  memory serves me correct, this DM changed on SPs
    http://technet.microsoft.com/en-us/library/ms181929(SQL.90).aspx


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    Tuesday, November 10, 2009 1:26 PM
    Moderator
  • Post build 1399 allowed the parm to not be specified.  Defaults the plan_handle to NULL I believe from memory.  I do not have a build that low to test but I believe that was why it allowed you not to pass anything.  After 3042 or so they added the sql or plan so you had to have the handle (and could not be null also I believe)

    correction: not Post build 1399 but 1399  build levels.  typed that wrong.  apologies


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    Tuesday, November 10, 2009 3:02 PM
    Moderator
  • <<Defaults the plan_handle to NULL I believe from memory

    Functions do not allow default values... stored procedures do.  You have to specify a parameter value to execute the function; however, specifiying a value of null is pointless, as the function will never return any rows.  You should be using this function along with other DMVs to get a query's text.


    SELECT *
    FROM sys.[dm_exec_connections]
    CROSS APPLY sys.dm_exec_sql_text([most_recent_sql_handle])


    Edit:
    -----------------------------
    Here is a sample of how a function still requires param values, even if a default is configured.


    USE [tempdb]
    GO
    
    IF object_id('tempdb.dbo.test') IS NOT NULL 
    BEGIN
    	DROP FUNCTION dbo.test;
    END
    GO
    
    CREATE FUNCTION dbo.test(@someid INT = 1) --Default value specified
    RETURNS TABLE
    RETURN(
    	SELECT @someid AS col
    )
    GO
    
    SELECT * FROM dbo.test() --throws an error when no parameters are specified
    





    http://jahaines.blogspot.com/
    Tuesday, November 10, 2009 3:20 PM
    Moderator
  • select *
    from  sys.dm_exec_requests
    I looked up the incorrect DM object as shown above.  My bad!


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    Tuesday, November 10, 2009 4:32 PM
    Moderator
  • Maybe you guys could help figure this one out. This vbscript works on some of my SQL Servers, not on others:

    Set objParameters = WScript.Arguments
    sManagedInstance = objParameters(0)
    Set objParameters = Nothing
    
    iWaitInMinutes = 10
    iAlertFreq = 10
    
    SCRIPT_SQL = _
            "SET NOCOUNT ON; " & _
            "" & _
            "CREATE TABLE #blocks " & _
            "( " & _
    	    "   SPID SMALLINT " & _
            ",	BlockingSPID SMALLINT " & _
            ",	WaitTime BIGINT " & _
            ",	LastWaitType VARCHAR(32) " & _
            ",	WaitResource VARCHAR(32) " & _
            ",	DBName VARCHAR(100) " & _
            ",	CPU_Time INT " & _
            ",	Total_Elapsed_Time INT " & _
            ",	Physical_IO BIGINT " & _
            ",	Logical_Reads BIGINT " & _
            ",	Memory_Usage INT " & _
            ",	Login_Time DATETIME " & _
            ",	[Status] VARCHAR(30) " & _
            ",	HostName VARCHAR(128) " & _
            ",	ProgramName VARCHAR(128) " & _
            ",	Command VARCHAR(16) " & _
            ",	NT_User_Name VARCHAR(128) " & _
            ",	Login_Name VARCHAR(128) " & _
            ",	SQL_Text VARCHAR(1000) " & _
            "); " & _
            "" & _
            "INSERT INTO " & _
    	    "   #blocks " & _
            "SELECT " & _
    	    "   R.session_id " & _
            ",	R.blocking_session_id " & _
            ",	R.wait_time " & _
            ",	R.last_wait_type " & _
            ",	R.wait_resource " & _
            ",	DB_NAME(R.database_id) " & _
            ",	R.cpu_time " & _
            ",	R.total_elapsed_time " & _
            ",	R.reads + R.writes " & _
            ",	R.logical_reads " & _
            ",	S.memory_usage " & _
            ",	S.login_time " & _
            ",	R.status " & _
            ",	S.[host_name] " & _
            ",	S.[program_name] " & _
            ",	R.command " & _
            ",	S.nt_user_name " & _
            ",	S.login_name " & _
            ",	LEFT(H.text, 1000) " & _
            "FROM " & _
            "	sys.dm_exec_requests R " & _
            "INNER JOIN " & _
            "	sys.dm_exec_sessions S " & _
            "ON " & _
            "	R.session_id = S.session_id " & _
            "CROSS APPLY  " & _
            "	sys.dm_exec_sql_text(R.sql_handle) H " & _
            "WHERE " & _
            "	R.session_id > 50 " & _
            "	AND  " & _
            "	( " & _
            "		(R.blocking_session_id <> 0 AND R.wait_time > " & CStr(iWaitInMinutes*60) & " * 1000 )" & _
            "		OR  " & _
            "		(R.blocking_session_id = 0 AND R.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 )) " & _
            "	) " & _
            "ORDER BY  " & _
            "	R.blocking_session_id;" & _
            "" & _
            "IF EXISTS (SELECT * FROM #blocks WHERE BlockingSPID > 0) " & _
            "BEGIN " & _
    	    "   SELECT * FROM #blocks " & _
    	    "   WHERE BlockingSPID > 0 " & _
    	    "   OR SPID IN (SELECT BlockingSPID FROM #blocks WHERE BlockingSPID > 0); " & _
            "END " & _
            "ELSE " & _
            "BEGIN " & _
    	    "   TRUNCATE TABLE #blocks; " & _
    	    "   SELECT * FROM #blocks; " & _
            "END " & _
            "" & _
            "DROP TABLE #blocks; "
    
    Set cnManagedInstance = CreateObject("ADODB.Connection")
    cnManagedInstance.Provider = "sqloledb"
    cnManagedInstance.ConnectionTimeout = 30
    sConnString = "Server=" & sManagedInstance & ";Database=master;Trusted_Connection=yes"
    
    cnManagedInstance.Open sConnString
    Set rsBlockedSPIDS = cnManagedInstance.Execute(SCRIPT_SQL)
    
    Do While Not rsBlockedSPIDS.EOF
        
        .... DO SOMETHING....
        rsBlockedSPIDS.MoveNext
        
    Loop

    I get an error which fails on the line of the query execution:
    Microsoft OLE DB Provider for SQL Server: Incorrect syntax near '.'. which is pointing to the line of code:
    Set rsBlockedSPIDS = cnManagedInstance.Execute(SCRIPT_SQL)
    What's weird is that if I ran the query itself in Management Studio while creating the locks manually, the query works and I am able to get information returned from the temp table. For whatever reason, in script format, the query doesn't work.
    Thursday, November 12, 2009 10:25 AM
  • This isn't a new post - it is a continuation of the same issue I had that I originally posted for. I was trying to debug this script and was trying to just run the portion that failed: "select * from sys.dm_exec_sql_text

    Since that didn't prove positive I decided to return to the problem. So, my question was "answered" but the "problem" wasn't "solved". Do as you wish.
    Thursday, November 12, 2009 12:23 PM