Post reply

Connecting to different servers from stored procedures

  • April 20, 2009 at 5:36 pm

    #130719

    Hello,

    I hope this is the right forum.

    I'm trying to write a stored proc where I query one set of tables from one database and another set from another database for reconciliation. The problem is that each db is on a different server.

    I won't be writing data, just doing a read and displaying in a report.

    I'm guessing that this is some connection parameter that I can set in the SP. Or can I set this in the SSRS report itself?

    Paul

  • April 20, 2009 at 11:10 pm

    #981197

    Hi,

    You can use OpenRowSet/OpenQuery to get data from different database located on different server.

    You can write query as:

    SELECTa.*

    FROMa

    INNER JOIN OPENROWSET('SQLOLEDB','ServerAddress';'UserName';'Password',

    'select * from Table

    ') b

    ON a.ID = b.ID

    Note: It might get an error that "Ad Hoc Distributed Queries" is disabled.

    You can enable it by this way:

    Let me know if it helps you in any way.

    Thanks,

    Tejas

    Tejas Shah

  • April 20, 2009 at 11:17 pm

    #981200

    You can also use linked servers.

    "Keep Trying"

  • April 21, 2009 at 3:55 am

    #981272

    execute this before executing the open rowset query.

    EXEC sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    EXEC sp_configure 'Ad Hoc Distributed Queries', 1;

    GO

    RECONFIGURE;

    GO

  • April 22, 2009 at 8:43 am

    #982235

    I'll try these options .....thanks....

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

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