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 Job Execution

Is there a way to execute SQL Server jobs (Not the jobs
created by DTS packages or Maintenance Plans. Just jobs
created with --> Create new Job...)remote (Trough a batch
file, third party software like tivoli schedular)?
Thanks.
Use OSQL the command line utility to run execute stored procedure
sp_start_job.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:98aa01c433ac$97e9f420$a301280a@.phx.gbl...
> Is there a way to execute SQL Server jobs (Not the jobs
> created by DTS packages or Maintenance Plans. Just jobs
> created with --> Create new Job...)remote (Trough a batch
> file, third party software like tivoli schedular)?
> Thanks.
>

Remote Job Execution

Is there a way to execute SQL Server jobs (Not the jobs
created by DTS packages or Maintenance Plans. Just jobs
created with --> Create new Job...)remote (Trough a batch
file, third party software like tivoli schedular)?
Thanks.Use OSQL the command line utility to run execute stored procedure
sp_start_job.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:98aa01c433ac$97e9f420$a301280a@.phx.gbl...
> Is there a way to execute SQL Server jobs (Not the jobs
> created by DTS packages or Maintenance Plans. Just jobs
> created with --> Create new Job...)remote (Trough a batch
> file, third party software like tivoli schedular)?
> Thanks.
>

Remote Job Execution

Is there a way to execute SQL Server jobs (Not the jobs
created by DTS packages or Maintenance Plans. Just jobs
created with --> Create new Job...)remote (Trough a batch
file, third party software like tivoli schedular)?
Thanks.Use OSQL the command line utility to run execute stored procedure
sp_start_job.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:98aa01c433ac$97e9f420$a301280a@.phx.gbl...
> Is there a way to execute SQL Server jobs (Not the jobs
> created by DTS packages or Maintenance Plans. Just jobs
> created with --> Create new Job...)remote (Trough a batch
> file, third party software like tivoli schedular)?
> Thanks.
>

Remote insert

I have an issue i need to understand and have not been able to find an answer yet. It may be something to do with parametised query execution but i`m not sure yet. Below is the scenario

If i do a

insert into server.db.dbo.remotetable
select * from dbo.localtable

and the local select returns say 3 values, 3 inserts will occur on the destination server where as if the insert into references a local table only 1 insert would occur!

Why? Is it possible to get the remote query to behave like a local and do the 3 records in 1 insert?

To test this i've supplied some very simple code. Just create source tbl on local server and destination and log tbl on remote server. Setup a linked server and run the remote insert then do a local insert and look at the log.

All advise gratefully received!

Cheers

Andrew

CREATE TABLE [dbo].[source] (
[server] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[dt] [datetime] NULL
) ON [PRIMARY]
GO

--Create these on the destination server
CREATE TABLE [dbo].[tbllog] (
[Server] [char] (100) COLLATE Latin1_General_CI_AS NULL ,
[tst_Count] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[destination] (
[server] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[dt] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TRIGGER [destination_ins] ON [dbo].[destination]
FOR INSERT, UPDATE, DELETE
AS
insert into dbo.tbllog
select server,count(server) from inserted group by server
GO

--Insert some sample data into the source table
insert into source values ('MYSERVER','1/1/2000')
insert into source values ('MYSERVER','1/2/2000')
insert into source values ('MYSERVER','1/3/2000')

--Run the insert from the source db

insert into destinationsrv.destdb.dbo.destination
select * from source

--Switch to the destination server and run select there is only meant to be 1 row!
select * from tbllogNews to me. I'll try it out when I get to my office.|||Thanks blindman|||I'm dealing with the same problem. Has anyone found a solution? I'd hate to resort to calling a remote stored procedure just to pull data across the link.

Surely people have encountered this problem before - I'm able to reproduce it in both Sql Server 2000 and 2005. Or do people normally only use linkedservers for queries?

Remote DTSX execution, when sql25k fails.

Hi everyone,

This is my snippet of code. It works fine with remote DTSX but when they have got connections against Sql Server 2005, ending given the following error:

Event: (On error)

12:21:52,-1071611876,0x,Error de la llamada del mtodo AcquireConnection al administrador de conexión "LOCALHOST.BDAplis"

That means more or less "Error on calling method AcquireConnection to Connection Manager localhost...

It's curious. I've got other packages which are using Sql Server 2000 connections without errors.

Thanks for your help,

pkgResults = pkg.Validate(Nothing, Nothing, EventsSSIS, Nothing)

If pkgResults = DTSExecResult.Success Then

pkg = app.LoadPackage(ActObject.packageName, Nothing, True)

endif

pkg.InteractiveMode = False

pkgResults = pkg.Execute()

Is this really remote execution? SSIS is really a client side tool. The user trying to make the connection to the SQL server, through a SQL connection in the package, is the user running your code. So who is running your code, and do they have rights to connect to your SQL Server? I would check the SQL server error log. Good practice says we have set the server level security option to log failed connections, and that should tell you who the user is. Alternatively run a SQL Server profiler trace against the server concerned, and select the login failed event. When the failure happens, you should see the event in the profiler output, and can see who the user is.|||

Hi DarrenSQLIS,

Thanks for you reply.

Ok, my user domain is doing the connection but how to select for LoadPackage(,,,) method another user, an Administrator user, for example.

Otherwise loadfromsqlserver offer the possibility to define user and password.

thanks