April 20, 2009 at 5:36 pm
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
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
April 20, 2009 at 11:17 pm
You can also use linked servers.
"Keep Trying"
April 21, 2009 at 3:55 am
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
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