Communication between two database servers

  • Hi,

    I am very amature in this perticular topic, please help me out.

    I want to write procedure on one database server, which have to select data from table residing on other database server. I know this is possible but I didn't understand how to do that.

    Can some one please explain me with some example. I mean some code.

    Please 🙂

    Also is there other way around that I can use, if so please help !

    Abhy

  • Check Books Online

    Linked Server

    OPENROWSET

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • Hi Jerry,

    thanks for your reply. But I am not clear about linked server post available on MSDN and online.

    Can you please direct me to specific example code which uses this sp_linkedserver procedure and performs some task???:w00t:

    Please help me with this issue.

    Thanks

  • abhi777_aiwa (1/8/2009)


    Hi,

    I am very amature in this perticular topic, please help me out.

    I want to write procedure on one database server, which have to select data from table residing on other database server. I know this is possible but I didn't understand how to do that.

    Can some one please explain me with some example. I mean some code.

    Please 🙂

    Also is there other way around that I can use, if so please help !

    Abhy

    Therefore u have crerate link server or use openrowset

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • hi,

    as per paresh told just create linked server and after that you can interact with there server

    do u know how to linked server if you don't know then reply me with what sql server version r u using

    Raj Acharya

  • raj acharya (1/9/2009)


    hi,

    as per paresh told just create linked server and after that you can interact with there server

    do u know how to linked server if you don't know then reply me with what sql server version r u using

    To add link serevrs:

    USE master;

    GO

    EXEC sp_addlinkedserver 'P01', N'SQL Server'

    GO

    USE [master]

    GO

    EXEC master.dbo.sp_serveroption @server=N'P01', @optname=N'data access', @optvalue=N'true'

    GO

    USE [master]

    GO

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'P01', @locallogin = NULL , @useself = N'False', @rmtuser = N'dba', @rmtpassword = N'dba'

    GO

    ------------

    to add link server to excel

    EXEC sp_addlinkedserver 'ExcelShare',

    'Jet 4.0',

    'Microsoft.Jet.OLEDB.4.0',

    '\\MyServer\MyShare\Spreadsheets\DistExcl.xls',

    NULL,

    'Excel 5.0'

    SELECT * FROM OPENQUERY(ExcelShare, 'SELECT * FROM [Sheet1$]')

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply