I seem to be having a problem updating specific fields under Company Maintenance using a SQL script. If I use the DMT tool, it updates without any issue. However, when I use the command below, SQL thinks the job step completed successfully but the values are not updated:
Update Ice.SysCompany set EntSearchURL = ‘‘http://sitename’’
Update Ice.SysCompany set EpicorHelpURL = ‘‘http://sitename’’
I am able to update other fields on the company maintenance tab. Any help is greatly appreciated. Thanks.
What happens if you stop the application server then perform the update?
I would be remiss in not stating that updating the database directly is not recommended.
Apologies on this next bit if you are a seasoned DBA…
If you are applying updates to another database perhaps, it is good practice to do it, wrapped in a transaction.
e.g.
Begin Tran
SQL code here
then either using ROLLBACK , if for some reason your update changed the wrong data, or COMMIT if you are 100% sure the change you put in worked in correct fashion. Remember if you leave the transaction in an uncommitted state, you can view the changes with a SELECT, but the table will appear blocked to connections/users.
All is well except for a couple items in step 5. As you know, restoring a db to another dev instance requires us to change values to ensure proper redirection. System agent among other things need changing and I was able to script this. However, the
EntSearchURL and EpicorHelpURL values will not update. I am probably missing something from the script in my original post to update these two specific values.
Hmm just did a test here and I get the same result. I am wondering if this is a situation that the information is being populated from the app server configuration, much like the SSRS settings.
When I look at the syscompany table I can see the field populated, but is shows nothing on screen, just as you mentioned.
You may need to use powershell to redeploy enterprise search on your dev environment.
If you don’t have the Enterprise ES search licence, I don’t think it really would make a difference as you can’t modify the BAQs/indexes in the standard ES anyway. You could in E9, but not in 10.
Same goes for the Help, unless you want to be doing some development around help. (i.e. annotations)
I what I’m saying, is that Help and ES are not really required and you should just not enable them in your dev app server config.
If anyone has another opinion, feel free to chime in.
I was able to update the SSRSReportName without any trouble as this was the only SSRS value needing changed. I wonder what I’m doing differently than how DMT updates those values. If I look at the csv used for DMT, I notice it has the Company value as one of
the columns. I wonder if I should specify the Company name in my sql script…
I even tried this process to a different Epicor instance with the same results so I’m not sure redeploying those would make a difference. As of now, I can use DMT but ultimately want it 100% automated.
adding the company will not make any difference, apart from the fact that the query will only update the specific company.
If you look at the data after you have run the query and it is committed you will see it does actually update the data, but it just does not show in the UI.
Perhaps, you could try a trace in DMT and see what is going on there.
If it works for the DMT, then I’d be inclined to call a power shell script for DMT from SQL agent, and be done with it, as I said earlier if you don’t plan on doing any development on your enterprise search or help then I would just skip it, Although it is always nice to get these sorts of things working in their entirety.
I was bound and determined to figure this one out in SQL and I did it HA! It appears that the GUI pulls that value from a different table. Seems strange why it would be coded this way. So, updating the actual value as below:
Update Ice.SysCompany set EntSearchURL = ‘siteaddress’’
Update Ice.SysCompany set EpicorHelpURL = ‘‘siteaddress’’
Update the GUI:
Update Ice.SysConfig set SysCharacter01 = ‘‘siteaddress’’ where Key1 = ‘‘ESUrl’’
Update Ice.SysConfig set SysCharacter01 = ‘‘siteaddress’’ where Key1 = ‘‘HelpURL’’
…just in case anyone else wants to avoid this slight annoyance.
So field help, in this scenario is “Field No Help” That is annoying.
You might want to still look at the app server config, my thought is that redeploying will overwrite the information in the Ice.SysConfig. I guess this is a great example as to why updating the database directly is not such a hot idea.
I am sure there is a thread around the best practice for copying live to test etc…if not here on Epicare. If I come across it I will update.
Doesn’t really go into detail about what needs to be changed after the restore but I’m pretty much doing the steps. I think I have all my ducks in a row. The only thing I didn’t do is stop and start the task agent service which I can easily add to my sql job.
