In a previous blog, we took a look at database errors when you cannot connect to database in Sage 300 ERP.

This week, we continue to look at troubleshooting more database errors particularly around issues involving Open Database Connectivity aka ODBC

The first is Error: “Cannot access database (error=40960). You will normally encounter this error when the Data Source Name (DSN) used in database setup points to an incorrect server.

The fix is to configure DSN correctly using the correct database server. To do so, follow these steps:

1. Open Data Sources (ODBC) (on a 64 Bit operating system ensure the 32 bit Data Sources (ODBC) is used
2. Select the appropriate DSN and click Configure
   
   
   
   
3. Complete the configuration of the DSN using the correct settings.

Another error occurs when logging into Sage 300 ERP you find that the database is locked. To troubleshoot, check the SQL driver installed on the bad workstation and make sure the appropriate ones are installed. If all is okay on that end, delete the existing ODBC DSN on the problem workstation and re-create it. If a DSN doesn’t exist, create the appropriate one. After that try logging in again.

And remember the letters ODBC as you will be seeing a lot of it in this blog.

Also while logging into Sage 300 ERP, some people have encountered this error:

Incorrect version of database software

Please note that the troubleshooting steps below require knowledge of database engines and application databases used by Sage 300 ERP (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.

This issue seems to only affect those on Microsoft SQL Server 2005 onwards with Sage 300 ERP 5.6 and up.

To resolve, the first step is to remove any ODBC connections in ODBC setup in Windows referencing to that database server and set up only the SQL Native Client (or SQL Native Client v10.0).

If you are running Sage Accpac ERP 5.6, then you probably have seen this error when opening up a company:

Error: "Invalid user ID. The connection user ID specified in the system database profile is not valid."

Follow these steps to resolve the issue:

1. Verify that System DSN used in ODBC is using the correct SQL login credentials. If server name is used, the program creates System DSN named [server name] 32.
2. Select System DSN and click Configure
3. Enter SQL credentials and test the connection.
4. Open Database setup and assign verified DSN to Company and System databases.
5. If error is user specific connecting via terminal server on Windows 2008, right click on the program and run it as administrator.

A similar issue happens when logging into a Sage 300 ERP Microsoft SQL database where you get an invalid database specification. This could be due to damage to the Microsoft SQL Native Client driver. This issue can be resolved by installing or repairing the SQL Native Client driver.

You might have also seen this error when opening up a company database:

Error "You Cannot Open This Company Because Another Task Has Locked the Database"

You will see this error if you are running version 11 of a Pervasive database on a virtual server.

To resolve the issue, recreate the ODBC connection to the Pervasive Database.

1. Go to the Windows' Start menu, select Start, All Programs, Administrative Tools,ODBC Data Sources (ODBC).
2. Select the System DSN tab.
3. Identify and select the ODBC connection for the Sage 300 ERP databases
4. Click Configure and make note of all the settings you need to recreate this ODBC connection.
5. Click Remove to delete the ODBC connection.
6. Click Add and recreate the ODBC connection.
7. Save changes.

See any other database connection errors as it relates to ODCB while in Sage 300 ERP? Let us know in the comments.

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