Remote-server execution of a global temp stored procedure

I have the following execution of a global temporary stored procedure on a remote SQL 2000 server:

insert into targetTable
exec remoteServer.master.dbo.sp_MSforeachdb ' ', @.precommand = 'exec ##up_fetchQuery'

This is an ugly duck query but it seems to work fine. when I try to directly execute the remote stored procedure such as with

insert into query_log exec remoteServer.master.dbo.##up_fetchQuery

I get execution error

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
Database name 'master' ignored, referencing object in tempdb.

When I try

insert into query_log exec remoteServer.tempdb.dbo.##up_fetchQuery

I get

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
Database name 'tempdb' ignored, referencing object in tempdb.

with

insert into query_log exec remoteServer..dbo.##up_fetchQuery

or

insert into query_log exec remoteServer...##up_fetchQuery

I get

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.

I guess the remote server has trouble resolving the name of the global temp stored procedure when its reference comes in as a remote stored procedure calls. Is there any way to directly call a global temp stored procedure from a remote server or do I need to stick with this goofy-looking work-around?

Dave

You can do below instead of using the undocumented system SP "sp_MSforeachdb".

insert into targetTable
exec remoteServer.master.dbo.sp_executesql N'exec ##up_fetchQuery'

|||

Thank you. (Laughing at myself; I thought of everything except the obvious; oh, brother!)

It definitely helps to have another set of eyes look at something.

Dave

Remote Stored Procedure exec from variable

Hello Folks,

I have a server that is linked to several other servers. I have been able to successfully execute a procedure manually to each from the common server by executing this:

exec server1.dbname.owner.procedure
go
exec server2.dbname.owner.procedure
go
exec server3.dbname.owner.procedure
go

While this is ok, I'd like to wrap this in a loop to execute the procedure but switch out the server name in each iteration (something like this):

while @.variable is not null
begin
select @.server = 'change name in loop'
select @.str = @.server+'.dbname.owner.procedure'
exec @.str
end

This unfortunately does not work. The execute is acting like the server portion of the name does not exist (defaulting to local). I have attempted to use the AT SERVERNAME syntax in a similar fashion and been unsuccessful.

Is there some way I could dynamically create the four part name and execute it?

Any assistance would be greatly appreciated.

Thanks, Mark

DECLARE @.server nvarchar(128)

DECLARE @.cmd nvarchar(1000)

SET @.server = 'MyServer'

SET @.cmd = 'EXEC ' + @.server + '.MyDatabase.MySchema.MySproc'

EXEC (@.cmd)