I’ve recently been working on a project upgrading a client from Microsoft Dynamics GP 9 SP3 to a later version and also from Microsoft SQL Server 2000 to SQL Server 2008 R2. Much of the upgrade has gone through without problems, but we’ve encountered one or two issues with customisations and custom reports.
The following error message was produced during testing when generating one of the old Crystal Reports:
Crystal Report Viewer
Failed to open rowset.
Details: 42000:[Microsoft][SQL Server Native Client 10.0][SQL Server]The query uses non-ANSI outer join operators("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backwa...
The compatibility level of a database for Microsoft Dynamics GP should NOT be changed back to 80 when this error is encountered. The solution to use is the recommended one from the error message: to rewrite the query.
In this case the client had a set of Crystal Reports written against GP 9 which called a variety of stored procedures. I spent a few hours reviewing and rewriting stored procedures to remove the non-ANSI outer joins and replacing them with ANSI ones.
In 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.
In the last post, I stepped through the installation of SSRS. As I mentioned in that post, I prefer to do the configuration separately, as I have had problems with the automatic configuration.
To configure SSRS, launch Reporting Services Configuration Manager from the Windows Start screen.
The first window launched is the Reporting Services Configuration Connection one; ensure the Server Name is correct and that the Report Server Instance is the one to be configured and click Connect:
In the previous post I covered the installation of SQL Server itself; in this post I’m going to cover the installation of SSRS. While it is possible to have SSRS installed and configured automatically when SQL Server is installed, but I have experienced problems with SSRS when doing this so I usually install it separately. You would also do a separate installation of SSRS if you had a reporting server where you were doing an install of SSRS.
To install SSRS launch the setup.exe on the installation media, click on Installation in the navigation pane and then click on New SQL Server stand-alone installation or add features to an existing installation:
The first item I’m going to cover is SQL Server itself; I have had problems before installing and configuring SSRS at the same time as SQL Server, so I now always separate these into three separate steps.
The first part is to install SQL Server; do so in SQL Server Installation Center by launching the setup.exe from the installation media:
The System Requirements for Microsoft Dynamics GP 2015 are now available from CustomerSource (login required).
The stand out items for me is that support has been dropped for several older versions of Windows, SQL Server and Office:
- Windows XP all editions
- Windows Vista all editions
- Windows Server 2003 all editions
- SQL Server:
- 2008 all editions (including R2)
- Office 2007
The only surprise on the above list is that some of the software listed as no longer supported was also listed as no longer supported with Dynamics GP 2013; in fact only SQL Server 2008 is new to the list.
Apart from the above retired software, the recommendations look pretty much the same as Dynamics GP 2013; Windows 8.1 and SQL Server 2014 have been added as supported.
After doing a little work linking databases together for a report, I ended up creating a script to be used within a stored procedure to quickly and easily re-add the linked server (which is required after a reboot of the SQL Server.
To run the script, change the three highlighted fields (server-name, username and password) and click Execute in SQL Server Management Studio:
DECLARE @Server AS VARCHAR(50) DECLARE @Username AS VARCHAR(50) DECLARE @Password AS VARCHAR(50) SET @Server = 'server-name' SET @Username = 'username' SET @Password = 'password' CREATE TABLE #linkedservers (SRV_NAME VARCHAR(50) ,PROV_NAME VARCHAR(50) ,SRV_PROD VARCHAR(50) ,SRV_DATA VARCHAR(50) ,SRV_STRING VARCHAR(50 ,SRV_LOC VARCHAR(50) ,SRV_CAT VARCHAR(50)) INSERT INTO #linkedservers EXEC sp_linkedservers IF (SELECT COUNT(*) FROM #linkedservers WHERE SRV_NAME = @Server) < 1 EXEC sp_addlinkedserver @Server, 'SQL Server' DROP TABLE #linkedservers EXEC sp_addlinkedsrvlogin @Server, 'false', NULL, @Username, @Password
When Reporting Tools Setup is used t deploy the reports to SQL Server Reporing Services an error be produced if the default configuration of SSRS has not been amended (I thought I’d blogged about this error before, but when trying to find it to send the post to someone I couldn’t find it).
The error produced is the following: