Tag Archives: SQL Script

Copy Microsoft Dynamics GP Live Company To Test Automatically

Posted on 11th November 2011 by Ian Grieve

Backing up the a Microsoft Dynamics GP company to test is, unfortunately, not as simple as backing up one database and restoring it to the Test one. There are two scripts which need to be run after doing so; the first changed the INTERID and Company Name and the second changes the database owner to dynsa.

At my last company, we decided to make this process as easy as possible for clients, so we started creating a SQL Agent Job which would do the backup, restore and run the scripts with minimal effort or could even run on a scheduled basis. The basic purpose was to give the client a test or training system which was always, or could very quickly be, up to date. Continue reading →

Get Next Journal Entry Number

Posted on 27th October 2011 by Ian Grieve

A colleague is currently working on some development for Microsoft Dynamics GP and needs to create a journal.

I did some digging around the company database looking for the correct way to get the next Journal Entry number and found a function called glGetNextJournalEntry.

A little work and I was able to supply the following to the developer for him to wrap into a stored procedure to get the Journal Entry number and make sure there were no issues in GP;



DECLARE @l_tINCheckWORKFiles tinyint = 1

DECLARE @I_iSQLSessionID int = USER_SID()

DECLARE @O_tOUTOK tinyint

DECLARE @IO_iOUTJournalEntry int = 1

DECLARE @O_iErrorState int
EXECUTE glGetNextJournalEntry

   @l_tINCheckWORKFiles

   ,@I_iSQLSessionID

   ,@IO_iOUTJournalEntry OUTPUT

   ,@O_tOUTOK OUTPUT

   ,@O_iErrorState OUTPUT

SELECT @IO_iOUTJournalEntry AS 'NJRNLENT', @O_tOUTOK AS 'OUTOK', @O_iErrorState AS 'ERROR'

Posted in Dynamics, GP, Microsoft | Tagged Development, Financials, General Ledger, GP, Microsoft Dynamics GP 2010, SQL Script | 3 Comments

Enabling Email Cheque Remittances For All Suppliers

Posted on 19th October 2011 by Ian Grieve

In two previous posts I discussed testing Microsoft Dynamics GP 2010′s new Email Remittance functionality; first, by setting up creditors with an internal email address and, then, by transferring the email address from the INET1 field to the new EmailToAddress field.

The other script which I used, and forgot to post, was the one which configures all of the creditors, who are not currently configured for email remittances Continue reading →

Posted in Dynamics, GP, Microsoft | Tagged Check Remittance, Creditors, Email, GP, Microsoft Dynamics GP 2010, Payables Management, Payment Run, Purchasing, SQL Script | 3 Comments

Locked Payables Transaction: You cannot display this record while another user is editing it

Posted on 29th September 2011 by Ian Grieve

With sites using Microsoft Dynamics GP over Citrix Presentation or XenApp, where they have set Citrix to time users out after a certain period of inactivity, we occasionally see the following error message when another user tries to open the voucher in Payables Transaction Entry (Transactions >> Purchasing >> Transaction Entry);

This document number already exists. Please enter a new document number.

Continue reading →

Posted in Dynamics, GP, Microsoft | Tagged Errors, GP, Payables Management, Purchasing, SQL Script, Transaction Entry | 2 Comments

Home Screen Connect Error – Server Error in ‘/lus’ Application.’

Posted on 15th September 2011 by Ian Grieve

A client testing Microsoft Dynamics GP 2010 R2 prior to going live rang to say they were getting a runtime error when logging in. I dialled in and checked the home screen and couldn’t see any problem with the dynsa home screen so asked for a screenshot showing the error;

The screenshot they sent showed the below error message;

Home Screen Connect Error - Server Error in '/lus' Application.'

Continue reading →

Posted in Dynamics, GP, Microsoft | Tagged Configuration, GP, Microsoft Dynamics GP 2010, SQL Script, System | 2 Comments

Going Live With Remittances By Email – Transferring Email Addresses

Posted on 4th August 2011 by Ian Grieve

Microsoft Dynamics GP A few days ago I posted about testing email remittances and supplied a SQL script for use on the test system to ensure emails remained internal and didn’t get sent to creditors.

I’ve also written a script which will transfer the email address from the current Internet Information column (with the assumption that the email address is in INET1) across to the new EmailToAddress column and then blank out the INET1 column so data is not being held, and therefore maintained, in two fields. Continue reading →

Posted in Dynamics, GP, Microsoft | Tagged Check Remittance, Creditors, Email, GP, Microsoft Dynamics GP 2010, Payables Management, Payment Run, Purchasing, SQL Script | 1 Comment

Testing Remittances By Email – Keeping Emails Internal

Posted on 1st August 2011 by Ian Grieve

Microsoft Dynamics GP As you’ve probably been able to tell from my previous posts, I’ve been assisting a client in configuring Microsoft Dynamics GP 2010 so they can test remittances by email.

One of the things I have done for them is produce two SQL scripts to ensure emails do not leave their organisation and reach suppliers telling them of payments being made in the test system. Continue reading →

Posted in Dynamics, GP, Microsoft | Tagged Check Remittance, Creditors, Email, GP, Microsoft Dynamics GP 2010, Payables Management, Payment Run, Purchasing, SQL Script | 2 Comments

How To Remove The CEIP Reminder

Posted on 22nd July 2011 by Ian Grieve

Microsoft Dynamics When you install Microsoft Dynamics GP it automatically creates a recurring reminder for the Microsoft Customer Experience Improvement Program. In most cases this reminder is not wanted but removing it through the system for every user can be a bit time consuming;

For each user, log into Microsoft Dynamics GP and open the Reminders window (Microsoft Dynamics GP menu >> Reminders) and open the CEIP task. In the window click Name and select an option. Flag the task as Completed and move onto the next user and repeat.

However, help is at hand in with two short SQL scripts. Continue reading →