Fix Sage 300 Connecting to Database Slow Issue

Sage 300 Connecting to Database Slow Issue

Are you facing issues in connecting the Sage 300 to a database or it is too slow while connecting? Then here we are to help you out in all the possible ways. We all use the SQL database to store the data in it. So we get to know that how difficult it is to handle the database if the connection is too slow because of this slow connection the work is due day by day.

So, to get rid of it set up a new database so that you can’t be stuck in the slow connection and do the work on time. Here we are going to tell you how you can resolve this problem easily.

Causes of Sage 300 Connecting to Database Slow Issue

The main reasons for the database slow issue can be:

  • A faulty or a low-quality hardware.
  • A network speed which is lower than 1 GBPs for all workstations.
  • Maybe the Wireless connections are verboten.
  • The old server has been in the PATH since after the upgrade.

Solution for Sage 300 Database Slow Connection

🔹 For working on Sage 300 ERP you need to work this as your first step-
🔹 Firstly, create a SQL database
🔹 Then set it up with ERP of Sage 300

Total Time: 35 minutes

Steps for Create SQL Database

🔹 Open the Management studio of SQL
🔹 Now check out your databases here that are already created in the server of SQL
🔹 Then you need to build a new database
🔹 For this, right-click on the database and select the New Database option
🔹 Then click on the option that says create both databases for system and company with their names
🔹 To create both databases, enter their name and then click on the OK button
🔹 A new database was created and connects it to the Sage ERP.

For Connecting it with Sage 300 ERP the Steps are as follows

Note: – Create a system database first and then the company database

🔹 For System Database, Go to the setup screen of the database
🔹 On the setup screen, select the New button
🔹 Then enter the name for the new database and also select the category as System and click on the OK button
🔹 An Edit SQL Server Database profile screen is opened
🔹 In this screen, server details need to be entered
🔹 Now create the Company Database
🔹 For this, firstly go to the screen of database setup
🔹 On this database screen of setup, select the New button
🔹 Then the new name for the new database needs to be entered
🔹 Also, select the category as Company and click on the OK button
🔹 Now both the databases are created and you can check them in the Sage database setup list. Also, it is shown under the Company after logged into Sage ERP.

Missed Connection: Troubleshooting more Database Errors

Let us take a look at some of the database errors that especially involve the Open Database Connectivity aka ODBC:

Error 1: Error: “Cannot Access Database (Error=40960)

This error is normally encountered when the Data Source Name-DSN is used for the database setup points to an incorrect server:

