Dynamics GP – Changing the Voucher Number of a Posted Payables Transaction


Recently I had a situation where I had to change the voucher number of a posted payables document. There is no standard way to do this via the application however it was causing issues with a third party integration so we had to investigate how this could be done. To do this task I teamed up with a developer and we came up with the following solution.

Please note there may be additional tables/columns you would have to update. Hopefully this article will not only outline the solution we used, but also how you can track down any additional tables you may need to update that we didn’t.

Find the tables with a VCHRNMBR column

The first thing we did was to find all the Dynamics GP tables that contained the VCHNMBR column. To do this we used the script below:

SELECT col.NAME AS 'Column',
       tab.NAME AS 'Table'
FROM   sys.columns col
       INNER JOIN sys.tables tab
         ON col.object_id = tab.object_id

This gave us a full list of tables that contained the VCHRNMBR column.

Next we decided to find which of those columns contained the actual voucher number we needed to change.

Find the tables that contained our voucher number

The previous query gave us quite an extensive list of tables that needed to be updated, and we weren’t sure we would need to update all of those tables and columns. Therefore we ran a query to find all the tables that contained a column with our actual voucher number in. For this I found this excellent query which I’ve used many times before when troubleshooting data issues. (This query has been taken from this forum post over on stack overflow)

DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))


DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')


    SET @ColumnName = ''
    SET @TableName = 
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    OBJECTPROPERTY(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

        SET @ColumnName =
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName

        IF @ColumnName IS NOT NULL

            INSERT INTO @Results
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

SELECT ColumnName, ColumnValue FROM @Results

To run this on your system just changed the DATABASE_NAME to your Dynamics GP company database and also enter the string you wish to search in place of the SEARCH_TEXT.

We now had a list of tables and columns that contained the voucher number we needed to change.

The final update script

Pulling the information we had gleaned from the queries above, and using knowledge of their system and of the Dynamics GP table structure (for example the column name we needed to change in the apply table is APTVCHNM rather than VCHRNMBR) we used the script below to update the voucher number:

/****** Object:  StoredProcedure [dbo].[ChangePMVoucherNumber]   ******/
CREATE PROCEDURE [dbo].[ChangePMVoucherNumber]
	@OldVoucher	   AS VARCHAR(17),
	@NewVoucher        AS VARCHAR(17)


	-- Example of execution:  exec ChangePMVoucherNumber '00000000000000398', '10000000000000398'


	-- Environment

        -- Declare variables

	-- Checks
	IF @OldVoucher IS NULL BEGIN 
		PRINT 'Old voucher must be specified'
	IF @NewVoucher IS NULL BEGIN 
		PRINT 'New voucher must be specified'
	IF @OldVoucher = @NewVoucher BEGIN 
		PRINT 'Old must differ from new voucher'

	----Get document type from keys of original voucher
	--FROM	TWO.dbo.PM00400
	--WHERE	CNTRLNUM = @OldVoucher

	--	PRINT 'Voucher is not an invoice / Not Found'

	-- Process


			Print 'Starting change of ' + @OldVoucher

			-- Query (PM Key Master File)
			UPDATE TWO.dbo.PM00400
			SET		CNTRLNUM = @NewVoucher
			WHERE	CNTRLNUM = @OldVoucher

			-- Query (PM Transaction OPEN File)
			UPDATE TWO.dbo.PM20000
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (Multicurrency Payables Transactions)
			UPDATE TWO.dbo.MC020103
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (Multicurrency PM Revaluation Activity)
			UPDATE TWO.dbo.MC020105
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (Purchasing Receipt History)
			UPDATE TWO.dbo.POP30300
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (PM Tax Invoices)
			UPDATE TWO.dbo.PM30800
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (PM Tax History File)
			UPDATE TWO.dbo.PM30700
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (PM Distribution WORK OPEN)
			UPDATE TWO.dbo.PM10100
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (PM Tax Work File)
			UPDATE TWO.dbo.PM10500
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (PM Paid Transaction History File)
			UPDATE TWO.dbo.PM30200
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (PM Distribution History File)
			UPDATE TWO.dbo.PM30600
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (PM History Removal Temporary File)
			UPDATE TWO.dbo.PM50100
			SET		VCHRNMBR = @NewVoucher
			WHERE	VCHRNMBR = @OldVoucher

			-- Query (PM Apply To History File)
			UPDATE  TWO.dbo.PM30300
			SET		APTVCHNM = @NewVoucher
			WHERE	APTVCHNM = @OldVoucher

			-- Query (PM Apply To WORK OPEN File)
			UPDATE TWO.dbo.PM10200
			SET		APTVCHNM = @NewVoucher
			WHERE	APTVCHNM = @OldVoucher

			-- Query (PM Payment Apply To Work File)
			UPDATE TWO.dbo.PM10201
			SET		APTVCHNM = @NewVoucher
			WHERE	APTVCHNM = @OldVoucher

			-- Query (PM Reprint Posting Journal Apply To File)
			UPDATE TWO.dbo.PM80500
			SET		APTVCHNM = @NewVoucher
			WHERE	APTVCHNM = @OldVoucher

			Print 'Ending change of ' + @OldVoucher


		PRINT 'Issue changing voucher number ' + @OldVoucher + ' ' + ERROR_MESSAGE()


To change a voucher number using the script you first create the procedure by executing the script and then execute the stored procedure as per below. (this would change voucher number 00000000000000398 to 10000000000000398)

exec ChangePMVoucherNumber '00000000000000398', '10000000000000398'


This script worked well but it was vitally important we did the research on other affected tables using the two scripts at the top of the article.

I also recommend doing this in a practice company prior to execution and also running reports such as a the Historical Aged Trial Balance before and after the voucher number has been changed.

Finally always have a goof full backup of your system that can be quickly restored if need be.

Thanks for reading!

Dynamics GP – 10 tips when running a General Ledger Year End on a large SQL Database


A few weeks ago I assisted with a General Ledger year end for a GP user with a very large number of journals and consequently a very large SQL database.

Usually a year end is a fairly straight forward routine, however as this user has such a large database, along with additional SQL bits and pieces for disaster recovery and bespoke reporting, it required a little more thought to ensure things went as smoothly as possible.

In this post I thought I’d share the steps I took to hopefully assist anyone else facing a similar situation in the future.

Here goes…..

1) Do the basics first.

By basics I mean following Microsoft’s general Year End prerequisites which can be found here.

I find the most important steps are ensuring you have a good backup and also ensuring the general ledger codes have the correct posting type as this can be a pain to correct post year end.

2) Ensure you have enough disk space for the SQL Data and SQL Log files.

This usually isn’t a consideration when running the Year End however given the size of the data its something that needs planning when dealing with GL tables in the 100’s of GBs.

Microsoft’s official guidance on disk space is included in the Year End documentation that I’ve linked to above however I’ve copied the section on disk space below:

During the year-end closing routine, all the records that will be moved are put in a temporary table before they are moved to the GL30000 table. You must have free disk space that is equal to the size of the GL20000 table to perform the routine.

To find the current size of the GL20000 you can use the sp_spaceused system stored procedure i.e.

However I’d also add that when dealing with large data volumes you also need to plan for extraordinary SQL transaction log growth as well.

For example the SQL transaction log grew to 240GB when I did this particular year end. This is because various statements including the INSERT statements into GL30000 are completed in one SQL transaction. Therefore no matter what the SQL recovery model is of your database, the log file is going to grow while the process completes. This is standard behaviour so SQL can roll the whole transaction back should any issue occur.

3) Remove any Database Mirroring or Availability Groups.

In my scenario the user has database mirroring for disaster recovery. I could have left this on however this would result in a lot of transaction log being sent over the wire while the Year End ran potentially doubling the length time it takes. I always see the amount of time something takes, as the size of the window of opportunity for something to go wrong. The quicker I could get the Year End process to go, the less chance I had of something going wrong. On the down side it does mean I had additional configuration to complete post year end, but I was fine with this.

4) Remove any custom indexes

If you have any additional custom indexes on GL20000 and GL30000 its best to remove them. Again its not a problem if you don’t remove them, however it can slow things down and also increase the amount of transaction log that SQL will produce. This is because if you are DELETEing and INSERTing data into tables the indexes also have to be updated. Therefore the less indexes you have the better. This requires additional space and also additional log to record the operations.

**Don’t remove any of the standard indexes though.

5) Stop the SQL Agent.

Maintenance tasks like reindex routines sometimes use the SQL agent to run. As I suspected the Year End will run for a very long time, I stopped the SQL agent to prevent this from happening. Its always best to check that critical operations won’t be affected by this though. If so just disable any SQL jobs that run maintenance on the Dynamics databases.

6) Temporarily revoke permissions for any Reporting Logins.

Ideally I’d like to set the database to single user mode while the Year End ran however as I wanted to monitor the process I didn’t want to restrict this too much. I therefore disable logins that I knew were used for reporting in case anyone sets off a large report in the middle of the year end.

