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.
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
2008 all editions (including R2)
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.
I’m currently involved with a couple of projects where we have needed to migrate databases from one SQL Server to another and needed to do so more than once.
Prior to the first copy, we used a script to load the users into SQL Server and then restored the databases and had no problems.
Later on, after changing some of the permissions to databases we had need to remigrate some of them over. However, we found that users could not access some databases and when we tried to add them we got the following error;
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'pidev' already exists in the current database.
It was looking very like we would need to go through each database and remove access and then regrant it all; a labourious process. A quick Google found me a page from Julian Kuiters which introduced me to the ALTER USER command. The following code will run against the selected database and tie together the SQL Server user with the user in the database without any other work;
ALTER USER pidev WITH LOGIN = pidev
The first highlighted element needs to be changed to the database you want to update the user on and then the following two entries are the usernames which need to be linked (these should be set to the same value).
Update: if your usernames contain a space (e.g. I Grieve) then wrap them in square brackets: e.g.
Earlier this week I did a post on how to configure SQL Server Reporting Services for use with Microsoft Dynamics GP. This is the follow up post where I’m going to explain how to deploy the SSRS reports for GP.
The post following this will cover deploying SSRS reports within Microsoft Dynamics GP but I thought I’d cover configuring SSRS as a spearate post rather than assuming everyone has it already configured. A number of the clients I deal with have had GP for a while and never installed the SSRS reports but are now doing so.
When you install SQL Server Reporting Services at the same time as SQL Server you have the option to automatically configure SSRS with the default settings. In the case of someone who has SQL already installed but didn’t install SSRS at the same time, this is not an option.
This How To starts after SSRS has been added to the installation on the client machine at the point where you would start to configure it; if you’d like a walk through adding SSRS to an existing installation of SQL Server, let me know and I’ll sort one out.
I periodically have problems when trying to restore a GP company database over the Test database as SQL reports that the database is currently in use.
One way of resolving is to restart the SQL Server but this is only possible on a stand alone test system which is not being used by other people but this approach is overkill.
The better solution is to change the database to single user, restore the database and change the database back to multiple user. This can be done manually through SQL Server Management Studio but is far easier done via a SQL script.
I have installed SQL Server into Virtual Machines many times in the past but I’ve recently seen an error when trying to do it again;
I had a think about the error and realised that I usually installed SQL from the VM HDD and not, as I was in this case, by attaching the iso image to the VM as external media. I uncompressed the iso onto the VM HDD and ran the installer again from this source and it installed without problem.
When you install Microsoft SQL Server you select the Data, Logs and Backup folder during the installation process. If you subsequently need to change these folders you can only change two of them through the SQL Server Management Studio UI. On the Server Properties dialog there are boxes for the Data and Logs default locations;