Tuesday, September 14, 2010

Removing Dynamics GP companies that no longer exist

This is a common problem and easy to fix, but I had a very hard time finding the KB article and corresponding SQL script on PartnerSource, so I'm posting this for my own reference so that I don't have to dig around using the often unreliable horrible PartnerSource search.

I had a call today with a client that has over 120 GP company databases.  Because there are so many databases, and so much data, they don't maintain all of the databases in their multiple test environments.

In the test environment we were working in, there were 22 company databases setup, but naturally GP still thinks there are 120, and displays them all in the company listing during login.  Normally that wouldn't be an issue, but we were working with a 3rd party product that scanned all databases, and if a database wasn't present, an error would occur.

So this scenario is essentially the same as if a GP company database had been deleted through SQL Server Management Studio, instead of through GP.  The records for the company database exist in the GP tables, but the corresponding master..sysdatabases records and physical databases "no longer" exist.

The KB article is titled "Deleting a company in Microsoft Dynamics GP", and is supposedly KB Article 855361.  At least that's what it says on the version I found--there might be more than one version, as the client had the same script, but with a different KB number in his notes.

After more searching, I found this article on Customer Source, and this apparently identical article on PartnerSource that appears to reference the same script.  But I can't find a KB Article number.  Here is a File Exchange link to download the SQL script referenced in these two articles.

Despite my repeated attempts to search for 855361, KB855361, and even "Deleting a company", I couldn't seem to find the article through the PartnerSource search.  Fortunately a Google search turned up a link to the article, and I was able to access it directly via the URL linked above.


UPDATE: It looks like all of the links above no longer work.  Here is a link to download a zip file containing the KB article and SQL script.  Please note that both of these files are very old--from 2009-2011, so they may not be applicable to GP versions newer than GP 2010, and you will want to be careful to review and test them prior to using them in a real environment.

https://1drv.ms/u/s!Au567Fd0af9Tn22I-s9ZCibHbjv7


at

8 comments:

  1. eldar108April 11, 2018 at 6:20 AM

    Hi Steve, I tried to use the URL to get to the sql script but it says that I am denied access.
    My organisation is a silver partner; should this help to gain access to the file?

    ReplyDelete
  2. Steve EndowApril 11, 2018 at 12:54 PM

    Hi,

    It looks like the KB articles and file downloads have all been removed by Microsoft.

    I have the KB article and script if you would like them, but it looks like they are quite old--the script is dated 2009.

    Email me at steveendow (at) gmail if you would like me to send them to you.

    Thanks,

    Steve

    ReplyDelete
  3. SheilajrossMay 31, 2018 at 4:42 PM

    I was just on the site & downloaded a newer version of this script that says it was updated to work with GP 2016 but when I try to run the script there are many errors. Would you mind looking at the new script? When it's pasted into my Query there is a lot of red text.

    https://mbs.microsoft.com/Files/customer/GP/Downloads/TaxUpdates/ClearCompanies.sql

    Thanks,
    Sheila

    ReplyDelete
  4. Steve EndowMay 31, 2018 at 8:28 PM

    Hi Sheila,

    I downloaded the script and tested it on my GP 2016 server and it worked okay for me. Not sure why it may be giving you errors, but you could try and run each section separately to see where the problems are coming from.

    The red text in SSMS is due to the dynamic SQL it is building--SSMS considers it all a string.

    Steve

    ReplyDelete
  5. SheilajrossJune 1, 2018 at 11:14 AM

    Thank you Steve. I'll give it another try. I'm no SQL expert and I was having a difficult time breaking it down into sections.

    Sheila

    ReplyDelete
  6. Steve EndowJune 1, 2018 at 8:14 PM

    Hi Sheila,

    If you can't get it to work, let me know and we can have a GoToMeeting session to take a look.

    https://precipioservices.com/contact-us/


    Steve

    ReplyDelete
  7. SheilajrossJuly 14, 2018 at 10:41 AM

    Here is the script that has an issue out of the KB

    --/* Clear out all tables in DYNAMICS database that have a companyID field
    -- that no longer matches any Company ID's in the SY01500 */
    USE DYNAMICS
    declare @companyID char(150)
    declare companyID_Cleanup CURSOR for
    select 'delete ' + o.name + ' where companyID not in (0,-32767)'
    + ' and companyID not in (select CMPANYID from DYNAMICS..SY01500)'
    from sysobjects o, syscolumns c
    where o.id = c.id
    and o.type = 'U'
    and c.name = 'companyID'
    and o.name <> 'SY01500'
    set nocount on
    OPEN companyID_Cleanup
    FETCH NEXT from companyID_Cleanup into @companyID
    while (@@FETCH_STATUS <>-1)
    begin

    exec (@companyID)
    FETCH NEXT from companyID_Cleanup into @companyID

    end

    DEALLOCATE companyID_Cleanup
    go

    What returns is the following error:
    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value 'CTTC' to data type int.

    ReplyDelete
  8. Steve EndowJuly 17, 2018 at 9:11 AM

    Hi Shiela,

    Can you run these queries and see if the value of 'CTTC' is appearing anywhere?

    select CMPANYID, * from DYNAMICS..SY01500

    select * from sysobjects o, syscolumns c
    where o.id = c.id and o.type = 'U'
    and c.name = 'companyID' and o.name <> 'SY01500'

    The only int value I'm seeing in the query is the CMPANYID in DYNAMICS..SY01500, so I'm puzzled why the conversion error is occurring.

    Submit a note at https://precipioservices.com/contact-us/ if you want to do a GoToMeeting.

    Thanks,

    Steve

    ReplyDelete