New azurecurve|Ramblings of a Dynamics GP Consultant Theme Launched

azurecurveAs you can see, I have introduced a new theme on this site. Admittedly it is pretty similar to my last one (azure on white), but the previous one was based on the default 2010 WordPress theme which had become rather dated.

The new one gives me access to some new functionality and brings me up to date. I expect there to be some teething problems over the coming days as I find things that were done a certain way because of the previous theme and which will now look/work differently.

I have also taken the opportunity to remove certain elements from the theme and turn them into plugins (see the Posts Archive as an example although there are more to follow).

Please do let me know both what you think of the new theme and if you encounter any issues or peculiarities in how posts or other items are displayed.

azurecurve Posts Archive WordPress Plugin – Beta Testers Wanted

WordPress PluginI recently created a WordPress archive of my Twitter posts using the plugin and theme from Ozh.

Part of the theme was a sidebar posts archive which I quite liked the look of and have now extracted and turned into a WordPress plugin so I can use it on any site (including this one):

azurecurve Posts Archive

Check out the archive in action in the sidebar to the right below the Calendar and above the Tag Cloud.

I am thinking about releasing it via the WordPress Plugins Directory, but before doing so, wondered if anyone might be interested in doing some beta testing? It is a very easy install (upload to plugins folder, activate and then place widget in widget area) and currently has no options although I am willing to create some if necessary (such as the ability to change the display name).

The plugin only reads the number of posts and does not write back to the database so there is no risk.

If you’re interested in beta testing get in touch with me (by email if you have it or use the Contact page) and I’ll send you a download link (I’m not posting publicly as I want to keep numbers under control while it gets tested).

azurecurve Twitter Archive Created

azurecurveApparently Twitter only allows you to download the last 3,200 posts you have made. While I am substantially short of this number at present, I thought it might be useful to keep an archive of these posts myself so I always had access to them.

The CMS I am most used to these days is WordPress and I recently came across a plugin and theme from Ozh which allows you to make an archive of your Twitter which looks like the new Twitter. This azurecurve Twitter Archive is now available; it has been themed to look very like my Twitter page but has all the functionality of WordPress.

Extract ABR Transactions For Import As Statement

Microsoft Dynamics GPthis is a simple script, but I’ve written it about four times now so I figured I’d post it so I can find it easily next time I lose my local copy.

Perfect Image are resellers of the Advanced Bank Reconciliation module from Nolan Business Solutions (along with the other add-ons they’ve written for Dynamics GP) and I often need to demo this replacement for the standard Bank Rec module.

One item I typically show is the auto-Propose function which matches transactions against statement lines in the Reconcile Bank Transactions window (Transactions » Financial » Advanced Bank Reconciliation » Reconcile Bank Transactions).

To do this I need to be able to import statement lines which match the transactions in Dynamics GP; the easiest way of doing this is to extract the transactions.

This can be done with a very simple SQL script:

SELECT
   ORPSTDDT
   ,ORDOCNUM
   ,TRXAMNT
   ,SOURCDOC
FROM
   NCABR012

Once the data has been extracted it can be imported during the demo using the standard ABR Import Statement routine.

SQL Server Cannot Start Following Maintenance

Microsoft SQL ServerI’ve just finished doing some maintenance on one of my development servers and encountered a problem where I could not restart the SQL Server. The development server was a virtual machine running on VMWare which had a second virtual HDD installed for SQL Server to store the database and log files. This disk had been provisioned at 200GB and over time had acquired a lot of files, such as database backups, which became unnecessary and had filled the 200GB.

I needed to get some of the space back on the VM host so I stopped the SQL Server, copied the SQL mdf and log files to a new HDD, deleted the SQL HDD and then set the drive letter of the new HDD to that of the old one.

I then tried to start SQL Server and received the following error:

Windows Event Viewer - FCB::Open failed: Could not open file E:\SQL208RTM\Live\DYNAMICS.mdf for file number 1. OS error: 5(failed to retrieve text for this error. Reason: 15105).Windows Event Viewer – FCB::Open failed: Could not open file E:\SQL208RTM\Live\DYNAMICS.mdf for file number 1. OS error: 5(failed to retrieve text for this error. Reason: 15105).

