Executing a Stored Procedure from a View

[dusanv]

Hello,

I tried to follow a workaround from some book for executing sp's from a view and created a linked server entry called LOOPBACK on my server that refers back to itself. Then I tried to create a view that has this statement:

SELECT * FROM OPENQUERY(LOOPBACK,'EXEC dbo.sp_test')

However, I am getting an error that dbo.sp_test stored procedure cannot be found even though it does exists in the database. Any insight on this -- thanks.

 

[tlbroadbent]

Which database is the SP in? Try adding the database name to the query.

SELECT * FROM OPENQUERY(LOOPBACK,'EXEC dbname.dbo.sp_test')
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[dusanv]

Yes, supplying the database name solved the problem. The stored procedure is in the same db as the one I'm creating a view in, so I didn't think supplying a database name was not neccessary. Thanks.