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

● Ian Grieve ●  ● 3 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.

● Categories: Dynamics, GP, Microsoft, SQL Server, SQL Server 2000, SQL Server 2008 ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

System Requirements For Microsoft Dynamics GP 2015 Now Available

● Ian Grieve ●  ● 1 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.

● Categories: Dynamics, GP, Microsoft, Office, Office 2007, SQL Server, SQL Server 2008, Windows, Windows Server 2003, Windows Vista, Windows XP ● Tags: , , , , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

SQL Server Cannot Start Following Maintenance

● Ian Grieve ●  ● 1 Comment   ● 

I’ve just finished doing some maintenance on one of my development servers and encountered a problem where I could not restart the SQL Server. The development server was a virtual machine running on VMWare which had a second virtual HDD installed for SQL Server to store the database and log files. This disk had been provisioned at 200GB and over time had acquired a lot of files, such as database backups, which became unnecessary and had filled the 200GB.

I needed to get some of the space back on the VM host so I stopped the SQL Server, copied the SQL mdf and log files to a new HDD, deleted the SQL HDD and then set the drive letter of the new HDD to that of the old one.

I then tried to start SQL Server and received the following error:

Windows Event Viewer - FCB::Open failed: Could not open file E:\SQL208RTM\Live\DYNAMICS.mdf for file number 1. OS error: 5(failed to retrieve text for this error. Reason: 15105).Windows Event Viewer – FCB::Open failed: Could not open file E:\SQL208RTM\Live\DYNAMICS.mdf for file number 1. OS error: 5(failed to retrieve text for this error. Reason: 15105).

Continue reading → SQL Server Cannot Start Following Maintenance

● Categories: Microsoft, SQL Server, SQL Server 2008 ● Tags: , , , ,  ● Permalink ● Shortlink ●

Error Granting Database Access In SQL Server Management Studio

● Ian Grieve ●  ● 3 Comments   ● 

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;

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.

● Categories: Microsoft, SQL Server, SQL Server 2008 ● Tags: , , , ,  ● Permalink ● Shortlink ●

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

● Ian Grieve ●  ● 2 Comments   ● 

I 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

● Categories: Microsoft, SQL Server, SQL Server 2008 ● Tags: , , ,  ● Permalink ● Shortlink ●

How To Deploy SSRS Reports In Microsoft Dynamics GP 2010 R2

● Ian Grieve ●  ● 3 Comments   ● 

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.

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

● Categories: Dynamics, GP, Microsoft, Reporting Services Reports, SQL Server, SQL Server 2008, SSRS ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

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

● Ian Grieve ●  ● 2 Comments   ● 

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.

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

● Categories: Dynamics, GP, Microsoft, Reporting Services Reports, SSRS ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

Restoring A Database Without Encountering A Locking Issue

● Ian Grieve ●  ● 1 Comment   ● 

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.

Continue reading → Restoring A Database Without Encountering A Locking Issue

● Categories: Microsoft, SQL Server, SQL Server 2008 ● Tags: , , ,  ● Permalink ● Shortlink ●

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

● Ian Grieve ●  ● 2 Comments   ● 

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;

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.

● Categories: Microsoft, SQL Server, SQL Server 2008, Windows Virtual PC ● Tags: , , , , ,  ● Permalink ● Shortlink ●

How To Change The SQL Server Backup Folder

● Ian Grieve ●  ● 2 Comments   ● 

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;

Server Properties

Continue reading → How To Change The SQL Server Backup Folder

● Categories: Microsoft, SQL Server, SQL Server 2008 ● Tags: , , ,  ● Permalink ● Shortlink ●