Calling remote Stored Procedure's

Hi,
I am experiencing some difficulties when it comes to executing a SQL
Stored procedure on a remote SQL server.
I have two SQL servers.
1st Server: Named 'SQLServer' running on Win2k Server (SP4)
2nd Server: Named 'SQLClient' running on Win2k Profession (SP4)
When I try to execute the Stored Procedure 'Ten Most Expensive
Products' (from the Northwind database), from my client machine
(SQLClient), on the server machine (SQLServer).
For example.
execute OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=sa;PWD=;').[Northwind].[dbo].[Ten
Most Expensive Products]
I get the following error.
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'Ten Most Expensive Products' on remote
server 'SQLOLEDB'.
However if I simply query a table directly (from the remote machine
'SQLClient'), all works fine. For example, the Customers table from
the Northwind database.
select * from OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=sa;PWD=;').[Northwind].[dbo].Customers
I also tried executing the stored procedure from 'SQLServer', and that
worked fine too. eg.
execute OpenDataSource('SQLOLEDB',N'SERVER=SQL;UID=sa;PWD=;').[Northwind].[dbo].[Ten
Most Expensive Products]
I think the problem may have something to do with RPC permissions. Can
anyone shed some light on why this doesn't work, and/or how to fix it.
Thanks in advance.
Rick 8-)Rick
EXEC sp_serveroption SERVER, 'data access' , 'true'
select *
from OPENQUERY(SERVER,'exec Northwind.dbo.[Ten Most Expensive Products]')
Note: I assume you have already created linked server.
"Rick Knight" <knight_rjb@.yahoo.com.au> wrote in message
news:4b3eabf7.0411071804.6933fe62@.posting.google.com...
> Hi,
> I am experiencing some difficulties when it comes to executing a SQL
> Stored procedure on a remote SQL server.
> I have two SQL servers.
> 1st Server: Named 'SQLServer' running on Win2k Server (SP4)
> 2nd Server: Named 'SQLClient' running on Win2k Profession (SP4)
> When I try to execute the Stored Procedure 'Ten Most Expensive
> Products' (from the Northwind database), from my client machine
> (SQLClient), on the server machine (SQLServer).
> For example.
> execute
OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=sa;PWD=;').[Northwind].[dbo
].[Ten
> Most Expensive Products]
> I get the following error.
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'Ten Most Expensive Products' on remote
> server 'SQLOLEDB'.
> However if I simply query a table directly (from the remote machine
> 'SQLClient'), all works fine. For example, the Customers table from
> the Northwind database.
> select * from
OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=sa;PWD=;').[Northwind].[dbo
].Customers
> I also tried executing the stored procedure from 'SQLServer', and that
> worked fine too. eg.
> execute
OpenDataSource('SQLOLEDB',N'SERVER=SQL;UID=sa;PWD=;').[Northwind].[dbo].[Ten
> Most Expensive Products]
> I think the problem may have something to do with RPC permissions. Can
> anyone shed some light on why this doesn't work, and/or how to fix it.
> Thanks in advance.
> Rick 8-)|||Hi,
Maybe I should give some background on the problem. I discovered a
problem when trying to get data to replicate from the Subscriber back
to the Publisher. I was able to determine that the Subscribers update
trigger (trg_MSsync_upd_<tablename>) was failing to execute the Update
Stored Procedure (sp_MSsync_upd_<tablename>_1) on the Publisher.
I used the Profiler, and determined that the trigger was failing when
executing the command;
exec @.retcode = OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=sa;PWD=;').[<databasename>].[dbo].[sp_MSsync_del_<tablename>_1]'
...
And since I didn't want to re-code the triggers automatically created
by SQL, I tried to figure out why the execution of the remote stored
procedure wasn't working.
BTW: With regard to my original post, I found out that if I try and
execute a Stored Procedure from Server (SQLServer) to the client
(SQLClient), it worked fine. So the only problem is going from Client
to Server!
I hope this sheds more light on the problem.
Thanks in advance.
Rick 8-)
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<#l31CxVxEHA.1392@.tk2msftngp13.phx.gbl>...
> Rick
> EXEC sp_serveroption SERVER, 'data access' , 'true'
> select *
> from OPENQUERY(SERVER,'exec Northwind.dbo.[Ten Most Expensive Products]')
> Note: I assume you have already created linked server.
>