Dynamic Code Blocks | Microsoft Dynamics GP & .NET technical Blog by Tim Wappat

Dynamics GP basic pricing export to Excel with crosstab SQL

02 February 2018 Tim-Wappat Microsoft Dynamics GP

The following SQL table function allows exporting of GP prices into Excel.

Assumes max of 6 price breaks, but can be expanded.

Usage:

SELECT * FROM [Extract_PricesCrosstabTable_Fast] ('GBP','TRADE','%') ORDER BY ITEMNMBR

Function:

-- =============================================
-- Author:        Tim Wappat 
-- Create date: 2010-08-02
-- Description:    Function to crosstab price breaks for price level

-- Faster set based non-cursor version of crosstab for pricing
-- =============================================

CREATE FUNCTION [Extract_PricesCrosstabTable_Fast] (
     @CURNCYID VARCHAR(15)
    ,@PRCLEVEL VARCHAR(11)
    ,@PATTERN NVARCHAR(31) = '%'
    )

RETURNS @ReturnTable TABLE (
    [ITEMNMBR] [varchar](31) PRIMARY KEY NOT NULL
    ,[BREAK1] [varchar](255) NOT NULL
    ,[PRICE1] [numeric](19, 5) NULL
    ,[BREAK2] [varchar](255) NOT NULL
    ,[PRICE2] [numeric](19, 5) NULL
    ,[BREAK3] [varchar](255) NOT NULL
    ,[PRICE3] [numeric](19, 5) NULL
    ,[BREAK4] [varchar](255) NOT NULL
    ,[PRICE4] [numeric](19, 5) NULL
    ,[BREAK5] [varchar](255) NOT NULL
    ,[PRICE5] [numeric](19, 5) NULL
    ,[BREAK6] [varchar](255) NOT NULL
    ,[PRICE6] [numeric](19, 5) NULL
    )
AS
BEGIN
    
    WITH PriceTableCte (
        itemnmbr
        ,curncyid
        ,prclevel
        ,uomprice
        ,fromqty
        ,toqty
        ,[PriceBreak]
        )
    AS (
        SELECT ITEMNMBR
            ,CURNCYID
            ,PRCLEVEL
            ,UOMPRICE
            ,FROMQTY
            ,TOQTY
            ,ROW_NUMBER() OVER (
                PARTITION BY ITEMNMBR
                ,PRCLEVEL
                ,CURNCYID ORDER BY TOQTY ASC
                ) AS 'PriceBreak'
        FROM IV00108
        WHERE ITEMNMBR LIKE @PATTERN
            AND PRCLEVEL = @PRCLEVEL
            AND CURNCYID = @CURNCYID
        
        )
    INSERT @ReturnTable
    SELECT ITEMNMBR
        ,isnull(max(CASE 
                    WHEN PriceTableCte.[PriceBreak] = 1    THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
                    END), '') AS PriceBreak1
        ,max(CASE 
                WHEN PriceTableCte.[PriceBreak] = 1    THEN UOMPRICE
                END) AS Price1
        ,isnull(max(CASE 
                    WHEN PriceTableCte.[PriceBreak] = 2    THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
                    END), '') AS PriceBreak2
        ,max(CASE 
                WHEN PriceTableCte.[PriceBreak] = 2
                    THEN UOMPRICE
                END) AS Price2
        ,isnull(max(CASE 
                    WHEN PriceTableCte.[PriceBreak] = 3    THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
                    END), '') AS PriceBreak3
        ,max(CASE 
                WHEN PriceTableCte.[PriceBreak] = 3
                    THEN UOMPRICE
                END) AS Price3
        ,isnull(max(CASE 
                    WHEN PriceTableCte.[PriceBreak] = 4    THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
                    END), '') AS PriceBreak4
        ,max(CASE 
                WHEN PriceTableCte.[PriceBreak] = 4
                    THEN UOMPRICE
                END) AS Price4
        ,isnull(max(CASE 
                    WHEN PriceTableCte.[PriceBreak] = 5    THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
                    END), '') AS PriceBreak5
        ,max(CASE 
                WHEN PriceTableCte.[PriceBreak] = 5
                    THEN UOMPRICE
                END) AS Price5
        ,isnull(max(CASE 
                    WHEN PriceTableCte.[PriceBreak] = 6    THEN LTRIM(CONCAT(STR(FROMQTY, 6, 0), '+'))
                    END), '') AS PriceBreak6
        ,max(CASE 
                WHEN PriceTableCte.[PriceBreak] = 6
                    THEN UOMPRICE
                END) AS Price6
    FROM PriceTableCte
    
    GROUP BY ITEMNMBR
        ,CURNCYID
        ,PRCLEVEL
    ORDER BY ITEMNMBR;

    RETURN
END;

A coutrecords operation on table ‘[Not Found]’ cannot find the table–Dynamics GP

30 January 2018 Tim-Wappat Microsoft Dynamics GP

Symptom: User experiencing this error dialog when using navigation lists, but can happen in other areas of Dynamics GP

Cause and solutions:

This error is caused by the client machine that is running Dynamics GP loosing network connection at some point during the Dynamics GP session. Dynamics GP holds some long lived connections, especially where temp tables are in use (for example in navigation lists). Indeed it is necessary for GP to keep the connection alive or the temp table will be reclaimed by SQL server as soon as the last connection to it drops. It may also be that you see an associated error similar to the following, with a random looking numeric table name for the global temp table. This is the result of the connection loss.

SQL Server, Invalid object name ‘##293343’

There can be many root causes for a SQL connection to be lost in action. Network infrastructure failures, due to cabling faults in RJ45 connectors, network cards, when the kicks the cables under desk intermittent errors, or someone re-patching live network cabling, network cards starting to fail due to wear and tear etc. These hardware related issues used to cause us issues many years ago, but with a better infrastructure, in recent years it has been software issues in the shape of “sleep settings”. Perhaps there is a poor wifi bridge or similar weak network infrastructure that needs attention, as every time someone starts the microwave in the canteen the wifi drops… – you get the idea, so much to investigate.

The server being rebooted, or SQL server tier being torn down while the user is using GP can also result in the same error.

The advanced settings of the network card can present various settings, including allowing the operating system to put the network card to sleep, supposed power efficiency? The machine OS sleep settings may be putting the machine into sleep or hibernate after a certain period of time, there may be BIOS settings too.

Users who take long lunches, or leave machines on overnight with GP logged in will more than likely get caught out by sleep settings. When the machines are left inactive they go to sleep loosing the SQL connection and resulting in the above error. I’ve seen more of this since we moved the estate to Windows 10, I don’t think I can draw any real conclusions from that though. I have also experienced users putting their machines to sleep when going for lunch or overnight, manually from the start menu. For this reason Active Directory Group Policy has been created and rolled out to lock down such settings for the GP users.

Although not personally experienced, I have read that this may also be that the ODBC connection settings not being set up correctly. For Dynamics GP, uncheck “Use ANSI quoted identifiers” and “Use ANSI nulls, paddings and warnings”, Uncheck “Perform translation for character data” in the ODBC settings.

See: How To Create An ODBC For Microsoft Dynamics GP 2010 which is applicable for all versions of GP.

Hopefully this has been useful to you – let me know if it has with a comment – motivates me to write more!

TF400409 you do not have licensing right to access this feature

26 January 2018 Tim-Wappat .NET, TFS

Setting up a new user for team foundation server, although user has been added to all the licencing groups and project groups, they cannot expand the root node of the team collection. They can also not access the portal security pages as it gives the above error.

Turned out to be that they needed the access level for the user setting.

This is outlined within the document here: https://docs.microsoft.com/en-gb/vsts/security/change-access-levels

You may follow this route to the access level admin (in the version we use)

Select Security from the Team Explorer Settings menu from Visual Studio

Navigate to the root of the control panel

Navigate to the Access Levels tab

Put the user under the appropriate access level of Basic or Advanced. This should then allow them to access the web portal without error and expand the source control collection node to see the individual projects (assuming you’ve set them up correctly with those too).

SQL formatting and Power Query

09 January 2018 Tim-Wappat SQL Server

Today I got caught out by using SSMSBoost to SQL format a query being used as the source for a Microsoft Power Query.

On updating the stored procedure the query stopped working in Power Query, with the error that the column “Temp” was missing.

Looking at the query output in SSMS, I could see that the column had changed to TEMP in upper case from Temp in the original query, easily fixed. Then I realised what might have happened to cause it and proved it. Formatting the SQL had caused the temp column to be upper cased, my suspicion is that Temp was wrongly identified as a keyword by the formatting template and thus with the setting in SSMSBoost being to uppercase keywords, it got uppercased. Power Query is case sensitive so got upset.

Just a warning for those of you that use SQL formatting tooling, one to keep an eye out for, luckily having a column named Temp is not too common.