Post reply

xp_ExecResultSet

  • October 12, 2005 at 9:14 pm

    #67579

    Is it just me, or has MS removed xp_ExecResultSet from 2005?  And yes, I enabled the Surface Area Configuration for OLE automation and xp_cmdshell.

    When I run this:

    use master

    grant execute on xp_ExecResultSet to PUBLIC

    I am getting this in Profiler:

    Error: 15151, Severity: 16, State: 1

    Cannot find the object 'xp_ExecResultSet', because it does not exist or you do not have permission.

    I looked through all the xp items in master, and it does not seem to be there...

    I know it was an undoc, but it was SO USEFUL!!!  Has anyone else tried to get this to work?  It looks like I may have to do some recoding.

  • October 12, 2005 at 10:47 pm

    #596999

    Hence the mantra ... "Do not use/rely on any undocumented system objects"

     

    --------------------
    Colt 45 - the original point and click interface

  • October 13, 2005 at 6:34 am

    #597049

    You are absolutely correct about undocs - And I have even heard it coming out of my mouth.  It must have been a moment of weakness at some point... What does the Good Book say?  "For whoever shall keep the whole law, and yet stumble in one point, he is guilty of all." (James 2:10)

    Regardless of my indescresion, has anyone else tried this, seen this, etc.?  I just want to know if I am missing anything technical, or if I am just a victim of my own sword and will have to write it using documented t-sql.

  • December 6, 2006 at 7:40 am

    #676216

    I posted on this last spring---

     

    Thanks for mentioning that the xp_execresultset is missing from SQL Server 2005.

    Yikes!  Before retiring your SQL Server 2000 servers, do this--

    1) Copy proc sp_execresultset from the master database of any server, and create

    it in the master database of the SQL Server 2005 server.  (The xp_execresultset

    is unike other xp_ procs; it isn't compiled and reading it shows that it calls

    sp_execresultset.)

    2) Copy proc xp_execresultset from the master database of any server, and create

    it in the master database of the SQL Server 2005 server, named sp_execresultset2.

    (Doesn't work if you name it xp_ -- and, proc can be named sp_ to be called

    from databases other than master without "exec master.." -- at least that

    is true in SQL Server 2000.)

    3) Edit the script to refer to sp_execresultset2 in place of master.dbo.xp_execresultset.

    _________________
    "Look, those sheep have been shorn."
    data analyst replies, "On the sides that we can see.."

  • February 20, 2007 at 12:52 pm

    #690731

    After examining the procedure sp_execresultset, I decided to write a cleaner port.

    USE master

    GO

    CREATE PROCEDURE sp_execresultset

          @SQLToGetRecordSet nvarchar(max)

          ,@DatabaseToExecuteCodeIn sysname

          ,@debug bit = 0

    AS

    DECLARE @SQL nvarchar(max)

    DECLARE @AlteredSQL nvarchar(max)

    SET @SQL='USE ' + @DatabaseToExecuteCodeIn + '

    DECLARE @DynamicSQL nvarchar(max)

    DECLARE @tbl TABLE(SQL nvarchar(max))

    SET @DynamicSQL = ''''

     

    INSERT INTO @tbl(SQL)

    ' + @SQLToGetRecordSet + '

     

    SELECT @DynamicSQL = @DynamicSQL + SQL FROM @tbl

     

    EXEC sp_ExecuteSQL @DynamicSQL'

     

    IF @debug=0

          EXEC sp_ExecuteSQL @SQL

    ELSE

          PRINT @SQL

    GO

    DO NOT USE IN PRODUCTION WITHOUT PROPER TESTING! I just wrote this code and have only done some minor testing with it, but it appears to function the same as the 2000 code. I'm open to critique but wanted to give something back to the community as I have found numerous useful pieces of information here.

  • February 20, 2007 at 10:20 pm

    #690809

    Another rewrite for SQL 2005 was done by Adam Machanic at

    http://sqljunkies.com/WebLog/amachanic/archive/2006/10/19/24218.aspx

     

    Scott Thornburg

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply