SQL OUTPUT Clause to move Dynamics GP prices to history

14 February 2014 Tim-Wappat Microsoft Dynamics GP 
SELECT *
FROM   iv00108_history;

DECLARE @ROWCOUNTER AS INT;

SET @ROWCOUNTER = 903843;

WHILE @ROWCOUNTER != 0
    BEGIN
        DELETE TOP (1000)
               IV00108
        OUTPUT DELETED.[ITEMNMBR], DELETED.[CURNCYID], DELETED.[PRCLEVEL], DELETED.[UOFM], DELETED.[TOQTY], DELETED.[FROMQTY], DELETED.[UOMPRICE], DELETED.[QTYBSUOM], DELETED.[DEX_ROW_TS] INTO IV00108_History
        WHERE  PRCLEVEL IN ('LIST');
        SET @ROWCOUNTER = @@ROWCOUNT;
    END

--select * from iv00108_history
SELECT *
FROM   iv00108_history;


Create a history table with the same schema as the live table (might want to remove the indexes to keep size down).

Use the above script, supplying the price list name to move the prices from the live IV00108 table to the new custom table IV00108_history.

This moves the records in batches of 1000 to keep the transaction locking under control in the database. If too much information is locked (selected) we start going from row locks to page locks into table locks. Performance goes at and users start to complain at that point. The price table has three million rows in it so this is a wise precaution.

Visual Studio Auto Shelve Extension

06 February 2014 Tim-Wappat .NET

Quality and finance audits

In your annual audits, as a software manager the question always crops up of how is work in progress protected? All the project source code is normally safely locked away in team foundation server. The Team Foundation Server is in turn backed up and backup shipped off site. What about drive failure on the local developer machine? Normally hours or days of work could be lost since the last check was performed!
My answer to this is to use Auto Shelve extension and a weekly machine backup of my development machines.

Auto Shelve protects work loss from drive failure since last checkin

Auto shelve is a Visual Studio extension that periodically & automatically shelves your pending changes into source control as you work. It does this in background and so has minimal impact. Should my SSD drive fail catastrophically, work since my last check in will have been preserved in a shelf set in the team foundation server.

image 

After setting it up, Auto Shelve keeps overwriting the shelf set with the latest pending changes. After using it for some time now I can say it is effortless backup and I love it. I will love it even more when the day comes that I have a disaster on my hands.

Install

Install it through Tools>>Extensions and Updates

Search on the online node to the left, for autoshelve and install.

Go to the settings to set up your shelf set name and the frequency of shelving.

image