Troubleshoot SQL Server 2008 R2 Reporting Services AdventureWorks Sample Reports (SQL Server Reporting Services SSRS)

Issue

The SQL Server 2008 R2 Reporting Services (SSRS) AdventureWorks sample reports do not run and you receive the message “Cannot create a connection to data source AdventureWorks2008R2”.
  

The following picture shows the error message that you see in the user interface.

 

To view the AdventureWorks sample reports for SQL Server 2008 R2 Reporting Services, you must have the corresponding AdventureWorks sample database installed. If you cannot connect to the AdventureWorks2008R2 database, you will not be able to run the corresponding sample reports.

The problem is most likely that the AdventureWorks2008R2 database is not installed.

Installation of AdventureWorks 2008 R2 RTM Sample Databases

The download package, AdventureWorks2008R2_RTM.exe, available on CodePlex (Sample Databases for Microsoft SQL Server 2008R2 (RTM)), includes three versions of the Adventure Works databases:

·         AdventureWorksLT2008R2 (the light weight version with a simpler schema)

·         AdventureWorksDW2008R2 (the data warehouse version)

·         AdventureWorks2008R2 (the online transaction processing (OLTP) version that the AdventureWorks sample reports use)

 

To successfully install the AdventureWorks2008R2 OLTP sample database from AdventureWorks2008R2_RTM.exe, available at Sample Databases for Microsoft SQL Server 2008R2 (RTM), you must have an instance of SQL Server 2008 R2 that meets the following requirements:

·         Full-Text Search is installed

·         SQL Full-text Filter Daemon Launcher is running

·         FILESTREAM is enabled

For step-by-step instructions to install and configure full-text search and FILESTREAM, see Database Prerequisites for SQL Server 2008R2 .

Note: To verify that all database versions installed, open SQL Server Management Studio and see which databases are in the Databases folder.

Note: A SQL Server 2008 R2 version of the AdventureWorks2008R2 OLTP sample database without the FILESTREAM dependency is now available at Sample Databases for Microsoft SQL Server 2008R2 (RTM).
   

Installing Sample Databases on SQL Server 2008 R2 Express

You can  install the AdventureWorks 2008 R2 RTM sample databases on SQL Server 2008 R2 Express. SQL Server 2008 R2 Express is free and you can download it from Microsoft/Express. Be sure to install the Database with Advanced Services edition. It is the only edition that includes full-text search. You cannot select options during installation. After you complete the installation, you must enable FILESTREAM. For step-by-step instructions to configure FILESTREAM, see Database Prerequisites for SQL Server 2008R2 .

 

View Reports Successfully

Reports are designed to work with AdventureWorks2008R2. Updating the data sources of will not solve the problem. These databases have different schemas (for example, tables and columns have different names) and the report queries do not work with them. You must install AdventureWorks2008R2 to run the reports.

To solve the problem, you must install Full-Text search and configure FILESTREAM on the instance of SQL Server Database Engine that you are using, remove or rename any versions of 2008R2 versions of the Adventure Works databases, and then rerun AdventureWorks2008R2_RTM.exe from Sample Databases for Microsoft SQL Server 2008R2 (RTM).

 

Your Reporting Services Reports Displays Different or No Data

The date/time data in the AdventureWorks2008R2 database has changed from earlier versions of the database. Four years were added to date/time column values. If your reports, use parameters or filters set by date/time values, the data that displays in the reports might be different or the report might be blank.  

To resolve the problem, update the filters or parameters or use the earlier SQL Server 2008 version of the database.

 

Showcase Localized Data in Reports

Additional localized product descriptions have been added to the AdventureWorksDW2008R2 database. The data is in the FactAdditionalInternationalProductDescription table. The addition of this table does not affect reports that use the AdventureWorksDW2008R2 database. They continue to run without modification.