Sql-server – SSRS (2008 R2 to 2014) Migration Issues Running Reports

migrationsql serversql server 2014ssrs

This is my first ever StackExchange question, so sorry if I something is off…I searched and found a rather helpful article that got me this far, but nothing I found addressed what I am currently seeing.

As the title states, I am having issues running reports on SSRS 2014 after having migrated from SSRS 2008 R2. A bit of background information:

  1. SQL Server 2008 R2 was hosted on a Win 2008 R2 server
  2. New Server (SQL Server 2014) is Win Server 2012 (not a big fan of metro interface)
  3. I added the databases with the backup/restore method going from 2008 to 2014
  4. All backups/restores completed smoothly.
  5. I can view all data, and SP from the other databases and the ReportServer DB
  6. I imported the key after backing it up form SQL Report Services Configuration manager which all completed successfully.

So the issue lies in the fact that when I connect to the SSRS home in IE, I can browse and manage everything, but nothing is able to run. I am getting to the point where I can enter parameters, but get the message:

"Query execution failed for dataset 'DataSet1'.(rsErrorExecutingCommand) Could not find stored procedure 'storeprocedurename'"

After having opened them in Report Builder on the server, it appears that when I right-click the data set to check the properties, I cannot actually login to the data source for the data set (data sets are embedded in reports). I tried creating a new data source, but logging into this proved an issue as well when it came time to add a data set – though it was able to successfully test connection to the new data source. So my guess is that the data source is not connecting to the new sql server database? At a loss and grasping for straws.

Another odd thing is that the sys accounts are not enabled for the databases I imported, nor am I given the option to enable it.

If anyone has any insight as to what I may have messed up, or what I need to modify to correct this, I would be incredibly appreciative. Thanks in advance for any help.

Best Answer

I found the solution in case anyone else comes across this.

The issue lied in the connection string property of the data source. For some reason (maybe standard, I am simply unaware of why) the connection string properties were empty after importing them to the new server. After setting them, they were able to hit the stored procedures and all reports ran as intended.

Related Question