January 7, 2008 at 10:06 am
I'm working on a multi-tier reporting solution that requires a reporting services data source that points at a linked server hosted on a SQL Server 2005 server. The linked server points at a DB2 database.
How do I setup a reporting services data source to access a linked server database?
Ultimately, we will have reporting services running on it's own server, that will be pointing at our SQL Server 2005 Cluster. We have a linked server on that cluster that points at a backend mainframe DB2 source. I need to be able to write reports against tables in that DB2 Linked server.
I have to setup data sources for report developers in Reporting Services to make it easy for them to write reports against the DB2 linked server tables.
How do I set this up?
We will likely be using OPENQUERY to write queries against the linked server.
I'm working on my local XP developer workstation. I'm using Visual Studio 2005 to write reports and create data sources that will be published to our Reporting Services server.
Can anyone tell me how to do this?
January 8, 2008 at 9:51 am
I am in the same boat. Any feedback would be appreciated.
December 14, 2011 at 8:57 am
Me 3. Any help??
December 14, 2011 at 9:05 am
I always use stored procedures as my dataset query in SSRS. If you do it this way, you will be able to use OpenQuery within the SP with no problem.
If this doesn't work for you, you might be able to use synonyms, I am not sure since I have never tried. The only other alternative I can think of is to set up some kind of mirroring, replication, or updating process from your DB2 linked server to a SQL Server database and tables.
December 14, 2011 at 9:34 am
Yeah thanks Dan, got it going with a synonym and OPENQUERY
SELECT * FROM OPENQUERY(Sybase, 'select location from location') AS A INNER JOIN Branch AS B
ON A.location = B.BranchCode
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply