Great Plains Dynamics 8.0 and Company List

I want to know how to control the company list users see once they have logged in. I recently moved the GP from a physical to a virtual server and did not transfer one of the old databases, the Test Company, however it still shows up as one of the available companies to login to. Where can I control which companies show up in this list. [I know 8.0 is very old so no comments needed on that issue please--thank you.]
GP-Companies.jpg
LVL 26
Lionel MMSmall Business IT ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Victoria YudinOwner / Dynamics GP ConsultantCommented:
The list for the available companies is taken from the SY01500 table in the DYNAMICS database. You can remove the test company from there and it will no longer show up in the drop down when users log in.
Lionel MMSmall Business IT ConsultantAuthor Commented:
Will you please tell me how to do that--thanks.
Victoria YudinOwner / Dynamics GP ConsultantCommented:
Make a backup of your DYNAMICS database in SQL.

Then run the following query in SQL:

delete from DYNAMICS..SY01500 where CMPNYNAM = 'Test Company'

Open in new window

Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Lionel MMSmall Business IT ConsultantAuthor Commented:
OK when I did that I now have two lines with "Happiness Farms, Inc". Test Company is gone but now I have a double entry
Lionel MMSmall Business IT ConsultantAuthor Commented:
I checked this from a second system--both have the double entry but one has a different list of companies than the other--if the SY01500 table is where this list is read and kept how can two different systems have two different lists/options of companies?
Victoria YudinOwner / Dynamics GP ConsultantCommented:
The list could be different if the user logging in is different. Have you logged all the way out of GP and back in before testing? How many rows are in the SY01500 table now?

select * from DYNAMICS..SY01500 

Open in new window

Lionel MMSmall Business IT ConsultantAuthor Commented:
I ran the suggested query--and there are only 3 rows see picture (Company List)
But why have I gone from one "happiness farms" to two (see other 2 pictures from 2 different users) and why is the list different for these two users, one is logged on at the SQL server (sa) and the other is from a PC (Kim)
Company-List.jpg
Company-List-User-sa.jpg
Company-List-User-Kim.jpg
Victoria YudinOwner / Dynamics GP ConsultantCommented:
User Kim most likely does not have access to company Old HFI History, so I would not consider it an issue that the lists are different for different users. You would need to compare with the same GP user for it to be a valid test.

I have not seen the doubling up of the companies like this  before, but I can think of a few things to try:

1. Get all users out of GP and run the following script:
 delete from DYNAMICS..ACTIVITY
 delete from DYNAMICS..SY00800
 delete from DYNAMICS..SY00801
 delete from tempdb..DEX_LOCK
 delete from tempdb..DEX_SESSION

Open in new window


2. There is a script to clear out all references to a deleted company available here: http://support.microsoft.com/kb/855361.
Lionel MMSmall Business IT ConsultantAuthor Commented:
Please excuse the delay--I will give your last recommendation a try tonight when everyone leaves--thank you.
Lionel MMSmall Business IT ConsultantAuthor Commented:
OK I tried the suggestion and still there are still 2 listing of Happiness Farms. I also looked at the suggested link and those are for GP versions other than 8 which is what I have. Each command gave the result (0 row(s) affected). I stopped and restarted SQl Server after doing this to see if that would help--no luck.
Victoria YudinOwner / Dynamics GP ConsultantCommented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lionel MMSmall Business IT ConsultantAuthor Commented:
THANK YOU--I tried Method 2 and that did it
Victoria YudinOwner / Dynamics GP ConsultantCommented:
Excellent - thanks for the follow up.
Lionel MMSmall Business IT ConsultantAuthor Commented:
Summary of all of the above
Make a backup of your DYNAMICS database in SQL.

Then run the following query in SQL
delete from DYNAMICS..SY01500 where CMPNYNAM = 'Test Company'

Use this query to show a list of all the companies in the Dynanmics DB
select * from DYNAMICS..SY01500

These following commands may clean up the Dynamics DB (but did not work for me)
delete from DYNAMICS..ACTIVITY
delete from DYNAMICS..SY00800
delete from DYNAMICS..SY00801
delete from tempdb..DEX_LOCK
delete from tempdb..DEX_SESSION

Script to clear out all references to a deleted company available here (not for GP 8.0) http://support.microsoft.com/kb/855361

This finally did it for my particular issue                                            
Try this KB article: http://support.microsoft.com/kb/968058
[I used Method 2]
Lionel MMSmall Business IT ConsultantAuthor Commented:
My Solution is not mine, it is simply a compilation of all the suggestions victoria_y gave me--thanks for all your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Dynamics

From novice to tech pro — start learning today.