Continue reading “SQL Server Cannot Start Following Maintenance”

SQL Script To Locate Columns With Different Collation

Microsoft SQL ServerA while ago I posted about a problem with a collation conflict on a couple of columns in the Tax table. It seems I posted about how to fix the problem, but it seems I didn’t post how I found the problem columns.

I did this with a fairly simple SQL script:

DECLARE @Collation SYSNAME SET @Collation = 'SQL_Latin1_General_CP1_CI_AS'

SELECT
   TABLE_NAME AS 'Table'
   ,COLUMN_NAME AS 'Column'
   ,DATA_TYPE AS 'Data Type'
   ,COLLATION_NAME AS 'Collation Name'
FROM
   INFORMATION_SCHEMA.COLUMNS
WHERE
   DATA_TYPE IN ('varchar','char','nvarchar','nchar','text','ntext')
AND
   COLLATION_NAME <> @Collation

Deactivate All Reports (Except PM EFT Payment Register) In All Companies

Microsoft Dynamics GPMicrosoft Dynamics GP includes a large number of reports which will automatically print off when a transaction or batch is posted; and some postings produce many different reports.

When I implement a new client I typically leave them on during training with an instruction to identify which ones they want to use after go-live as I will switch off any I’m not told to leave on.

I used to do it the other way and tell people to let me know which ones they wanted switched off; however, this lead to all reports being left on and then it being mentioned a year or so down the line that they get all these reports printing that they don’t want.

So now, all get switched off unless I am specifically told to leave them on. As I’ve dealt with clients with many companies (I think the largest is 180 companies) this is not something I want to do manually.

Fortunately, the settings for whether the reports should print is stored in SQL table which means a SQL script can be written to switch them off in bulk.

Continue reading “Deactivate All Reports (Except PM EFT Payment Register) In All Companies”

Microsoft Dynamics GP 2013 R2 Coming Soon

Microsoft Dynamics GPThe next version of Microsoft Dynamics GP, 2013 R2, is going to be released soon. I can hardly wait, but, unfortunately, I have to 🙁

The Dynamics GP Support and Services Blog have posted an article today outlining their intended blog post schedule where they are going to run through the upgrade process and all of the new features which are due in the coming version.

  • April 29 – Lifecycle
  • May 1 – Upgrade Planning
  • May 6 – Known Upgrade Issues
  • May 8 – Version Checks & Upgrades
  • May 13 – Database Upgrade
  • May 15 – Modified Dictionary Upgrade
  • May 20 – Upgrade Troubleshooting
  • May 22 – GP is upgraded – what’s next?
  • May 27 – Upgrading Web Client
  • May 29 – Tables Changes in R2 – Review the SDK
  • June 3 – Identity Management
  • June 4 – Azure Backups
  • June 5 – New Workflow
  • June 10 – Requisition Management
  • June 11 – OLE Document Attach
  • June 12 – Human Resource Time Entry
  • June 17 – Project Accounting Time Entry
  • June 18 – General Ledger
  • June 19 – Payables Management
  • June 24 – Receivables Management
  • June 25 – Bank Reconciliation
  • June 26 – Analytical Accounting
  • July 1 – Fixed Assets
  • July 2 – Sales Order Processing
  • July 3 – Inventory Control
  • July 8 – Purchase Order Processing/Encumbrance Management
  • July 9 – Email or Print any report in Word
  • July 10 – SmartList Designer 2.0
  • July 15 – Office 365
  • July 16 –Take Company Offline
  • July 17 – Ribbons in Desktop

Integration Manager – An Item With The Same Key Has Already Been Added

Microsoft Dynamics GPI was working with a client the other day with Integration Manager to import some purchase orders when we received the following error on some of the purchase orders:

5. 820909 Insert Failed 0. Seconds
DOC 4 ERROR: An item with the same key has already been added.

The vast majority of the documents integrated without problem. When we examined the documents which failed, we determined that those which failed were those which had a currency of Euro.

Integration Manager errors are typically very unhelpful and this was no different; the first thing we checked was to see if the PO Number existed in GP, but as we expected it did not.

We did some investigating and found that the Euro Exchange Rate’s Transaction Rate Default in GP was defined as Exact Date; I was able to update this to Next Date after which the integration stopped producing errors.