7) Ensure SQL log growth on the database is set sensibly.

As I mentioned above the SQL transaction log is going to grow during this operation regardless of the recovery model. Therefore go to the properties of the SQL database and ensure the growth is set correctly, and its set in MB’s rather than a percent. I set it to grow 10GB increments for this particular year end process.

8) Use SQL monitoring scripts

I like to nosy under the hood when running most things but I especially want a sneak peak on the General Ledger Year End. My tool of choice for this is Adam Machanic’s awesome free monitoring tool sp_whoisative. This is a free script you can use to monitor activity in SQL. Using this I can pretty much see at what point the Year End was up to. i.e. INSERTing the data to GL30000 etc.

**If you didn’t know about this free script, and this is all you take away from this blog I’d be happy :). Its a really cool free tool you can use when troubleshooting all sorts of things in SQL i.e. blocking etc.

I also want to monitor my database and log growth as the process is running. If I monitor this I can predict when the log will run out of space and need to grow. I then check the disk space to ensure the growth will happen smoothly. To do this I use this script

name AS FileName, 
size/128.0 AS CurrentSizeMB, 
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files; 

9) Be patient 🙂

One of the most important things is to be patient as this routine can last hours on a large data set. Its also important to never cancel the operation by manually closing GP down even if it says “Not Responding”. If it doubt you can run the sp_whoisactive stored procedure and check what’s happening on the server.

10) Once complete

After a huge sigh of relief I performed an additional backup and then started recreating the indexes, setting up the mirroring and restarting the SQL agent. I also shrank the SQL transaction log back down to a reasonable size.


I hope you find the information in this post useful. Although you may not need to follow all the points I outlined, I think the most important are disk space for the SQL transaction log, monitoring and lots of patience 🙂

Thanks for reading!

Dynamics GP – Using the “Batch Enquiry” window to assist troubleshooting the “Receiving” or “Busy” batch statuses


Situations can arise where batches show a status of “Receiving” or “Busy” in the various series post windows. This is generally nothing to worry about, it just means a user is active with that batch, however if the batch continues to say “receiving” or “busy” when no one is posting, further investigation may be needed. When this happens users and admins will often dive straight into SQL to investigate further, however this post shows how you can use the little known “Batch Enquiry” window to get a greater insight into what is happening with batches in Dynamics GP, without the need for SQL. You can then take some remedial action to hopefully prevent any long winded SQL fixes.

The Busy and Receiving Batch Statuses

When you are working in a batch in Dynamics GP the batch status changes to record you are active in that batch. For example I’m working in the “GAV JAN INVS” batch so the series post window shows this batch as “Busy”

In the scenario below I’m posting some Sales Invoices, so the General Ledger Batch RMSLS000009 is showing a status of “Receiving”. (as this is receiving transactions from the Sales Invoice batch I’m posting)

As I mentioned above this is all normal behaviour, however instances can arise where no one is in the batch yet the batch status remains as “Busy” or “Receiving”.

Batch Enquiry

When this occurs users and admins can be tempted to delve straight into SQL however you can also use the “Batch Enquiry” window to see which users are active with those batches. The “Batch Enquiry” window is a little known window that gives you a peak into the “Batch Activity” table (the SY00800 table in the DYNAMICS database – a row is inserted into this table when a user is active in a batch).

You can access the window via the option “Enquiry > System > Batch”

Once in this window it gives you an overview of what users have which batches open and a status of what is happening in those batches.

Armed with this information we can check directly with those users to see if they are indeed working in those batches. If they aren’t then the first course of action is to ask them to log out of GP and log back in. Doing this can trigger GP to automatically recover the batch for you, negating the need for any intervention at SQL level.

For example the user “sa” wasn’t active with the batches “SL JAN INVS” or “RMSLS0000009” so after logging the “sa” user back into GP we were prompted with the message below:

This indicates that the system has automatically recovered the batch for you and now you can just go to “Batch Recovery” to continue the posting of the batch. There was no need to go into SQL and start manually removing activity records. In fact doing that in the first instance could have made the fix much more difficult. (i.e. if the batch activity record was removed manually in SQL its likely you wouldn’t have been prompted with the message to recover the batch. Therefore fixing things would have been a manual process)


Hopefully using the batch enquiry can help identify which users need to log out and back into GP which can sometimes force GP to fix any issues with the batch for you without the need for SQL intervention.

It can also help you monitor the system more effectively, giving an insight into which batches users are working on.

Thanks for reading!