Solved

Great Plains Dynamics 8.0 and Company List

Posted on 2013-12-30
15
635 Views
Last Modified: 2014-01-13
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
0
Comment
Question by:lionelmm
  • 9
  • 6
15 Comments
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 39745986
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.
0
 
LVL 24

Author Comment

by:lionelmm
ID: 39746200
Will you please tell me how to do that--thanks.
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 39746235
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

0
 
LVL 24

Author Comment

by:lionelmm
ID: 39747284
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
0
 
LVL 24

Author Comment

by:lionelmm
ID: 39747291
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?
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 39747723
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

0
 
LVL 24

Author Comment

by:lionelmm
ID: 39748091
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
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 39748107
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.
0
 
LVL 24

Author Comment

by:lionelmm
ID: 39759281
Please excuse the delay--I will give your last recommendation a try tonight when everyone leaves--thank you.
0
 
LVL 24

Author Comment

by:lionelmm
ID: 39763896
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.
0
 
LVL 18

Accepted Solution

by:
Victoria Yudin earned 500 total points
ID: 39764588
0
 
LVL 24

Author Comment

by:lionelmm
ID: 39764969
THANK YOU--I tried Method 2 and that did it
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 39764976
Excellent - thanks for the follow up.
0
 
LVL 24

Assisted Solution

by:lionelmm
lionelmm earned 0 total points
ID: 39765039
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]
0
 
LVL 24

Author Closing Comment

by:lionelmm
ID: 39776063
My Solution is not mine, it is simply a compilation of all the suggestions victoria_y gave me--thanks for all your help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

On July 14th 2015, Windows Server 2003 will become End of Support, leaving hundreds of thousands of servers around the world that still run this 12 year old operating system vulnerable and potentially out of compliance in many organisations around t…
Automatically creating a Trello card using data from a Microsoft Dynamics CRM record turned out to be an easy project that yielded great results.  Here's how I did this for an internal team at General Code.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now