Selecting columns from a system sproc

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)

Selecting columns from a system sproc

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-10-19 : 05:51:49

Morning all

I know it is not good form to query system tables directly, therefore, is it possible to select only certain parts of the results of executing a system stored procedure and store the results in a table variable or temporary table?

For example, I want to return and retain a list of all the db servers using sp_helpserver (the [name] attribute). I have tried a couple of approaches (for example, select [name] from (exec sp_helpserver), or dynamic sql) but have not yet come up with a solution. Ideas please......

Hearty head pats

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-19 : 05:55:45

first you add a linded server. it's you server that you link to yourself.
it's like a table joined to it self.
then you execute the sproc from the linked server with
openquery into a temp table then you can query the temp table.
you need to add the linked server only once.


-- add 'loopback' linkedserver 
if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
    @srvproduct = N'',
    @provider = N'SQLOLEDB', 
    @datasrc = @@servername
go
select * into #t from openquery(loopback, 'exec sp_helpserver) 
select * from #t
drop table #t
go

Go with the flow & have fun! Else fight the flow

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-10-19 : 06:16:32

Hey Spirit

Thanks for replying. When I try and execute the sproc, I get the following error message:

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'exec sp_helpserver'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.

Hearty head pats

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-10-19 : 07:26:39

Editing my post...

I'm totally off base

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-10-19 : 07:41:48

I think you don't need to go as far as setting up a linked server, although I am at a loss why you get that error.

Just create a temp table with the same structure as the proc output.

Create Table #t (
[name] varchar(200),
network_name varchar(200),
status varchar(200),
id int,
collation_name varchar(20),
connect_timeout int,
query_timeout int
)

INSERT #t
exec sp_helpserver

select * from #t
drop table #t
go

However, I really don't have a problem with querying system tables, just don't try to update them... and make sure you test before upgrading to sql2005.

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-10-19 : 11:16:48

Thanks Merkin! Been a bit distracted at the moment - been pulled off on two other projects, both of which are not working - arghhh! It never rains, it pours!

Cheers for the advice though guys! You've always come up trumps!

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-10-19 : 11:27:08

By the way, why did you say editing 'my' post? Are you and Spirit1 infact the same person?

Hearty head pats

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-19 : 11:47:33

lol... no we aren't.

after seeing your error i asked merkin if he had any ideas why it's happening.
he thought it was front end ADO and set nocount on problem.
then he edited it as it wasn't.
and since you know the columns you want to see before you can easily create the table before calling the sproc.

Go with the flow & have fun! Else fight the flow

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-10-19 : 11:55:22

Ah, I seeee! There's my conspiracy theory out the window! Well thank you both! No doubt I will be back here with another problem soon! Right, i am off home! See ya later!

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources