Question for Chad and any Undocumented T-SQL Gurus

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Question for Chad and any Undocumented T-SQL Gurus

Author

Topic

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-28 : 09:14:11

I found this undocumented SP in Ken's Guru's Guide/SPs, XML, HTML book:

xp_execresultset

I tried running the example he has in the book on a named instance of SQL 2000:

exec master..xp_execresultset 'SELECT ''PRINT ''test''', 'pubs'

And I get the following error message:

Server: Msg 50007, Level 16, State 1, Line 0
xp_execresultset: unable to get a bound connection back to server

Is this because it's a named instance? I can't test it on a default instance, I was hoping if you could shed some light on this.

Also, does this SP exist in SQL 7.0? It wasn't mentioned in the earlier Guru's Guide, but Ken said he left out a number of undocumented procedures anyway.

Thanks!

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-02-28 : 10:22:21

He mentioned it in his presentation also. I tested it with the same results on a default instance. I've actually tested it on two different servers with no luck.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-01 : 13:32:08

Thanks graz.

Has anyone been able to run this successfully, or have any more information on it?

Thanks.

chadmat
The Chadinator

1974 Posts

Posted - 2002-03-01 : 14:26:44

I don't know much about this XP.

but, based on this article: Q281093

it looks like it was in 7.0

What is the expected result Rob?

It looks like this XP is used in replication.

-Chad

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-01 : 17:07:28

According to Ken, it will accept a SELECT statement and execute the results as a SQL command. It's meant to be used for queries that are greater than 8000 characters. Ken's example wasn't demonstrative of the actual results. I was looking to test it out and play with it from there, but this error stopped me dead.

Have you been able to run the code I posted Chad? That's what was in the book. Is there any more information on the error message?

I found that xp_execresultset is used by these system SPs:

sp_addsynctriggers
sp_articleview
sp_getqueuedrows
sp_MScreatebeforetable
sp_MSdefer_check
sp_MSgen_sync_tran_procs
sp_MSgetconflictinsertproc
sp_MSgettrancftsrcrow
sp_MSmakearticleprocs
sp_MSmakeconflictinsertproc
sp_MSmakeconflicttable
sp_MSmakeexpandproc
sp_MSmakeinsertproc
sp_MSmakesystableviews
sp_MSmaketrancftproc
sp_MSmakeupdateproc
sp_MSmakeviewproc
sp_MSpublicationview
sp_MSsetartprocs

They all seem to be replication related and all undocumented.

I know it's probably not worth using xp_execresultset anyway, but it offers some pretty tantalizing features.

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-03-02 : 13:06:16

Well, if you do get it working, be sure to try


create table #t (a varchar(255),b varchar(255),c varchar(255))
go
insert #t exec ('dbcc inputbuffer(@@spid)')
update #t set c='master..xp_execresultset '''+ c+ ''',''pubs'''
xp_executeresultset 'select c from #t','pubs'
go
drop table #t

Cheers
-b

chadmat
The Chadinator

1974 Posts

Posted - 2002-03-04 : 12:34:44

Rob,

I get the same result regardless of whether it is on a named instance, or default instance.

-Chad

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-04 : 12:38:22

Same result...error message on both?

or

Same result...worked fine on both?

izaltsman
A custom title

1139 Posts

Posted - 2002-03-04 : 13:17:55

I get error message on both... As well as on SQL7

chadmat
The Chadinator

1974 Posts

Posted - 2002-03-04 : 14:56:46

Same result as you. Error message.

-Chad

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-06 : 11:39:58

OK!!!! A year later I seem to be getting somewhere with this XP!

I ran the following in my SQL 7 Query Analyzer:

exec master..xp_execresultset N'SELECT ''''PRINT ''test''', N'master'

And got a nice message "The command(s) completed successfully." WITH NO RESULTS!!

It should've output the word "test" but it didn't.

Could someone please try the above command on their own servers and let me know if you get any results? You can also try this variation:

exec master..xp_execresultset N'SELECT ''''PRINT ''test''', N'pubs'

I discovered that the original syntax did not have the N' prefix to denote nvarchar data type, and that is what caused the "unable to get bound connection" error. Now I'm no longer getting an error, but I'm still not getting any restuls!

I had also run DBCC TRACEON(3604) thinking it might have an effect, but it doesn't seem to.

Thanks all!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-06 : 11:42:33

Also, if anyone is looking for other examples using this XP:

http://www.rac4sql.net/xp_execresultset.asp

I haven't tested that one as I don't have the Nortwind DB at work. If anyone can try that and get results, PLEASE LET ME KNOW!

Thanks again.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-06 : 12:33:10

This gives me "test" as output
exec master..xp_execresultset N'SELECT ''''PRINT ''test''', N'pubs'

This generates 9 csv files
Use Northwind
go
-- Create a global temp table
-- to hold our SQL BCP statements
SELECT DISTINCT 'EXEC master..xp_cmdshell ''BCP "SELECT * FROM Northwind..Orders WHERE Employeeid=' + cast(employeeid as varchar) + '" queryout "C:\Temp\' + cast(employeeid as varchar) + '.csv" -w -t"|" -T -S"' + @@servername +'"''' AS sql
INTO ##TMP
FROM Orders
SELECT sql FROM ##TMP
-- Execute the resultset
-- based on our temp table
EXEC master..xp_execresultset N'SELECT sql FROM ##TMP', N'Northwind'

-- Clean up
DROP TABLE ##TMP

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-03-06 : 12:49:09

All I get is "The command(s) completed successfully."

SQL 7, minimal SP's...maybe be on #3....but we're not aggressive in implementing SP's so I don't think it'd be higher.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-06 : 14:48:24

OK, I assume you're using SQL 2000 Nigel, and that probably explains why Andrew and I aren't getting anything returned.

Thanks guys!

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-06 : 17:30:55

Yep.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources