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
8 comments:
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?
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
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
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
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
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
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.
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
Post a Comment