Local table and OpenQuery

I created a stored procedure like this:

CREATE PROCEDURE SP
AS
BEGIN
CREATE TABLE #T( C INT )
INSERT INTO #T(C) VALUES (1)
SELECT * FROM #T
END

When I call it this way: EXEC SP, it works ok.

But when I do it like this:
SELECT * FROM OPENQUERY( MYSERVER, 'EXEC SP')
I receive an error: Invalid object name '#T'
Why?...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Evgeny Gopengauz <evgop@.ucs.ru> wrote in message news:<4119d7ae$0$14493$c397aba@.news.newsgroups.ws>...
> I created a stored procedure like this:
> CREATE PROCEDURE SP
> AS
> BEGIN
> CREATE TABLE #T( C INT )
> INSERT INTO #T(C) VALUES (1)
> SELECT * FROM #T
> END
> When I call it this way: EXEC SP, it works ok.
> But when I do it like this:
> SELECT * FROM OPENQUERY( MYSERVER, 'EXEC SP')
> I receive an error: Invalid object name '#T'
> Why?...
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

It looks like this is another multi-posted question - if you want
feedback from different groups, then please post to all of them at
once with a single posting. Having said that, you might find Erland's
recent explanation in another thread useful:

http://groups.google.com/groups?hl=...es.ms-sqlserver

Also, if you are trying to do something with the output of a stored
procedure, this article (also from Erland) should point you in the
right direction:

http://www.sommarskog.se/share_data.html

Simon|||Simon! Thank you for your references, I just found the accurate solution
for my troubles.

Concerning with my mutli-posting... I'm sorry but I have no an ability
to post to the comp.databases.ms-sqlserver and
microsoft.public.sqlserver.programming simultaneously (at the same
message) because this conference is available for me through
www.developersdex.com, but microsoft.public through NNTP. Excuse me
please, hope it will never happen again, I will use a single conference
for each single quiestion.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!