locked
Linked server problem RRS feed

  • Question

  • Hi All,

    We are facing problem in to retrieving data by linked server.

    What we have done, we are using 3 servers, first have data and second have procedure and in third server we need to execute this procedure.

    Please let me know how we can do it by using linked server.

    Thanks,

    Brij 

    Thursday, June 14, 2012 3:17 PM

Answers

  • Hi,

    Just to re-iterate

    Server_A >Data

    Server_B> SPs

    Server_C> you want to invoke the SPs on Server_B from Server_C.

    Is that you are trying to do? Again are you accessing data on the server_A in the stored Procs on Server_B?

    if yes, all your processing will be very slow. Its always better to do processing on the same server where the data resides.

    You can invoke a SP at linked server using the command 

    exec AdventureWorks.dbo.uspGetBillOfMaterials AT LinkedServerName

    Regards
    satheesh

    • Marked as answer by Maggie Luo Sunday, July 8, 2012 5:09 PM
    Tuesday, June 19, 2012 11:12 AM

All replies

  • Hi,

    I am not able to understand much from the post. why you are keeping data on one server, SPs on another and you want to execute the SP on third ? whats the point in doing such design?

    and of course you cannot execute a SP from one server on another, one possibility is creating synonyms on third server for all stored procs on second.

    Regards
    satheesh

    Thursday, June 14, 2012 3:52 PM
  • Hi,

    You can execute code via a linked server by using the four part qualified name: NameOfLinkedServer.NameOfDataBase.NameOfSchema.NameOfProcedure.

    However, I think Brij has asked some interesting questions.

    Why have you got data on one server and procedures on another?

    Seth


    http://lqqsql.wordpress.com

    Monday, June 18, 2012 3:34 PM
  • Are you using the 4 part key for your linked server ? It should be a full qualitfied Naming convention

    Name of the linked Server. Database. Schema . object.

    Tuesday, June 19, 2012 4:28 AM
  • I am using 4 part key for your linked server. Actually this is our project requirement and client wants like that, i know it would not correct way but whatever client said.

    like,   Name of the linked Server. Database. Schema . object 

    Thanks,

    Brij

    Tuesday, June 19, 2012 11:01 AM
  • Hi,

    Just to re-iterate

    Server_A >Data

    Server_B> SPs

    Server_C> you want to invoke the SPs on Server_B from Server_C.

    Is that you are trying to do? Again are you accessing data on the server_A in the stored Procs on Server_B?

    if yes, all your processing will be very slow. Its always better to do processing on the same server where the data resides.

    You can invoke a SP at linked server using the command 

    exec AdventureWorks.dbo.uspGetBillOfMaterials AT LinkedServerName

    Regards
    satheesh

    • Marked as answer by Maggie Luo Sunday, July 8, 2012 5:09 PM
    Tuesday, June 19, 2012 11:12 AM