Tag Archives: SQL Script

Restoring A Database Without Encountering A Locking Issue

I periodically have problems when trying to restore a GP company database over the Test database as SQL reports that the database is currently in use.

One way of resolving is to restart the SQL Server but this is only possible on a stand alone test system which is not being used by other people but this approach is overkill.

The better solution is to change the database to single user, restore the database and change the database back to multiple user. This can be done manually through SQL Server Management Studio but is far easier done via a SQL script.

Continue reading

Posted in Microsoft, SQL Server, SQL Server 2008 | Tagged , , , | 1 Comment

How To Bulk Update Segment Descriptions From A CSV

On the Microsoft Dynamics GP Community site a question from Lisa Sorenson in October last year asking if it was possible to use the Table Import (Microsoft Dynamics GP menu >> Tools >> Integrate >> Table Import) feature in Microsoft Dynamics GP to update some Segment descriptions has risen to the top with Steve Cummings linking to a post where the suggestion is to use a CSV file and Word template to generate a mailmerge.

This solution will work, but can be accomplished in much less time and effort by using the SQL command BULK INSERT to load the CSV (formatted as Segment ID, Segment Number and Description) Continue reading

Posted in Dynamics, GP, Microsoft | Tagged , , , , , , , | 4 Comments

Missing Font Causes Crash In Management Reporter

I’ve recently been doing some work for a client where we’ve upgraded them from Microsoft Dynamics GP 10 to 2010 R2. Alongside this, we also migrated them from FRx to Management Reporter.

All looked fine after the upgrade until I opened one of the rows for editing and got the following warning message;

Management Reporter - Font 'Gill Sans' cannot be found.

Continue reading

Posted in Dynamics, FRx, Management Reporter, Microsoft | Tagged , , , , , , | 2 Comments

Copy Microsoft Dynamics GP Live Company To Test Automatically

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

Posted in Dynamics, GP, Microsoft, SQL Server, SQL Server 2008 | Tagged , , , , , | 3 Comments

Get Next Journal Entry Number

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 , , , , , | 3 Comments

Enabling Email Cheque Remittances For All Suppliers

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 , , , , , , , , | 3 Comments

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

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 , , , , , | 2 Comments

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

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 , , , , | 2 Comments

Going Live With Remittances By Email – Transferring Email Addresses

Microsoft Dynamics GPA 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 , , , , , , , , | 1 Comment

Testing Remittances By Email – Keeping Emails Internal

Microsoft Dynamics GPAs 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 , , , , , , , , | 2 Comments