This is a funny story… or funny now that I've figured out the problem I had earlier today. This story begins with a DEV/TEST environment for Dynamics GP where I was setting up some scripts to automate the process of restoring from PROD to DEV. I posted a blog article about this earlier this year in fact, but I didn't get into the Clear Companies script.
What is the Clear Companies script?
This is a script that will remove references to companies that no longer exist in a Dynamics GP environment. This script would be used if an old GP database was removed, for instance, and the GP company name still appears in the Select Company drop-down list. In my case, we have 1 more company database in production than we have in DEV (a test company). When I refresh the DEV environment, since that database doesn't exist in SQL, I want to remove the reference to it and that's exactly what the Clear Companies script is designed to do.
The latest version of the script I can find is here and this includes a 2014 update to make this work with Smartlist Designer:
Error: Conversion failed when converting the varchar value 'XXX' to data type int.
Today I was setting up my automated jobs for the first time since we upgraded to GP 2016 in the summer. I had not yet done this as I hadn't needed to refresh the DEV environment for any projects since then.
I was reviewing the scripts and most of them that I kept from GP 2013 were still relevant so I copied my database backups over to the DEV server and ran the SQL Agent job to restore + run the post-restore scripts. The Clear Companies script kept failing. The error above was what I was getting and it was erroring on the Test Company db name from production.
Like any good troubleshooter, I googled the error and found this question on the Dynamics community forums. Richard Wheeler, one of our former MVPs in the GP world, posted the question with the same issue (plus an unrelated issue with TWO). He found the issue to be a table called syDeployedReports.
Derek Albaugh responded with this tidbit of information:
"The main problem with the syDeployedReports table is that it has the 'CompanyID' column as a varchar data type, whereas, every other table that has a companyID or CMPANYID column, such as the SY01500 table (Company Master) that it references, is setup as a smallint data type. The issue with the ClearCompanies script is that it attempts to compare the company ID values from other tables to the SY01500 and runs into an issue when it has a varchar value trying to compare it to a smallint value, thus the error message. This is why the syDeployedReports table is probably 99% of the issues we see running the ClearCompanies script.".
At this point, I was reading this and assuming, naturally, that this table was also likely the cause of my issue. I reviewed the table, which tracks the status of deployed SSRS and Excel reports, and it had a lot of old data in it, from the prior version too and things referencing an older server. I cleared out some info that was no longer relevant, removed the reference to the Test Company and tried again.
The script still failed. I tried various other things like a SQL trace and wasn't any closer to finding the cause. So, then I parsed the pieces of the clear companies script out to see if something else was causing my issue by paying more attention to what it was doing.
Breaking down the Clear Companies script
The first part of the script is finding the GP System Database name(s) from the SY00100 table in each company db, loading them into a temp table ##GPSystem, then deleting the ones where the DB doesn't exist on the server. That wasn't my problem.
The second part is deleting the records in SY01500 for databases that don't exist on the server. This also was not my problem.
The third part and subsequent are where there are different scripts (which seem like there would be a much more efficient way to do this by the way…), each looking for references to companies or databases that don't exist. Each one is creating a delete script. Each section was slightly different, sometimes it's the column name that varied, sometimes the where clause.
Ultimately I saw that in two of the subsections, there was a specific handling for syDeployedReports. Right then I knew that was not my issue, so what I did was take each statement and run it manually to generate the delete scripts. For example, in the section that was "declare companyID_Cleanup1 CURSOR for", I took the next statement, removed all the double quotes and ran it to get a list of tables to update.
I did that for each cursor section so I had a complete list of the delete statements it was trying to run. Then I ran them until I found the one that generated an error.
DOH!
The problem was a custom table that I had built a few months ago when testing a new integration I was building. Guess what the issue was? My custom table had a column "CompanyID" which was a varchar field (I was going to use it for the database name aka "INTERID"). However, since it was using the magic column name of "CompanyID" and was in the DYNAMICS database, the scripts to find all tables with a CompanyID column included this table.
Lesson Learned
I learned two lessons which I will implement as I continue with that integration. One is if I am going to have a custom table where I need to track a company ID, and I don't mean the ID number, I need to name the column in such a way that it doesn't conflict with this script. "GPCompany" is perhaps a generic way to name it, which is not a term used elsewhere.
The second thing is I may move my custom table outside of DYNAMICS entirely. The theory was at the time I wanted to track multi-company integrations in a central spot, so what better place than a non-company-specific database, right? Yes, in theory, although this is a perfect situation to create my own separate db for something like this. I could also use that separate db for cross-company SQL views and things like that and avoid any clutter in DYNAMICS.
So, in the end, I changed the column name in my custom table to avoid the conflict (for now) so that the clear companies script would complete and I will likely drop the table entirely once I resume development there and put it somewhere more appropriate.
