none
write an view to fetch data from remote server RRS feed

  • Question

  • i have an project running on my system but for some data i need to fetch data through intranet by writing a view

    how to do it???

    Tuesday, February 7, 2012 8:34 AM

Answers

  • Hi Heman_t,

    Regarding the description, as Manish_BI mentioned you can create a Linked server to fetch data from remote server. As far as accomplishing this inside of a view, you would first need to set up a linked server. For more information, please refer to sp_addlinkedserver

    Then the sample of  inside the view as below:
    CREATE VIEW TestView 
    AS 
    SELECT * -- the columns that you needed
    FROM MyRemoteServer.RemoteDB.RemoteSchema.RemoteTable1 t1 
    JOIN MyRemoteServer.RemoteDB.RemoteSchema.RemoteTable1 t2 ON t1.SomeField = t2.SomField 
    WHERE ---SuitableWhereClause
    
    Meanwhile you can consider SSIS packages, which used to connect to the two servers and transfer data. For more information, please refer to SQL Server Integration Services.

    Regards, Amber zhang

    Thursday, February 9, 2012 3:12 AM
    Moderator

All replies

  • Hemant,

    Using the Linked server you can access the DB of other server, then you can create your view on the other.


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, February 7, 2012 8:53 AM
  • Hi Heman_t,

    Regarding the description, as Manish_BI mentioned you can create a Linked server to fetch data from remote server. As far as accomplishing this inside of a view, you would first need to set up a linked server. For more information, please refer to sp_addlinkedserver

    Then the sample of  inside the view as below:
    CREATE VIEW TestView 
    AS 
    SELECT * -- the columns that you needed
    FROM MyRemoteServer.RemoteDB.RemoteSchema.RemoteTable1 t1 
    JOIN MyRemoteServer.RemoteDB.RemoteSchema.RemoteTable1 t2 ON t1.SomeField = t2.SomField 
    WHERE ---SuitableWhereClause
    
    Meanwhile you can consider SSIS packages, which used to connect to the two servers and transfer data. For more information, please refer to SQL Server Integration Services.

    Regards, Amber zhang

    Thursday, February 9, 2012 3:12 AM
    Moderator
  • Hi Heman_t,

    Adding info.

    If you can use SQL Server Standard Edition or above then you have alternative for using SQL Replication / Mirroring. It copies remote data to your local system by sql agent job when transaction occur. You can have real time data. You can choose what table to be replicated and also it has filtering capabilities. Replication / Mirroring is recomended for reporting.

    In term of performance, I think it is better since data is copied to your local system. SQL Server Express Edition does not have these functions.

    Regards, Agung G

    My application developer journal

    Monday, February 13, 2012 9:01 AM