Tag: Microsoft SQL Server 2008 R2

SQL Error: “The query uses non-ANSI outer join operators”

Ian Grieve3 minute read3 Comments

Microsoft Dynamics GPI’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. Detsails: 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...

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.

System Requirements For Microsoft Dynamics GP 2015 Now Available

Ian Grieve2 minute read1 Comment

Microsoft Dynamics GPThe 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:

  1. Windows:
    • Windows XP all editions
    • Windows Vista all editions
    • Windows Server 2003 all editions
  2. SQL Server:
    • 2008 all editions (including R2)
  3. Office:
    • 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.

Error Granting Database Access In SQL Server Management Studio

Ian Grieve3 minute read5 Comments

Microsoft SQL ServerI’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;

USE [GPNL]
GO

ALTER USER pidev WITH LOGIN = pidev
GO

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.

SSRS GL Trial Balance Returning No Data

Ian Grieve2 minute read2 Comments

Microsoft Dynamics GPI was doing some testing to compare the SSRS Trial Balance against the standard report version but ran into a problem straight away when I found the SSRS version returned no data.

A quick search online found me this post SSRS: GL Trial Balance Summary report returns no data – Follow Up by Mariano Gomez where he discussed the exact problem I had encountered.

As Mariano says this has been fixed in the upcoming SP3 but for now there is a Knowledge Base article covering a workaround which is to alter the stored procedure (seeglPrintSRSTrialBalance) which is used for returning the data: GL Trial Balance SRS Reports return no data using Microsoft Dynamics GP.

SQL Server Management Studio Not Allowing Table Changes That Require A Table To Be Dropped

Ian Grieve1 minute read2 Comments

Microsoft SQL ServerI was doing some work with a custom table yesterday and at one point needed to make a change to the table layout via SQL Server Management Studio. However, when I tried, I got the below error message;

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can

Continue reading “SQL Server Management Studio Not Allowing Table Changes That Require A Table To Be Dropped”

How To Deploy SSRS Reports In Microsoft Dynamics GP 2010 R2

Ian Grieve1 minute read5 Comments

Microsoft Dynamics GPEarlier 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.

To deploy the reports you need to open the Reporting Tools Setup window (Tools » Setup » System » Reporting Tools Setup) Continue reading “How To Deploy SSRS Reports In Microsoft Dynamics GP 2010 R2”

How To Configure SQL Server Reporting Services In Order To Deploy Reporting Services Reports In GP

Ian Grieve2 minute read3 Comments

Microsoft Dynamics GPThe 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.

To do the initial configuration of SSRS, or to change the configuration of an existing installation, you need to start the Reporting Services Configuration Manager (Start menu » Programs » Microsoft SQL Server 2008 R2 ?? Configuration Tools » Reporting Services Configuration Manager) Continue reading “How To Configure SQL Server Reporting Services In Order To Deploy Reporting Services Reports In GP”

Restoring A Database Without Encountering A Locking Issue

Ian Grieve2 minute read1 Comment

Microsoft SQL ServerI 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.

Continue reading “Restoring A Database Without Encountering A Locking Issue”

Error Code 2337 When Installing Microsoft SQL Server Into A Virtual Machine

Ian Grieve1 minute read2 Comments

Microsoft Dynamics GPI 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;

The following error has occurred: The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2337.

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.

How To Change The SQL Server Backup Folder

Ian Grieve1 minute read2 Comments

Microsoft SQL ServerWhen 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;

Server Properties

Continue reading “How To Change The SQL Server Backup Folder”