Post reply

Views based on tables on diff servers

  • December 4, 2012 at 9:21 am

    #265738

    Hi All,

    Could you please let me know the possibilities of creating a view based on a table that exists in a different server.

    Thanks

  • December 4, 2012 at 9:26 am

    #1565393

    its certainly possible, you'd probably want to create a linked server to the other server, and then create the view with a full four part naming convention.

    CREATE VIEW

    AS

    SELECT ColumnList

    FROM MyLinkedServer.DatabaseName.dbo.TableName

    Where SomeColumn = SomeCriteria

    now be warned: linked servers can be SLOW, especially if the tables on the linked server are huge...data usually gets copied over to the temp db, and then filtered. that's defintiely the case if you join that linked table to some local table...all the data is copied, then joined, then filtered, then returned as the results of a query.

    If you know the data doesn't change much, I'd suggest replicating a copy to a local table instead, and refreshing it on some regular interval.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

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