Wow - time flies, I really can't beleive its been 3 weeks since I last looked at this blog. To follow up on some of the feedback I got, here is a summarey of the post I was discussing. Its a re-hash of the actual posting from OJ, but it should give you some idea.

My problem was that any stored proc that uses temp tables wouldn't return a column list when queried from a linked server, which in turn might be used as part of a view. In the following example we have to turn that behaviour of by calling set fmtonly on first. Also, the sp can return as many resultsets it wants. However, only the first will be accepted and redirected by the view. The example shows both of these facts in 'action'.
 
e.g.
 
use tempdb
go
create proc usp
as
set nocount on
create table #tmp1(OrderID int, CustomerID sysname)
create table #tmp2(EmployeeID int, Freight money)
 
insert #tmp1
select top 1 OrderID,CustomerID
from Northwind..Orders
 
insert #tmp2
select top 1 EmployeeID,Freight
from Northwind..Orders
 
select * from #tmp1
select * from #tmp2
go
 
use Northwind
go
create view _v
as
select *
from openquery(myserver,'set fmtonly off; exec tempdb..usp')x
go
select * from _v
go
 
drop view _v
go
exec tempdb..sp_executesql N'drop proc usp'
go
 

Openquery() opens a new connection for distributed query based on the current_user authentication/profile. By default, every login's default database is master (unless you over-ride it). Thus, the distributed connection will work under master database context. If you want to invoke an user-object (i.e. view/sp/udf/etc) from master
database, you will have to qualify the database. Note that system objects (stored procedures, functions, etc.) can be called from any database, hence you wouldn't have to qualify them.

Thanks to all

Ryan