One of the common issues that people may have encountered while using Sage 300 ERP are database errors. The majority of these issues is being unable to connect to the database.

In this week’s blog post, we will highlight some of the common database errors and how to troubleshoot them.

Before we start, please note that any troubleshooting steps noted require knowledge of database engines and application databases used by your Sage product (including Microsoft/Transact SQL, Pervasive SQL, or MySQL, etc.). So if you are not knowledgeable in this area, please do not attempt to follow these steps on your own instead contact an authorized business partner or database administrator for assistance. We here at Customer Support are not responsible for assisting with these steps and cannot be responsible for errors resulting from changes to the database engine or databases. Finally, before making any changes, please make sure you’ve performed a full backup of your database.

Now that we got that out of the way, let’s take a look at some common database issues in Sage 300 ERP.

The first one is Error 98 “Unable to connect to database”.

You normally see this error when opening a Sage 300 ERP company or running Verify from Database Setup.

It has been reported that this error occurs if you are using a Pervasive database (version 11) on a virtual server. It seems the cause is the Pervasive database became disabled due to a change in hardware configuration.

To resolve this issue, all you need to do is re-install the Pervasive database and recreate the ODBC connection on the affected workstation.

Another error you will see is Error 49153 where you cannot access the database during login. There are a couple of things that is possibly causing this error:

• You are not using System Administrator (SA) level SQL logon to create ODBC Data Source to connect to Sage 300 ERP databases located on Microsoft SQL Server.
• Your firewall is blocking connection to the Microsoft SQL Server.

To resolve this issue, you need to check to see if your ODBC data source can connect to your SQL Server. Follow these steps to see your ODBC connection.

1. Identity the name of the data source used by the company database:

1. From Windows, click Start.
2. In the Search box, type Database Setup.
3. From your keyboard, press ENTER.
   The Database Setup screen appears.
4. Select the Database ID for the company that you are receiving the error.
5. Click Edit.
   The Edit SQL Server Database Profile screen appears.
6. From the Data Source list, identify the Data source being used.
7. Click OK to close this screen.
8. Click Exit to close the Database Setup screen. 
   Make sure that both your company and system database is using the same data source.

• Run the SQL Server ODBC Data Source Test for this Data Source:

1. If you are using a 32-bit versions of Windows, click Start, Control Panel, Administrative Tool, and then Data Sources (ODBC).
   The ODBC Data Source Administrator screen appears.

• If you are using a 64-bit version of Windows:

1. In your Windows operating system, browse to the following directory:
   C:\Windows\SysWOW64
2. Click odbcad32.exe.
   The ODBC Data Source Administrator screen appears.

• Click the System DSN tab.
• From the System Data Sources list, select the Sage 300 ERP data source you identified in 1.f.
• Click Configure.
  The Microsoft SQL Server DSN Configuration screen appears.
• Click Next.
• Select the following option:
  With SQL Server authentication using a login ID and password entered by the user.
• For the Login ID and Password, you must use a SA level SQL Server login account.
  If you do not know the login id and password for your SQL SA login account, please contact your Database Administrator.
• Click Next.
• Click Next.
• Click Finish.
  The ODBC Microsoft SQL Server Setup screen appears.
• Click Test Data Source.
  If you receive an error, this indicates the SQL server login account does not have SA level privileges. To resolve the issue you need to use a SA level user in step 2.g.
• Repeat steps 2.f. to 2.K. until you receive the following Microsoft SQL Server message:
  "TESTS COMPLETED SUCCESSFULLY!"

Once you receive the message "TESTS COMPLETED SUCCESSFULLY!" you should now be able to login to your Sage 300 ERP without the error appearing.

If you are receiving a message that the test did not complete successfully, this may indicate your ODBC data source may be corrupted and will require you to recreate a new data source.

If you still receive the same message that the test did not complete successfully for the new data source, this may indicates that your:

• Microsoft SQL Server login is not a SA user or does not have SA Level permissions.
• The Microsoft SQL Server requires a reboot.
• The firewall is blocking the connection.

At this point this would be an issue with your network and Microsoft SQL Server. This would require the assistance of your Network and Database Administrator to troubleshoot.

And finally when saving database setup for a Microsoft SQL database, you might see this message:

Unable to connect to the database (error=99)

This error will only occur after you’ve removed the SQL Native Client driver that came with System Manager and installed the SQL Native Client 11 driver instead on your computer. To resolve the issue, simply create your ODBC database connection using SQL Native Client 10.

Check back soon when we outline some more database errors in Sage 300 ERP

See any other database connection errors while in Sage 300 ERP? Let us know in the comments.

For the latest support news and updates...
Follow us on...