Connecting to different servers from stored procedures

• paucann

  SSC Eights!

  Points: 912

  More actions

  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

• Tejas Shah

  Old Hand

  Points: 308

  More actions

  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

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  April 20, 2009 at 11:17 pm

  #981200

  You can also use linked servers.

  "Keep Trying"

• mverma4you

  Ten Centuries

  Points: 1013

  More actions

  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

• paucann

  SSC Eights!

  Points: 912

  More actions

  April 22, 2009 at 8:43 am

  #982235

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