Custom datasource for a Reporting Services report

[TheBugSlayer]

Hi. I have a situation where the resultset I need to produce gathers its data from too different sources: one is a table on a SQL Server 2008 database and the other one a table from SQL Server 2005 database. I have SA privileges on the later. I am think CLR stored procedure or Reporting Services Custom Data Extensions with the latter being heavily favored. I did a little research on it but examples are sketchy. I have .NET applications that display ReportViewer reports with data comming from a DLL, fairly easy. But it seems a bit more complicated when the report is to be published on a Report Server. Can anyone provide some guidance, an example or point me to some useful sites?

Thanks much.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[zcompguy]

Hi BugSlayer,
Not sure if this will help you out or not, but here goes...

I write many reports using SSRS and a report server, and 99% of all required data resides on 3 seperate servers (all SQL 2000). What I have done is setup a server with SQL 2008 on it, and I have linked the required servers to it, and then write sp's to collect the data for me for the report. The same could be done with your 2 servers, setting one as your main, and linking the other server to it.

I was thinking that this may be of some help to you. SSRS does not mind because only 1 resultset is used.

Just a thought...good luck.

Chester aka zCompGuy

 

[TheBugSlayer]

Hi Zester. Thank you very much for your suggestion, which makes a lot of sense...as long as the server you are linking from has the higher version. My problem is precisely that I only have read access on the SQL 2008 server but I am an admin on the SQL 2005 server. Unless they are forward-compatible which I doubt...

Thank you again.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TheBugSlayer]

Right now I am writing a DLL that' ll give me the data I want. I am using .NET Framework 3.5 with two connections, one to each DB, and using LINQ for the queries...Now I need to format that resultset per

http://www.devx.com/dbzone/Article/31336/1954

but something is missing somewhere...I can' t find where to download the example they are referring to.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)