Here are the steps to fix this:

  • Launch the Data Sources (ODBC) (on a 64 Bit operating system ensure the 32-bit Data Sources (ODBC) is used
  • Now Select the correct DSN and click Configure
  • Now Complete the configuration of the DSN using the right settings

Error 2: “Invalid User ID. The Connection User ID specified in the System Database Profile is not Valid.”

Here are the steps to resolve the error:

  • Start the Verification if the System DSN used in ODBC is using the correct SQL login credentials.
  •  In case the server name is used, the program creates a System DSN named [server name] 32.
  • Now Select System DSN 
  • Next, click Configure
  • Now Enter SQL credentials and test the connection.
  • Launch Database setup and assign verified DSN to Company and System databases.
  • In case the error is user-specific connecting via a terminal server on Windows 2008, you need to right-click on the program and run it as an administrator.

There are times when you might encounter the same error when you are logging in to Sage 300 ERP Microsoft SQL database, where you can encounter invalid database specifications. The reason can be because of damage to the Microsoft SQL Native Client Driver. The problem can be resolved by either installing or repairing the SQL Native Client Driver. Likewise, the same error can also occur when the user is trying to open the Company database.

Read More-: Fix Sage 300 Error 49153 Cannot Access Database

Error “You Cannot Open this Company Because another Task has Locked the Database”

This error usually occurs when the user is trying to run version 11 of a Pervasive database on a virtual server. To resolve the issue, you will need to recreate the ODBC connection to the Pervasive database

  • Navigate to the Windows Start menu, 
  • Now select Start > All Programs > Administrative Tools > ODBC Data Sources (ODBC).
  • Next, Select the System DSN tab.
  • Now you need to Identify and select the ODBC connection for the Sage 300 ERP databases
  • Next, Click Configure and make a note of all the settings you need to recreate this ODBC connection.
  • Now Click Remove to delete the ODBC connection.
  • Next, Click Add and recreate the ODBC connection.
  • Finally, Save changes.
    • The other forms of error that can commonly occur are when the user is trying to log in to the Sage 300 ERP and the database has been locked. To resolve the issue you will need to check on the SQL driver that has been installed on the faulty workstation and ensure that the right ones have been installed. Once done, you need to delete the existing ODBC DSN within the problematic workstation and create it once again. In case the DSN does not exist, create one and try logging in once again. 
    • There is yet another error; ‘Incorrect version of database software’. To start with, this error only affects those on Microsoft SQL Server 2005 onwards with Sage 300 ERP 5.6 and above. To solve this, start by removing any ODBC connections in the ODBC setup within the Windows referencing to that database server and setting up only the SQL Native Client.

Performance Issues on a Standalone or Network

Section 1: Sharing of Permissions & Security

  • In case the shared data folder’s permissions and/or security have not been set properly, this can cause it to be slow. 
  • Ensure that the network drive within each workstation has been mapped directly to the shared folder and not to a sub-folder (for instance, Sage 50 is shared, but the drive is mapped to the Company); if it is, disconnect and remap the drive

Section 2: Sage Company Size

  • The size of a Sage company directly affects the performance of the program where large databases can result in longer load times for the program.
  • To check a company’s size, Click select Help >Support Utilities > File Statistics.
  • In the case of Large companies, they must be purged to remove old transactions/inactive records and decrease the company size.

Section 3: User Account Control (UAC)

  • In Windows 8.1 or 10, UAC can lead to slowness by not accessing Sage to properly access the shared data files and/or program files.

Section 4: IPv6

  • When using Windows 8.1, or 10, you need to disable IPv6 in order to significantly enhance Sage’s performance but can cause issues with Windows OS.
  • Instead, you need to configure Windows OS to prefer IPv4 addresses over IPv6.
  • Consult with your Local IT or Network Administrator for assistance with this.

Section 5: Anti-virus and Firewall Software

  • You need to contact your IT professional or your security software manufacturer for instructions on configuring the exceptions list.

Section 6: Network Connection

  • As you know Sage is not supported on wireless networks. 
  • Using a wireless network can cause performance and/or data integrity issues.
  • Hence It is strongly recommended to use a hard-wired connection within the program.
  • Next, Verify if the server and workstation(s) are on the same domain or workgroup.
  • Now Verify if the server can ping the workstation by name and receive a response from the correct IP address and that the workstation can ping the server by name
  • Now Check the network speed. 
  • The Network-related issues can be confirmed by changing one of the workstations to a local data path and opening a sample company. 
  • In case the performance issues desist after bringing the data over to the local machine, then you need to contact an IT professional to troubleshoot across the network.
  • Now Verify if there are no bad or faulty network cables or network cards in the environment.
  • Be sure to replace any damaged equipment.
  • In case the system experiences performance issues comprising dual network interfaces (active wireless and wired network connections) then you need to disable the unused interface.
  • Consult with your IT professional to diagnose and repair network latency.

Section 7: Memory & Processor Speed

  • Ensure each computer meets at least the minimum processor and RAM requirements for Sage.
  • Within the server, you need to increase the amount of RAM in correspondence with the number of workstations that are accessing Sage; a good idea is to have 2GB of RAM per workstation.
  • You might require more memory/processor speed if working in multiple companies at once.
  • Next Check Sage’s performance on the server; in case the server is slow, every workstation will also be slow.
  • Now Verify if the processor and memory resources have not been artificially limited. This can be done by pressing the Windows+R keys, type msconfig, and then click OK.
  • Note: When prompted to allow the program to make changes, Click on Yes.
  • Click the Boot tab.
  • Select Advanced options.
  • If either Number of processors or Maximum memory is selected, clear them.
  • Click on OK.
  • Click on OK again.
  • Finally, Restart the computer.

Section 8: Hard Drive Space and Virtual Memory

  • Every PC’s hard drive should have a minimum of 1 GB or three times the company database size, whichever is larger, of free space.
  • Running Windows’ Disk Cleanup and Defragmenter utilities on each PC will also ensure free disk space.
  • Every computer’s virtual memory should be set so it at least matches the amount of RAM on the system.
  • In the case of RAM, the more virtual memory a computer has, the better it will run.

Section 9: Background Applications

  • The slow performance can also be due to the Other programs and background applications running at the same time as Sage.
  • In this case, you need to reboot all your systems into Selective Startup to verify if background programs are affecting Sage’s performance.
  • In case the performance improves after doing so, you need to start re-enabling services and startup items one at a time until the one that is the main issue is discovered.

Section 10: Data Entry

  • Start by Changing the Global Options
  • Now click Select Options, and then Global.
  • Next, Select the General tab.
  • Go to Improve Performance and Check one or more options within,
  • Now Clear Smart Data Entry.
  • Finally, Select OK.

Note: When entering transactions, you need to use unique references like: invoice, check, etc.) numbers.

  • Start by Changing the Action Items settings
  • Now click select Tasks> Action Items.
  • Next, click on Options and then the Transactions tab.
  • Clear every Create Event option.
  • Now Select the Startup tab.
  • Clear Display Action Items each time a new company is opened.
  • Finally, Select OK, 
  • Now Close

