This post is part of the series on MySQL Snippets.
Like the other …Snippets series, this one may not get many posts as I’ll be using it to record things I need to remember. In thie series, I’ll be posting MySQL snippets; usually ones which highlight a difference between the MySQL and Microsoft SQL query syntax.
| MySQL Snippets |
|---|
| Update Statement |
I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.
I tend to do data manipulation in SQL Server rather than Excel, as it is generally easier to do it in SQL where I can run commands sequentially. However, there are times when I do need to do it in Excel.
I recently needed to replace one part of a string of text and really had trouble doing so. This was because I am used to using REPLACE in SQL and str_replace in PHP and expected the REPLACE function in Excel to allow similar replacement. However, I rapidly found that this was not the case.
I did some exploring and found that the command in Excel is actually SUBSTITUTE:
=SUBSTITUTE(A2,"Apples","Oranges")
This will replace the word apples with oranges in the string in cell A2.
I did some work for a client recently where we created a new check remittance layout and showed them how to enable emails in Microsoft Dynamics GP. When we deployed ive however, we encountered a problem whereby they client could not enable, en masse, the creditors for emails as the button was missing fromthe action pane in the Creditors navigation list.
On the standalone test system the E-mail Settings option on the Modify section of the navigation pane was present:
But on live, this option is missing:
While comparing the two systems, I realised that the Vendors Not Submitted and Vendors Pending Approval navigation ist favourites were missing from live, but present on test (just like hte E-mail Settings option.
We had two small projects running in parallel; one for the remittance and emails and the other for workflow approvals. We had identified the navigation list favourites had been missing from the test system and had planned fixing them when workflow was deployed to live, but the remittance project was signed off first.
The navigation list favourites is a known issue after upgrade, but hadn’t been fixed as the client had not been using the navigation lists and so hadn’t noticed they were missing. We fixed the navigation ists on live and were then abe to use the email settings option to enable email on creditors en masse.
I’m not sure how, but I stumbled across an article on the ERP Software Blog by Michael Krasivsky of The Resource Group where he has posted a more efficient version of the Grant.sql script.
For those who don’t know, the Grant script is used to apply the correct permissions to SQL objects (tables, views and stored procedures) used by Microsoft Dynamics GP; this script can take a while to run as it utilises a cursor to update the permissions on every single SQL objects in the database regardless of the current permissions.
What Michael has done is create a script which only selects the SQL objects which do not have the correct permissions and grant them on those objects only; this makes the script much faster to run, as in most cases the majority of the SQL objects will already have the correct permissions so it is only a minority of objects which need to be updated.
Earlier this year I added SmartConnect from eOne Solutions to a new demo vm (I’m a bit behind with posts). I was able to create the data source and integration without any issues, but then encountered an error when running any of the integrations in SmartConnect:
The source was not found, but some or all event logs could not be searched. To create the source, you need permission to real all event logs and to make sure that the new source name is unique. Inaccessible logs: Security
On a new installation, permissions isn’t all that unusual and was confirmed when I found a post from eOne on their knowledge base with steps to resolve.
The fix is to add the service account user to the local administrators group on the machine on which SmartConnect is being run and then launch SmartConnect using right click, run as administrator. You only need to run it as admin the first time after adding the service account as a local admin.