Microsoft Dynamics GP Reporting Services Reports Not Supported on SQL Express

Microsoft Dynamics GPUsually when installing or upgrading Microsoft Dynamics GP for clients they will use SQL Server Standard and sometimes Enterprise; very rarely, I’ll encounter a mall client who is using or wants to use SQL Server Express.

SQL Server Express is a free version of SQL Server, but which has some limitations. SQL Server Reporting Services (SSRS) is not one of those limitations; you can install SSRS nd it will work happily with SQL Server Express as long as both are installed on the same server.

However, Microsoft Dynamics GP will not deploy reports to SSRS when it is being used with SQL Server Express. if you try you get this error message:

SSRS deployment error

Microsoft Dynamics GP

You do not have security access to the location where you want the reports to be deployed.

You will also be prompted to supply a username and password, but these will never be accepted. The Microsoft Dynamics GP System Requirements say that Dynamics GP will work with Standard, Enterprise and Express editions of SQL Server, but does not mention the limitation of SSRS and SQL Express.

There are some forums posts and such mentioning the restriction, but thought it worth a post as a general reminder.

Automate SQL Server Express Backups

Microsoft SQL ServerWe have a couple of small clients who are hosting Microsoft Dynamics GP on SQL Server Express; this is a supported platform, but does not include SQL Server Agent. This means that backups cannot be automated in SQL Server as it is the Agent which provides this functionality.

However, there are still ways in which backups can be automated; one way is to create a DOS batch file and execute it using Windows Scheduler.

The below is a SQL statement which can be executed to generate the DOS batch file commands to backup all of the databases for a Microsoft Dynamics GP implementation.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
DECLARE @SQL NVARCHAR(MAX) = 'echo off
cls
echo -- BACKUP DATABASE --
set SERVERNAME=2018SQL1\GP
set DATESTAMP=%DATE:~-4%-%DATE:~3,2%-%DATE:~0,2%
set SYSTEMDATABASENAME=DYNAMICS
set BACKUPFILENAME=C:\SQLServer\Backup\%SYSTEMDATABASENAME%_%DATESTAMP%.bak
echo.
sqlcmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%SYSTEMDATABASENAME%] TO DISK = N''%BACKUPFILENAME%'' WITH NOFORMAT, INIT, NAME = N''%SYSTEMDATABASENAME%-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
echo.'

SELECT @SQL = @SQL + STUFF((
					SELECT 
						'
set DATABASENAME=' + RTRIM(INTERID) + '
set BACKUPFILENAME=C:\SQLServer\Backup\%DATABASENAME%_%DATESTAMP%.bak
sqlcmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%DATABASENAME%] TO DISK = N''%BACKUPFILENAME%'' WITH NOFORMAT, INIT, NAME = N''%DATABASENAME%-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
echo.'
					FROM
						DYNAMICS.dbo.SY01500
					FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SELECT @SQL

If you are using a named system database, the two highlighted sections need to be amended.

To run the script, set the output to file and then copy and paste the resulting script into a file with the .bat extension; this batch file can then be scheduled to run automatically using Windows Scheduler.