Executing a Stored Procedure against a Linked server (W/O putting thesp on the Linked Server)

robboll (robbollathotmaildotcom) writes:
> How can I run the statement: EXEC SERVER_C master.dbo.usp_mySP without
> putting usp_mySP on SERVER_C?

You can't.

I'm a little curious why you even expect this to be possible. The
procedure does not exist, so how would you be able to execute it?

Yes, there is one in server_A and one in server_B. And there is
a Statue of Liberty in New York and one in Las Vegas. Now you are
asking the question "how do to I get to the Statue of Liberty in
Seattle?"




--
Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On 2011-04-20 23:37, Erland Sommarskog wrote:
> Yes, there is one in server_A and one in server_B. And there is
> a Statue of Liberty in New York and one in Las Vegas. Now you are
> asking the question "how do to I get to the Statue of Liberty in
> Seattle?"
>
By going to Alki Beach Park. :-)

If only stored procedures were as widely replicated.

--
J.

Well I thought it is possible to execute stored procedure usp_mySP on
SERVER_A against SERVER_C since all it's doing is querying the system
tables on SERVER_C.

I mean, If I can run from SERVER_A: Select * from SERVER_C.
[SERVER_C_DB].DBO.SERVER_C_TABLE

why cant it get that syntax from the stored procedure. It's
essentially doing the same thing?

I really thought this was possible.


On Apr 20, 4:37*pm, Erland Sommarskog wrote:
> robboll (robb...athotmaildotcom) writes:
> > How can I run the statement: EXEC SERVER_C master.dbo.usp_mySP without
> > putting usp_mySP on SERVER_C?
>
> You can't.
>
> I'm a little curious why you even expect this to be possible. The
> procedure does not exist, so how would you be able to execute it?
>
> Yes, there is one in server_A and one in server_B. And there is
> a Statue of Liberty in New York and one in Las Vegas. Now you are
> asking the question "how do to I get to the Statue of Liberty in
> Seattle?"
>
> --
> Erland Sommarskog, SQL Server MVP, esq...atsommarskogdotse
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Wed, 20 Apr 2011 23:37:39 +0200, Erland Sommarskog
wrote:

>robboll (robbollathotmaildotcom) writes:
>> How can I run the statement: EXEC SERVER_C master.dbo.usp_mySP without
>> putting usp_mySP on SERVER_C?
>
>You can't.
>
>I'm a little curious why you even expect this to be possible. The
>procedure does not exist, so how would you be able to execute it?
>
>Yes, there is one in server_A and one in server_B. And there is
>a Statue of Liberty in New York and one in Las Vegas. Now you are
>asking the question "how do to I get to the Statue of Liberty in
>Seattle?"

I can not believe you missed such an easy one:
ren "Space Needle" "Statue of Liberty"
-- although you might have to suffix with a number -- and retry.

Sincerely,

Gene Wirchenko

robboll (robbollathotmaildotcom) writes:
> Well I thought it is possible to execute stored procedure usp_mySP on
> SERVER_A against SERVER_C since all it's doing is querying the system
> tables on SERVER_C.
>
> I mean, If I can run from SERVER_A: Select * from SERVER_C.
> [SERVER_C_DB].DBO.SERVER_C_TABLE
>
> why cant it get that syntax from the stored procedure. It's
> essentially doing the same thing?
>
> I really thought this was possible.

The call

EXEC Server_C.master.dbo.usp_mySP

works if there is a usp_mySP in the master database on Server_C. But if
there is not, the call fails.

The statement

SELECT * FROM Server_C.master.dbo.some_table

works if there is a table some_table in master database on Server_C,
but if there is not, it fails.

So, yes, they are the same.

You can of course write a stored procedure on Server_A which accesses data
on Server_C:

CREATE PROCEDURE my_sp AS
SELECT * FROM Server_C.master.dbo.some_table

But that was not how I interpreted your initial question.

I'm still uncertain what you really want to do. Maybe you could take one
step to the beginning, and explain what you are trying to achieve.

--
Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx