SEO

#1 (**permalink**) 02-29-2008, 03:54 AM

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!

#2 (**permalink**) 02-29-2008, 03:54 AM

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=e...s.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

#3 (**permalink**) 02-29-2008, 03:54 AM

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!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Local table and OpenQuery