SSRS requires that you use local SQL Server relational databases… error

Microsoft Dynamics GPMicrosoft Dynamics GP ships with a set of default reports which can be deployed into SQL Server Reporting Services; before doing the deploy, there is a setting in the SSRS web.config file which needs to be changed to avoid a maximum request length error.

Recently, when performing an upgrade of a clients system to Microsoft Dynamics GP 23018 R2, the deployment of the reports went fine, but we encountered a problem while testing the deployed reports:

SSRS reports error message

The client had a licenced version of SQL Server 2018 Web Edition which worked in all other respects; the SSRS service was running locally on the SQL Server which was hosting the databases for both SSRS and Microsoft Dynamics GP itself.

This wasn’t something I’ve encountered before, but, fortunately, a colleague had. The problem is not with the Web Edition itself, but rather with the data connection deployed by Microsoft Dynamics GP for the reports. When the data connections are deployed, there is a space created preceding the Data Source name:

Connection settings in SSRS Manager

The fix is to go through all of the datab connections and remove the space after the equals sign, so that, in the example above, Data Source= IG-SQL2017-01\GP... becomes Data Source=IG-SQL2017-01\GP....

How to Install Microsoft SQL Server 2017: Download SSRS 2017

Microsoft SQL ServerIn this short series of posts, I am going to be covering the installation of Microsoft SQL Server 2017; the installation process for this version is somewhat different to previous ones, in that SQL Server Management Studio and SQL Server Reporting Services have been separated out from the main installer.

The series index can be found here. I am not a SQL expert and this series of posts is intended only for use on demo or test environment and not production ones.

This is a very late addition to this series of posts; I recently came to install SQL Server 2017 on a new machine which did not have an Internet connection and so needed to download the installer and copy itto the new machine.

In the installation post I used the download option in the installer, but didn’t include the direct download link.

Well, to remedy that, here is the download link to the latest version of SSRS 2017.

How to Install Microsoft SQL Server 2017: SSRS 2017 Not Supported on a Domain Controller

Microsoft SQL ServerIn this short series of posts, I am going to be covering the installation of Microsoft SQL Server 2017; the installation process for this version is somewhat different to previous ones, in that SQL Server Management Studio and SQL Server Reporting Services have been separated out from the main installer.

The series index can be found here. I am not a SQL expert and this series of posts is intended only for use on demo or test environment and not production ones.

I often create two types of demo or test environment; one will have multiple virtual machiens createdin order to replicate a typical live environment, and the second has only a single virtual machine.

While prepping a test environment of the latter type in readiness for the launch of Microsoft Dynamics GP 2018 RTM, I encountered a problem with SSRS:

Microsoft SQL Server 2017 Reporting Services installation issue

Microsoft SQL Server 2017 Reporting Services

Setup blocked

The following issues blocked Setup

Installing Reporting Services on a domain controller is not supported

This is a major departure from SQL Server 2016 and previous, all of which allowed SSRS to be installed on a Domain Controller; for most systems this will be absolutely fine, but I’ll no longer be able to use single virtual machine demo or test environments.

How to Install Microsoft SQL Server 2017: Avoiding Max Request Length Error in SSRS

Microsoft Dynamics GPIn this short series of posts, I am going to be covering the installation of Microsoft SQL Server 2017; the installation process for this version is somewhat different to previous ones, in that SQL Server Management Studio and SQL Server Reporting Services have been separated out from the main installer.

The series index can be found here. I am not a SQL expert and this series of posts is intended only for use on demo or test environment and not production ones.

In the last post I configured SSRS, which is fine for most uses. However, Microsoft Dynamics GP requires an additional configuration step. This is not done through the Report Server Configuration Manager, but by editing a config file.

The reason this is required is because of an error produced when deploying the SSRS reports. If you do not change the setting, the error you will receive is the following:

GP – The deployment has exceeded the maximum request length allowed by the target server. Set maxRequestLength="20690" in the web.config file and try deploying again.

Microsoft Dynamics GP

The deployment has exceeded the maximum request length allowed by the target server. Set maxRequestLength="20690" in the web.config file and try deploying again.

To avoid this error, requires the SSRS web.config file to be amended. The file, for SQL Server Reporting Services 2017, is located in C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\ReportServer.

Below is a snippet of code from the web.config file with the added node highlighted:

<httpRuntime executionTimeout="9000" requestValidationMode="2.0" maxRequestLength="20690" />

Add the above, save the file, and SSRS is now ready for the Microsoft Dynamics GP reporting services reports to be deployed.

How to Install Microsoft SQL Server 2017: Configure SQL Server Reporting Services

