When a database is created, it has a logical name assigned to it which will match the physical name. However, when working with Microsoft Dynamics GP, we often create both a live and test database and then populate the settings in the live database and replicate over the top of the test one.
Or on occasion we have created a template database which then gets copied when a new company is created (this method is often used for clients who have a substantial amount of setup in third party modules which the PSTL Company Copy doesn’t cater for.
The problem with both of these is that when a database is restored elsewhere it brings it’s logical name with it; meaning a mismatch between the logical and physical names which causes problems when backing up and restoring databases.
However, all is not lost; it is possible to change the logical name of a database using a simple SQL script. The script, below, has two ALTER DATABASE commands, one for the data file and the other for the log file.
I am changing the logical name from GPST15R2 to GPSP15R2 on both files (see highlighted text):
/* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ ALTER DATABASE P15R2 MODIFY FILE ( NAME = [GPST15R2Dat.mdf] ,NEWNAME = [GPSP15R2Dat.mdf] ) GO ALTER DATABASE P15R2 MODIFY FILE ( NAME = [GPST15R2Log.ldf] ,NEWNAME = [GPSP15R2Log.ldf] ) GO
As always when running a SQL script against a database, make sure you have a good backup and perform a test afterward to make sure there are no problems.
Merry Christmas to everyone in the Dynamics GP community.
Image courtesy of Sira Anamwong at FreeDigitalPhotos.net
One of Perfect Image’s clients is merging with another organisation and I’ve been onsite a few times to help them configure the new companies and import data. One of the items on my list was to use Integration Manager to import the new chart of accounts. While doing this I used VBA to extend the integration to map between the chart of accounts in the new system to the new format and numbering wanted in Microsoft Dynamics GP.
It was when I came to test the integration with the mapping active that I encountered my problem:
ERROR: Error Executing Script 'Before Integration' Line 17: - Object required: 'gpObj' Error Executing Script 'Before Integration' Line 17: - Object required: 'gpObj' Object required: 'gpObj' Integration Failed Integration Results 0 documents were read from the source query. 0 documents were attempted: 0 integrated without warnings. 0 integrated with warnings. 0 failed to integrate.
When I was doing the training I was explaining what a Temporary Creditor (or Vendor for the American readers) was and why I hated them so much. As I was talking I was showing that even through the temporary creditor itself had been deleted, the history for it remained.
It was when I tried to open the Payables Transactions navigation list that I got the below error:
Microsoft Dynamics GP[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into 'VENDNAME', table 'tempdb.dbo.##181163', column does not allow nulls. INSERT fails.[Microsoft][SQL Server Native Client 11][SQL Server]Cannot insert the value
This particular client uses the eConnect incoming queue to integrate journals and payables invoices into Dynamics GP from a housing management system.
After installing eConnect and configuring the incoming queue I set about doing a test to ensure it was working.
Unfortunately, it didn’t.
A while ago I came across a bug in the Creditor (or Vendor for my American readers) Maintenance window where when a creditor is deleted the EFT information is not deleted; this has caused problems for a number of clients and I finally decided I needed to do something about it.
The result is a SQL trigger on the Creditor Master (PM00200) table; when a creditor is deleted the trigger runs and deletes all records in the Address Electronic Transfer Funds Master (SY06000) table are then deleted:
CREATE TRIGGER dbo.utr_AZRCRV_DeleteSY06000 ON dbo.PM00200 AFTER Delete AS /* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ DELETE ['Address Electronic Transfer Funds Master'] FROM SY06000 AS ['Address Electronic Transfer Funds Master'] INNER JOIN deleted ON deleted.VENDORID = ['Address Electronic Transfer Funds Master'].VENDORID GO
This the avoids the possibility of a new creditor record being linked to a different creditors bank details. As always with a script (especially one which deletes information, make sure it is tested and you’re happy with how it works before releasing live.
I got a new laptop at work recently (i7 16GB RAM 1080P) and have finally been able to pretty much transition away from VMWare to Hyper-V (which I use at home for my test system); I only have a final couple of VMs to recreate or migrate. It has not all been smooth sailing however, as I have found that if the laptop goes to sleep or is switched off the VMs cannot be started again.
I think this is something to do the AD group policy rather than an inherent problem as my Surface Pro 3 works fine.
The solution is to restart the Hyper-V Virtual Machine Management service, but going into Services every time for this was soon going to get annoying, so instead I created a batch file I can run from a keyboard shortcut containing the following two lines:
net stop vmms net start vmms
These commands stop and then start the service allowing me to use the VMs.
Instead, there is a command you can run at the command line to change the key:
cscript "C:\Program Files (x86)\Microsoft Office\Office15\OSPP.VBS" /inpkey:officekey
Replace the highlighted section above to the new key you want to put in place; once done you can then log into Office and activate the software online.
When I joined Perfect Image they had, unfortunately, standardised on VMware Workstation for virtualisation and I have been stuck using it ever since (we have recently been transitioning to Hyper-V which I intend to start using exclusively once I can get all the VMs either converted or recreated).
I was away for a demo the other week and the night before I tried to launch the demo VM to do some final preparation when I got the following error:
GP Demo - VMware Workstation>/p>
Not enough physical memory is available to power on this virtual machine with its configured settings.
This error message was a baffling one as I had run the VM a couple of days previously with no problems and had made no changes to the laptop in the intervening period.
The fix was a rather odd, yet simple one: launching VMware Workstation using Run as Administrator allowed the application to launch and the VM work without further problem.
Since doing this I have been able to launch the VM at any time under my usual security context.
I had an issue to deal with for a client recently where messages submitted to eConnect were no longer appearing in Microsoft Dynamics GP. I did the usual things of checking the Windows Event Log and ensuring that the two eConnect services were running.
One item I tried was to submit a test message to eConnect which worked fine; so it worked for the perfectimage user I was logged in as, but no-one else. I eventually found the answer in the Properties of the econnect_incoming queue:
The permissions for the perfectimage account (Full) were still configured correctly, but, somehow, the Send Message permission on Everyone had been removed. Once this setting had been added back, people were able to submit messages to the queue without further problem.