Consider this scenario. You have SharePoint with SQL Reporting Services (SSRS) running in SharePoint Integrated mode. You want to use a SharePoint list as a data source for a report.
In the SSRS Data Connection you enter the URL and credentials for the SharePoint site but when you test the connection the following error appears:
The given key was not present in the dictionary
After a bit of trial and error I discovered that this error occurs if the URL entered isn’t the default URL for the web application.
To resolve the issue, go into Central Admin and check the Alternative Access mappings on the SharePoint site used in the data source. Check that you are using the default URL.
Similar but unrelated issue
Note that there is a similar issue that occurs when added managed accounts in Central Admin. These issues are not related. Further details of that issue can be found here: https://support.microsoft.com/en-nz/help/2463865/sharepoint-2010-error-message-the-given-key-was-not-present-in-the-dic
I’ve been working on a project where multiple servers including two SharePoint farms running SQL Server Reporting Services (SSRS) in SharePoint Integrated mode need to be moved to a single SQL Server. Each farm has its own SQL Server to host the SharePoint and SSRS databases. To make the job a little more challenging the databases in each farm have the same name.
To complete this job I need to change the names of the databases to avoid duplicate database names on the new SQL Server. Here’s the approach I have taken and few tips to resolve issues caused by the renaming process.
Before attempting the following process, make sure you have a backup.
SQL Reporting Services has two databases:
Backup / Restore :
- Backup and restored the databases to the new server, renaming the databases in the process. In this case I added a prefix to distinguish between the two SharePoint farms.
- On the SharePoint Server run the SSRS Configuration Tool
- Change the database to the new database server name. Note that you don’t get the opportunity to change the Temp database name only the reporting server database.
Update the references to the Temp database:
- In SQL Management Studio, locate the ReportingServerDatabase. There are references to the Temp database in several views and many stored procedures (83 in my case).
- Generate a script for the Views in the database (Drop and Create)
- Generate a script for the Stored Procedures in the database (Drop and Create)
- In both scripts search and replace the old Temp database name with the new one
- Stop the SSRS Services on the SharePoint Server (in the SSRS Configuration tool)
- Run the updated View script
- Run the updated Stored Procedures script
- Restart the SSRS service
- In SharePoint navigate to the SSRS Reporting library (in my case called ReportServer)
- Edit a Data Connection
- View a report
If you get an error in either of these functions, you should see the name of the Stored Procedure and database it is referencing. Go back into SQL Management Studio and check the references have been updated.