Microsoft SQL ServerIn this short series of posts, I am going to be covering the installation of Microsoft SQL Server 2017; the installation process for this version is somewhat different to previous ones, in that SQL Server Management Studio and SQL Server Reporting Services have been separated out from the main installer.

The series index can be found here. I am not a SQL expert and this series of posts is intended only for use on demo or test environment and not production ones.

In the last post I covered the installation of SQL Server Reporting Services (SSRS); in this post I am going to cover the basic configuration of SSRS.

To complete the configuration, launch the Report Server Configuration Manager; ensure the Server Name is set to the correct name and click the Connect button:

The Report Server Configuration Connection

Continue reading “How to Install Microsoft SQL Server 2017: Configure SQL Server Reporting Services”

How to Install Microsoft SQL Server 2017: Install SQL Server Reporting Services

Microsoft SQL ServerIn this short series of posts, I am going to be covering the installation of Microsoft SQL Server 2017; the installation process for this version is somewhat different to previous ones, in that SQL Server Management Studio and SQL Server Reporting Services have been separated out from the main installer.

The series index can be found here. I am not a SQL expert and this series of posts is intended only for use on demo or test environment and not production ones.

Microsoft Dynamics GP ships with some Reporting Services Reports by default, so this componenbt of SQL Server also needs to be installed. As with SSMS, SQL Server Reporting Services (SSRS) is not installed by the main installer, but, again like SSMS, it is available via a download accessible tyhrough the setup utility.

To install SSRS, laucnh the main setup utility of SQL Server, click Installation and then select Install SQL Server Reporting Services:

SQL Server Installation Center - Installation

Continue reading “How to Install Microsoft SQL Server 2017: Install SQL Server Reporting Services”

How To Install Microsoft SQL Server 2016: Configuring SQL Server Reporting Services

Microsoft Dynamics GPThis is a short series of posts on how to install Microsoft SQL Server 2016; the series index can be found here.

In the last post, I installed the SQL Server Database Engine and the Reporting Services, but I did not configure Reporting Services. I mentioned that I have had problems before when doing this, so always do it separately.

To configure SQL Server Reporting Services (SSRS), launch Reporting Services Configuration Manager from the Windows Start menu.

Select the Report Server Instance to connect to and click Connect:

Reporting Services Configuration Connection

Continue reading “How To Install Microsoft SQL Server 2016: Configuring SQL Server Reporting Services”

How To Install Microsoft SQL Server 2016: Installing SQL Server Database Engine

Microsoft Dynamics GPThis is a short series of posts on how to install Microsoft SQL Server 2016; the series index can be found here.

In this post, I am going to step through the installation of the Microsoft SQL Server 2016 Database Engine. As most of my work is with Microsoft Dynamics GP, there will be a little focus on the installation required specifically for Dynamics GP, but the basic install is the same regardless of whether it is for Dynamics GP or not.

Launch the SQL Server setup utility (setup.exe), select Installation and click on New SQL Server stand-alone installation or add features to an existing installation:

SQL Server Installation Center

Continue reading “How To Install Microsoft SQL Server 2016: Installing SQL Server Database Engine”

MDGP 2015 R2 Feature of the Day: Historical Received Not Invoiced Report

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 will soon be released (due late May/early June and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.

The fifteenth Feature of the Day is Historical Received Not Invoiced Report.

A new SQL Reporting Services Report based off the current Received Not Invoiced report but add new functionality by adding a cutoff date based on transaction date or GL Post Date to allow customers to use the report for Historical purposes. This report will allow users to see what was received into inventory but not invoiced yet as of a specific date.

Historical Received Not Invoiced Report SSRS Report

These reports are becoming increasingly popular with clients as they look far better than the “very white” ones GP has traditionally shipped with. The only downside for someone who isn’t in the US is that they ship with a default language of en_US so all values come out as USD. I have some PowerShell scripts I have been working on to download, update the language and upload the reports from SQL Server Reporting Services. I need to revisit and see if I can knock them into shape to post online; I’m not experienced with PowerShell so it is taking me awhile. If someone wants to volunteer to help, I would appreciate it.

Click to show/hide the MDGP 2015 R2 Feature of the Day Series Index

Install SQL Server 2014: SSRS Configuration for Microsoft Dynamics GP

Microsoft Dynamics GPIn the previous two posts, I stepped through the installation and configuration of SSRS. To deploy the Microsoft Dynamics GP SSRS reports to the SSRS server there is one last piece of configuration which needs to be completed.

There is a setting in the web.config file which needs to be added for the reports to deploy successfully. I have previously blogged about that setting here. The only change in the path will be the version number in the folder name will be higher.

Once you have completed that step you can then move onto deploying the SSRS reports in Dynamics GP; this post was written on Dynamics GP 2010 R2, but the process for deploying them is the same in Dynamics GP 2015.