how to call a sp from within a sql statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

how to call a sp from within a sql statement?

Author

Topic

00kevin
Yak Posting Veteran

78 Posts

Posted - 2006-08-17 : 14:31:10

I've tried this code but it just gives me an error. "Server is not setup for DATA_ACCESS"

SELECT * FROM OPENQUERY(LOCALSERVER, 'EXEC sp_who') WHERE status = 'runnable'

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-17 : 14:33:48

you have to add localserver to linked servers.

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

blog thingie: http://weblogs.sqlteam.com/mladenp

00kevin
Yak Posting Veteran

78 Posts

Posted - 2006-08-17 : 14:44:13

is there another way to call a stored procedure without using openquery or a linked server?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 14:49:03

Is the stored procedure on a different server?

Tara Kizer

00kevin
Yak Posting Veteran

78 Posts

Posted - 2006-08-17 : 14:51:56

no. the stored procedure is on LOCALSERVER.

everything is run from the same database and the same server. I don't understand why I would have to create a linked server to itself

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-17 : 14:55:08

because it works like that


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_who') 
select * from #t
drop table #t
go

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

blog thingie: http://weblogs.sqlteam.com/mladenp

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 14:55:33

You don't. And you don't need to use OPENQUERY. You posted the OPENQUERY part in your first post which led us to believe that the stored procedure was on a remote server.

Could you explain what you want to do with the stored procedure?

Tara Kizer

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 14:57:34

spirit1's post is only needed if you don't know what the column names are that are returned from the stored procedure. Otherwise, you just do this:

CREATE TABLE ...

INSERT INTO ...
EXEC StoredProcNameGoesHere

sp_who is a static stored procedure, so you do know what the column names are. Don't use a linked server or openquery for this.

Tara Kizer

00kevin
Yak Posting Veteran

78 Posts

Posted - 2006-08-17 : 15:09:44

I know the column names that will be returned.

At my crazy company we have a reporting tool that only makes use of sql statements. I wrote a stored procedure to return the results for a report that is rather complicated. I don't want to create a temp table and do the inserts.

To make things simple, I guess i will just have to link the server to itself. I just hope that doesn't cause any problems.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 15:17:30

Ummm, the linked server approach requires a table too.

Tara Kizer

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-17 : 15:20:10

acctually it doesn't.... it can return the just the query results.

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

blog thingie: http://weblogs.sqlteam.com/mladenp

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 15:25:46

quote:
Originally posted by spirit1

acctually it doesn't.... it can return the just the query results.

Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp

I meant if he wants to work with the data returned which I assume he does based upon his subject.

Tara Kizer

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-17 : 15:27:28

ok

i understood that he just want's the query results returned to the reporting tool...

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

blog thingie: http://weblogs.sqlteam.com/mladenp

00kevin
Yak Posting Veteran

78 Posts

Posted - 2006-08-17 : 16:52:57

well I tried linking the server in 2005 using the management studio but it doesn't let you. It tells you that you can't use a local server as a linked server.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 16:56:42

quote:
Originally posted by 00kevin

well I tried linking the server in 2005 using the management studio but it doesn't let you. It tells you that you can't use a local server as a linked server.

Check out spirit1's post. He doesn't use localserver. He is using an alias to the local machine.

Tara Kizer

00kevin
Yak Posting Veteran

78 Posts

Posted - 2006-08-17 : 17:00:33

sp_addlinkedserver worked

making a linked server from the studio doesn't. I event tried just using the IP but then openquery didn't work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources