Calling remote Stored Procedure's

From: Rick Knight (knight_rjb_at_yahoo.com.au)
Date: 11/08/04 

Date: 7 Nov 2004 18:04:45 -0800

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-)

Relevant Pages

  • Stored procedure does not complete until result set is retrieved from ODBC
    ... I have a SQL Server Stored procedure that I am executing via ODBC. ... -- Start Code without cursor ...
    (microsoft.public.sqlserver.odbc)
  • Re: Limitations of Scheduled jobs
    ... > Actually we can schedule the jobs with accuracy less than ... where executing the stored procedure ... >>on what the stored procedure does exactly. ... >>SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: remotely call a DTS
    ... only time this stored procedure hsa worked was when I ran it with the E ... Ideally I would like to run the DTS using the U and P switches with the ... P and E switches are for you to be able to access the SQL Server to ... My basic problem is calling a DTS from Excel on a remote machine. ...
    (microsoft.public.sqlserver.dts)
  • RE: Login failed for user foo but the SQL executed correctly
    ... Could you post the detailed error message when executing the stored ... and see under which user the stored procedure is executed. ... Is the error thrown by the stored procedure or the SQL Server 2000? ... | for user 'foo' error. ...
    (microsoft.public.sqlserver.security)
  • Re: error executing sprocedure
    ... Wayne Snyder, MCDBA, SQL Server MVP ... I've created one stored procedure that has one input ... executing this procedure sql reports one error. ... input paramenter to the stored procedure but if the number ...
    (microsoft.public.sqlserver.server)