Post reply

extracting data from two servers to make a report

  • November 20, 2007 at 5:24 am

    #377094

    hello all

    i am writing for help.

    I have two servers say A and B, i need to get the turnover and profit from a table called "bookings" in server - A and get the customer infomration from a table "Customer" and purchase product details from table - "enquiry" both found in server - B

    target is to make a report detailing the name, address, total sale, turnover and profit for a period of time.

    any help in this regard would be appreciated.

  • November 20, 2007 at 6:06 am

    #752633

    You first need to setup linked servers, and then access the linked server in the same fashion...

    SELECT * FROM ServerA.SomeDB.SomeOwner.SomeTable

    --Ramesh

  • November 20, 2007 at 7:54 am

    #752711

    thanks ramesh, but the problem is that the servers are different and hence i might have to provide DSN (opendatabaseconnection()) thats where the problem kicks in.

  • November 20, 2007 at 8:03 am

    #752716

    You could do this with linked servers as suggested. Look for linked servers in BOL for some details on how this works. This will allow you to write queries that span multiple servers.

    I am not a fan of linked servers for several reasons. Most of them come from the fact that they performed poorly and could cause stability issues. These problems have all been fixed in SQL 2005 (most were fixed in sp4 of SQL 2000), so I should re-evaluate my stance on this point.

    However, if you are interested in doing this without a linked server, you can use SSIS as the source for a report. This allows you to use multiple data sources and all of the transformations you want without linked servers and their limitations.

    This is done using a DataReader destination - look for this on Google and I am sure you will find some articles on how to set it up. Once it is configured, it is pretty easy to implement.

  • November 20, 2007 at 8:12 am

    #752722

    Desperate newbie (11/20/2007)

    thanks ramesh, but the problem is that the servers are different and hence i might have to provide DSN (opendatabaseconnection()) thats where the problem kicks in.

    You just need to create a linked server using sp_addlinkedserver procedure

    EXEC sp_addlinkedserver

    @server='TestServer',

    @srvproduct='',

    @provider='SQLNCLI',

    @datasrc='YourServer\InstanceName'

    Then, create linker server login

    EXEC sp_addlinkedsrvlogin 'TestServer', 'false', 'sa', 'sa', 'password'

    Thats it.

    Now query the server,

    select * from TestServer.master.dbo.sysobjects

    --Ramesh

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

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