Home accounting software dynamics gp troubleshooting gp errors microsoft dynamics gp

Oops! Wrong Company Showing in Dynamics GP Drop-Down? Here's a Fix

Updated: • 0 • min read
Table of Contents

Wrong Company Showing Up in Your Dynamics GP Drop-Down? Let’s Fix It!

Dynamics GP
image just illustration

We’ve all been there. You’re trying to get work done in Dynamics GP, and suddenly, the wrong company is staring you in the face in the drop-down menu. Talk about frustrating! Luckily, there’s a pretty straightforward fix for this common issue, and we’re going to walk through it step by step.

The Problem: Phantom Companies

Sometimes, Dynamics GP can get a little confused and display companies that no longer exist on your SQL Server. This can happen after database migrations, server changes, or other system updates. These “phantom companies” clutter your drop-down and can lead to confusion and errors.

The Solution: A SQL Script to the Rescue!

The fix involves running a SQL script called ClearCompanies.sql. This script essentially cleans up your Dynamics GP system by removing references to any companies that don’t actually exist on your SQL server. Think of it as a digital spring cleaning for your Dynamics GP environment.

The Script: ClearCompanies.sql

Below is the ClearCompanies.sql script. Important Note: Before running any SQL script, always back up your databases. This is crucial to avoid potential data loss.

set nocount on

/* Remove all references in the company master table (table SY01500) for databases that do not exist on the Microsoft SQL Server server. */
delete DYNAMICS..SY01500 where INTERID not in
 (select name from master..sysdatabases)

/* Remove the tables from the DYNAMICS database if the CMPANYID value of the table does not match any company IDs in table SY01500. */
USE DYNAMICS
-- ... (rest of the script as provided in the JSON)

Understanding the Script (A Simplified Explanation)

The script does a few key things:

  • Checks the Company Master Table: It looks at the SY01500 table (which stores company information) and removes any entries for databases that are no longer on your SQL Server.
  • Cleans Up the DYNAMICS Database: It searches for and removes any references to these phantom companies in other tables within the DYNAMICS database. This ensures that all traces of the non-existent companies are gone.
  • Removes Stranded Business Alert References: Cleans up orphaned records in Business Alerts tables related to non-existent companies.

Step-by-Step Instructions:

  1. Back Up Your Databases: Seriously, do this first. You don’t want to regret skipping this step.

  2. Copy the Script: Copy the entire ClearCompanies.sql script provided above.

  3. Open SQL Server Management Studio (SSMS): Connect to your SQL Server instance.

  4. New Query: Open a new query window in SSMS.

  5. Paste the Script: Paste the copied script into the query window.

  6. Execute: Run the script.

  7. Verify: Check your Dynamics GP drop-down. The phantom companies should be gone!

Troubleshooting Tips:

  • Permissions: Make sure you have the necessary permissions to execute SQL scripts on your server.
  • Database Name: Double-check that the DYNAMICS database name in the script matches your actual database name.
  • Errors: If you encounter any errors, review the error messages carefully. They usually provide clues about what went wrong. If you’re unsure, consult with a SQL Server expert.

Why this Fix is Important

Having incorrect companies listed in your Dynamics GP drop-down can lead to several problems:

  • Data Integrity Issues: You could accidentally post transactions to the wrong company database.
  • Reporting Errors: Your reports might include data from non-existent companies, leading to inaccurate results.
  • User Confusion: It can be confusing for users to see companies they shouldn’t have access to.

Beyond the Fix: Keeping Your Dynamics GP Tidy

This script is a great reactive solution, but proactive maintenance is even better. Regularly reviewing your SQL Server databases and removing any unused or outdated databases can prevent these phantom companies from appearing in the first place.

Alternative Solutions:

While the script is a direct solution, alternative methods might involve manually removing the company entries through the Dynamics GP interface. However, this can be more time-consuming and might not catch all the stranded references within the database.

A Final Word

Fixing the wrong company showing in your Dynamics GP drop-down is a relatively easy process with the ClearCompanies.sql script. By following the steps outlined above, you can quickly resolve this issue and ensure your Dynamics GP system is running smoothly. Don’t forget to back up your databases before running any SQL scripts!

We hope this guide was helpful! Do you have any other Dynamics GP tips or tricks to share? Leave a comment below and let us know! We love to hear from our readers and learn from your experiences. Come back and visit again soon for more helpful information!

Post a Comment