Section 11: Compatibility Settings

  • Start by Right-clicking on Sage Icon, and then 
  • Now select Properties.
  • Next, Select the Compatibility tab.
  • In case the Run this program in compatibility mode is selected you need to clear it.
  • Note: If the check box is greyed out, you need to simply go over to the next step.
  • Now Select Change settings for all users.
  • If Run this program in compatibility mode is selected you need to clear it.
  • Click Select Run this program as an administrator.
  • Now Select Apply.
  • Finally, Select OK

Section 12: Microsoft .NET Issues

  • Get in touch with an IT professional or Microsoft support for assistance installing/reinstalling Microsoft .NET 4.0.
  • If using Windows 8, Microsoft .NET 4.5 is built into the operating system, so instead of reinstalling it, disable it, reboot, and then re-enable it.

Section 13: The User Does not have the Correct Access

  • Each user in the network needs either Administrator or Power User access

Section 14: Oversized Form (.FRM) File(s)

  • Start by Checking the Forms and individual company directories, 
  • Next sort the files in the directory by size.
  • Any large forms can be moved out of the Forms and individual company directories.
  • If these are custom form files, they can be re-customized.
  • Finally, Verify if the program is more responsive

Section 15: Windows Update

  • Start by Checking for any pending or required Windows updates, Contact an IT professional or Microsoft support for assistance.
  • Now You can view your Windows updates in Windows 10. This can be done by typing updates where it says Type here to search on your bottom taskbar.
  • Go to the menu that appears and you can then click Check for updates.

Also Read-: How to Fix Sage 300 Error 49153

Final Words

So, there you have it people, these are some of the tips, solutions and tricks to FIX SAGE 300 CONNECTING TO DATABASE SLOW ISSUE. However, if you still encounter some issues despite applying the solutions, be sure to visit us at : www.accountingadvice.co and you are sure to easily find the best resolution in no time.

Accounting Professionals & Specialized Experts

Want quick help from accounting software experts? Get in touch with our team members who can install, configure and configure your software for you. Proficient in fixing technical issues, they can help you quickly get back to work whenever you encounter an error in Sage software. Our team is available 24/7 365 days to assist you. To get in touch.

Frequently Asked Questions(FAQs)

Why is the Database Application so Slow?

The main reasons for the slow database application can be because of the redundant query lines, complex or looping syntaxes, lack of proper indexing, the database tables have not been partitioned properly, query deadlocks.

How can one Connect with the Sage 300 Database?

In order to connect with the Sage 300 database, you need to follow the below steps:
🔹 Start by Clicking on  Start > All Programs > Sage 300 > Database Setup.
🔹 Use the administrator password to Sign in
🔹 Now, Click New.
🔹 The New Database dialog box appears.
🔹 Now Enter a database ID for the system.
🔹 This should  be exactly the same as the DSN created by you for this system database.
🔹 The database ID once created cannot be changed after creating the database
🔹 Now Click Select System as the database category.
🔹 Next Click OK.
🔹 Fill in the fields on the Edit SQL Server Database Profile dialog box, as follows:
🔹 WithIn the Description field, you need to enter a description for the database, that should be up to 60 characters. (The program will display the database ID entered by you within the New Database dialog box, but you can change it.)
🔹 Next Select the data source name from the Data Source field, or specify the server name for the database within the Server field. (Only data sources  created by you on your workstation will appear within the list.)
🔹 Note that the names of the data sources should  be consistent on all workstations that use the same Shared Data directories. Also remember to type the name with the same capitalization that you used to define it with the ODBC Administrator.
🔹 Next Enter the Login ID that was created by you  for all Sage 300 users.
🔹 Now Enter the Password for the Login ID entered by you in the previous field.
🔹 Once the user has signed
🔹 Go on to Sage 300, where the System Manager verifies their Sage 300 passwords. 
🔹 Now log onto SQL Server using the Login ID and password specified here.In the case of the Individual Sage 300 users they do not require LOGIN IDs for SQL Server.
🔹 Next Enter the name of the database created by you as the Sage 300 database. 🔹 Ensure you use the same capitalization that you did when you defined it with the SQL Enterprise Manager.
🔹 In case you wish to limit the user access into accounting data, select the Enable Application Security option.
🔹 Once the security  is on, all access to application windows will now be controlled by the Sage 300 security system. With security off, Sage 300 will still use the SQL Server Login ID and Password to gain access to the database, but this doesn’t restrict access to Sage 300 users.
🔹 Once you return to the Database Setup dialog box, the new system database will be displayed within the list.
🔹 Next Click the Security button on order to assign advanced global security for all users of companies that use this system database

Related Posts

Further Reading