xp_ExecResultSet

Question

Post reply

xp_ExecResultSet

scporich

SSC-Addicted

Points: 493
More actions
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.

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

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

philcart SSC-Forever Points: 47795 More actions · Answer 1

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

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

scporich SSC-Addicted Points: 493 More actions · Answer 2

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.

katesl SSCommitted Points: 1743 More actions · Answer 3

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.."

cliffb SSCarpal Tunnel Points: 4598 More actions · Answer 4

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.

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 5

Another rewrite for SQL 2005 was done by Adam Machanic at

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

Scott Thornburg