Freigeben über

SQL Server Reporting Services interop

  • Artikel

SQL Server Reporting Services is nearly ubiquitous as SQL Server itself, it is in nearly all versions of SQL Server from Express (with Advanced services) to DataCenter edition and will soon be available in SQL Azure.   It’s been around for 8 years and if Microsoft used traditional version numbers it would now be on V5.  This wide spread availability and long history does cause some confusion when trying to work out what works with what.

So here are the FAQs I commonly see and get asked about :

FAQ 1. Can Reporting Services report on data stored in [ insert relevant database platform here]?

Although Reporting Services is part of SQL Server it can consume data from pretty well any structured data source e.g Excel, xml, and anything you can get an ADO.Net,OLEDB or ODBC connector for. To design your reports you’ll need these database connectors (drivers) on the machine you’re designing the reports on and on the server they’ll be run from.  

Also don’t forget that if your source data is in a different version of SQL Server that doesn’t matter either, providing you again use the right connector/drivers.

FAQ 2. Can I design a report in the SQL Server 2XXX and then run it on Server 2YYY?

A report can generally be run on a later version of SQL server than it was designed for (unless you have custom code or rare authentication or security setup). Once you save it to the newer version of reporting services it will be automatically updated. However you can never do this the other way around i.e. run a report on an older version an the one it was designed in.   This is because the report definition language (RDL) gracefully changes in each release, so that older reports can run.

Other resources on upgrading to SQL Server 2008 R2 can be found here

FAQ 3 Can I use and older version of SQL server to host the reporting server databases

**

Reporting services uses 2 SQL Server databases, one to hold the metadata about the reports and the other as a temporary workspace. You can use a different version of SQL Server to store these databases and the interop matrix looks like this

SQL Server database Reporting Services version Compatibility
2000 2005 OK
2005 2005 OK
2005 2008 OK
2005 2008 R2 OK
2008 2008 OK
2008 2008 R2 OK

 

FAQ 4. Which versions of Reporting Services work with which versions of SharePoint?

SharePoint version Reporting Services version Compatibility
2007 2005 OK
2007 2008 OK
2007 2008 R2 OK
2010 2005 Not possible
2010 2008 OK, but you actually use the 2008 R2 reporting services add-in for SharePoint (actually this part of the SharePoint installation) and 2008 needs to be at Service pack1 cumulative update 8
2010 2008 R2 OK

 

FAQ 5. Which versions of Visual Studio (VS) work with which version of Reporting Services?

When SQL Server 2005 came out it introduced the BI development studio (BIDS) which is essentially a cut down version of Visual Studio in that case 2005. When SQL Server 20087 came out BIDS was built on VS2008. However (BIDS) in SQL Server 2008 R2 is also still built on VS 2008 , but it’s simple to have another version alongside e.g. VS 2010.

FAQ 5. Which versions of Visual Studio do I use to embed my reports into my applications?  

An ancillary question to this relates to the report viewer control that developers can use to embed reports in their projects.  

  • For SQL Server 2005 there is a report viewer tool, described here, that works in Visual Studio 2005
  • If you followed that link you’ll notice there is an other version option that tales you to similar details about the report viewer control that works in VS 2008 but still against SQL Server 2005.
  • The report viewer in VS2010 the only works against SQL Server 2008 and SQL Server 2008 R2 as detailed here.

in summary the allowed permutations are:

Visual Studio version Reporting Services version Compatibility
2005 2005 OK
2008 2005 OK
2010 2008 OK
2010 2008 R2 OK

FAQ 6 How is Reporting Services licensed?

Probably a post in it’s own right, but to summarise: If you install a server component of SQL Server (so reporting services, analysis services the database engine etc.) on a server then you must license that server for SQL Server, either by CPU or user CAL just as you would the DB engine itself.

This covered in the SQL Server licensing quick reference guide on the SQL Server 2008 R2 licensing page    

This is a classic example of why I  blog,  I  will be asked about what this post relates to again and again and I’ll forget where the answer is unless I page it to a post!

Comments

  • Anonymous
    August 26, 2010
    Hi Andrew Did you have more to say in your answer to FAQ3?  Looks like some words got cut off.  I want to know whether I can use SSRS 2008 R2 and put the databases on SQL Server 2008 SP1 so I'm interested in the answer! Regards Simon simon.jordan@xchanging.com

  • Anonymous
    August 26, 2010
    Simon Huge apologies. Post is updated, ping me (afryer@micrsooft.com) if you have any more questions

  • Anonymous
    October 10, 2013
    Hi Andrew, Is it possible for me to have an SSRS 2000 with my report databases on a SQL 2008 R2 server?

  • Anonymous
    December 09, 2014
    My current system using Sqlserver Reporting service 2000 and Visual studio 2005, now Reporting service will be upgraded to 2008.
    Will Visual studio 2005 will be compatabile ?
    Data source for Reporting service is Oracle 9 . Is Oracle 9 is compatabile with Reporting service 2008 ?
    Also application has been deployed in Windows server 2003 . Please update me compatible software for Reporting server 2008 for this ?

    Thanks in advance
    senthilkumar

  • Anonymous
    December 12, 2014
    Senthil you have a mountain to climb here and to be honest your best bet is to get to at least 2012 for everything (VS,SQL and Windows) as they do all work together and are also better at backward and forward compatibility.

    Your plan to use VS2005 to write reports on SQL 2008 won't work and Windows Server 2003 is out of support in July 2015 so you could end up doing a lot of work on a non supported system.

    Oracle compatibility is less of an issue as you'll just be using the rather slow Microsoft oracle oledb driver to get your queries.