SQL Snippet: Split String By Delimiter

Microsoft SQL ServerIf you’ve been following this blog, you’ll know that I write a fair bit of SQL. I’m going to post some small snippets of SQL which I had to work out how to accomplish a small task as part of a larger script.

This third example, shows how to use the new in SQL Server 2016 string_split command:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT
	value
	,ITEMNMBR
	,ITEMDESC
	,ITMCLSCD
FROM
	IV00101
 CROSS APPLY
	string_split(RTRIM(ITEMNMBR), '-')
WHERE
	value = 'SHP'

The example is part of the code I used when working on a client project a while ago; the client had a large number of Inventory Items and I needed to select a subset of the Items from the Inventory Master (IV00101).

When the clioent created their items they did so using a hyphen delimiter. Using the string_split command, I was able to separate out the segments of the Item Number and select only one of them in the WHERE clause.

SQL Snippet: Format Dates

Microsoft SQL ServerIf you’ve been following this blog, you’ll know that I write a fair bit of SQL. I’m going to post some small snippets of SQL which I had to work out how to accomplish a small task as part of a larger script.

This second example, shows how to format a date in two of the most common formats I work with. Each example returns the date using the FORMAT command introduced in SQL Server 2012 and the more traditional method.

The first example, returns the date as day month year separated with /:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
DECLARE @DATE DATETIME = '2017-05-31 11:59:59.000'

SELECT
	CONVERT(VARCHAR(10), @DATE, 103)
	,FORMAT(@DATE, 'dd/MM/yyyy')

The second returns the date in ISO 8601 format:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
DECLARE @DATE DATETIME = '2017-05-31 11:59:59.000'

SELECT
	CONVERT(VARCHAR(10), @DATE, 126)
	,FORMAT(@DATE, 'yyyy-MM-dd')

Deploy SQL View to All Databases

Microsoft Dynamics GPI have a few clients who have quite a few company databases in Microsoft Dynamics GP. One of them has well over a hundred live companies. This can make deploying reports somewhat long winded when you need to deploy an SQL view to all of the databases.

Fortunately, Microsoft SQL Server has ways and means which you can use to make the process a lot easier. In this case, I am using a SQL cursor to select all of the databases from the Company Master (SY01500) and loop through them to deploy the view; the deployment is in three phases:

  • Delete any existing view with the same name (this allows for an easy redeployment).
  • Create the view.
  • Grant the SELECT permission to DYNGRP.
  • The script is posted below with a simplified PO report being created; the view name is set in the highlighted parameter near the top of the script.

    The large highlighted section is where you please the content of the view which is to be deployed.
    Continue reading “Deploy SQL View to All Databases”

    SQL View to Return Budgets By Month

    Microsoft Dynamics GPThe budget functionality in Microsoft Dynamics GP isn’t the strongest with reporting being particularly weak. The ability to report on budgets in Management Reporter does somewhat redeem this area of functionality.

    However, the absence of a SmartList Object for budgets is quite a big issue, as SmartList is a very nice flexible reporting tool which the majority of my clients know well. For those with SmartList Builder, it was easy enough to create a SmartList Object for them.

    With the introduction of SmartList Designer, we were able to roll out the SmartList budget report to all of the clients who wanted it.

    The script is below and returns the budget information with the beginning balance, 12 hard-coded periods and total horizontally across the page.

    Continue reading “SQL View to Return Budgets By Month”

    Stored Procedure To Get Next Purchase Receipt Number

    Microsoft Dynamics GPThis stored procedure can be executed to generate the next sequential purchase receipt number which can be used for both receivings transactions (Shipment and Shipment/Invoice) and invoices; the generated invoice was then added to the integration file which was then submitted to eConnect. I’ve written this stored procedure at least three times for different integrations, so thought it best to post it here so I don’t write it again.

    -- drop stored proc if it exists
    IF OBJECT_ID (N'usp_AZRCRV_GetNextPOPReceiptNumber', N'P') IS NOT NULL
        DROP PROCEDURE usp_AZRCRV_GetNextPOPReceiptNumber
    GO
    -- create stored proc
    CREATE PROCEDURE usp_AZRCRV_GetNextPOPReceiptNumber AS
    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
    */
    BEGIN
    	DECLARE @return_value INT
    	DECLARE @I_vInc_Dec TINYINT = 1
    	DECLARE @O_vPOPRCTNM AS VARCHAR(17)
    	DECLARE @O_iErrorState INT
    
    	exec @return_value = taGetPurchReceiptNextNumber  @I_vInc_Dec, @O_vPOPRCTNM = @O_vPOPRCTNM OUTPUT,  @O_iErrorState = @O_iErrorState OUTPUT
    	SELECT @O_vPOPRCTNM
    END
    GO
    
    -- grant execute permission on stored proc to DYNGRP
    GRANT EXECUTE ON usp_AZRCRV_GetNextPOPReceiptNumber TO DYNGRP
    GO
    
    -- execute stored proc
    EXEC usp_AZRCRV_GetNextPOPReceiptNumber
    GO

    The stored proc calls a Microsoft Dynamics GP stored procedure which actually does the work, so we are still getting the receipt number using standard functionality.

    SQL Script To Return Functional Currencies For All Companies Without a Cursor

    Microsoft Dynamics GPI posted a script a while ago which used a cursor to return the functional currencies for all companies connected to a system database. However, I have recently revisited this script and created a version which does not use a cursor.

    This script has been written to only return the companies which do not have a functional currency set; if you want to see all companies, regardless of the functional currency, remove the highlighted section.

    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
    */
    CREATE TABLE #FunctionalCurrencies(
    	INTERID VARCHAR(5)
    	,FUNLCURR VARCHAR(20)
    )
    GO
    
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = STUFF((
    					SELECT 
    						CHAR(13) 
    							+ 'SELECT 
    								''' + INTERID + '''
    								,FUNLCURR
    							FROM
    								' + INTERID + '.dbo.MC40000
    							WHERE
    								LEN(FUNLCURR) = 0'
    					FROM
    						DYNAMICS.dbo.SY01500
    					FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    
    INSERT INTO #FunctionalCurrencies
    	EXEC sys.sp_executesql @SQL
    GO
    
    SELECT * FROM #FunctionalCurrencies
    GO
    
    DROP TABLE #FunctionalCurrencies
    GO

    Restore The Account Segment Warning

    Microsoft Dynamics GPWhen new accounts are being created in Microsoft Dynamics GP a warning message is displayed if an entered segment does not exist in the Segment Master (GL40200) table:

    Restore The Account Segment Warning

    If the checkbox is marked then the warning message is not displayed for that user anymore and there is no way to restore the message through the front end of the system.

    The below script, allows the message to be restored for a named user (change the highlighted text to the required user):

    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
    */
    DECLARE @USERID VARCHAR(20)
    SET @USERID = 'userid'
    
    DELETE FROM
    	SY01401
    WHERE
    	coDefaultType = 13
    AND
    	USERID = @USERID
    GO

    SQL Script To Activate Horizontal Scroll Arrows

    Microsoft Dynamics GPThe Account field in Microsoft Dynamics GP is of a fixed width, but the maximum width of the account string themselves can be much longer than the field.

    The solution Microsoft have supplied is an option which activates horizontal scroll arrows in the Account field:

    Acount Maintenance

    This is activated, on a per user basis, via the User Preferences window (Microsoft Dynamics GP menu >> User Preferences) by marking the Horizontal Scroll Arrows:

    User Preferences

    However, if you have a lot of users, this can sometimes be a difficult message to disseminate.

    An alternative I came up with for a client a wile ago was to create a trigger on the Users Master (SY01400) table which updates the field after a new user is created:

    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
    */
    CREATE TRIGGER
    	utr_AZRCRV_UpdateSY01400ActivateHorizontalScrollArrows
    ON
    	SY01400
    AFTER INSERT AS
    	UPDATE
    		['Users Master']
    	SET
    		HSCRLARW = 1
    	FROM
    		SY01400 AS ['Users Master']
    	INNER JOIN
    		inserted AS INS
    			ON
    				INS.USERID = ['Users Master'].USERID
    GO

    If users have already been created, then the following script can be used to activate the horizontal scroll arrows for them:

    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
    */
    UPDATE
    	SY01400
    SET
    	HSCRLARW = 1
    WHERE
    	HSCRLARW = 0
    GO

    We actually decided to remove the trigger and apply the update script via a SQL Server Agent scheduled job which runs on a period basis (if I recall correctly, it was configured to run each evening at 2100 (avoiding backup jobs).

    Removing Analytical Accounting from Microsoft Dynamics GP

    Microsoft Dynamics GPI recently posted a script to remove the Fixed Asset Management tables from Microsoft Dynamics GP. I wrote that script for a client who wanted to start using Fixed Asset Management, but it turned out the module had been installed at some point in the past and then removed and the client wanted to reintroduce it.

    We’ve just had a similar instance come up with a client upgrading from Microsoft Dynamics GP 2013 SP2 to 2016 R2 where Analytical Accountijng has been installed at some stage and removed and was stopping the upgrade from going through.

    In this case though, there was (operative word was) a Knowledge Base article from Microsoft on how to remove Analytical Accounting which is no longer available (hopefully it will be back as I understand Microsoft are moving to a new KB system).

    The script was available when we had the issue, but a couple of weeks later, the question was asked by one of the other MVPs. By the time I saw the question, they’d been emailed the scripts by one of the others.

    However, when my colleague downloaded the scripts they had also saved a copy of the web page, which meant I could grab the download links for the scripts.

    1. System Database Script
    2. Company Database Script

    After running the Microsoft scripts, you’ve downloaded using the above links, run the below script on the system database to remove the Analytical Accounting entries from the upgrade tables:

    DECLARE @PRODID INT = 3180
    
    DELETE DB_Upgrade WHERE PRODID = @PRODID
    DELETE DU000020 WHERE PRODID = @PRODID

    As always before running scripts, make sure you have a good backup of your databases.

    SQL Script To Log Into Each Company

    Microsoft Dynamics GPI have a few of clients who have a large number of companies. This generally isn’t a problem as Microsoft Dynamics GP supports this very well.

    However, after doing an upgrade of a system which has the VAT Daybook module installed, you need to log into each and every database using the sa account.

    Doesn’t sound so bad on the surface, but when you have two hundred companies, this soon adds up to a substantial length of time.

    I did a little looking around a while ago for ways to automate this process and found a post by Aaron Berquist from 2011 where he had done exactly this.

    However, when we tried his script we got a few errors, so I made a few changes and improvements; my script automatically adds OK button clicks for Test, Historic and Fabrikam sample company databases.

    The Test and Historic databases are identified by having or at the end of their names; this is what Dynamics GP uses to display the message.

    I removed the temporary table from Aaron’s script as well; when my version of the script is run, output the results to text (make sure you have increased the query results length).

    You can copy and paste the output into a Notepad document which can be saved with a .mac extension.

    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    Based on idea and code from http://www.aaronberquist.com/2011/07/use-dynamic-sql-to-generate-a-dynamics-gp-login-macro/
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
    */
    SELECT
    	'# DEXVERSION=16.00.0033.000 2 2
    	  CommandExec dictionary ''default''  form ''Command_System'' command ''Switch Company'' 
    		NewActiveWin dictionary ''default''  form ''Switch Company'' window ''Switch Company'' 
    		  ClickHit field ''(L) Company Names'' item ' + CONVERT(VARCHAR(3), ROW_NUMBER() OVER (ORDER BY CMPANYID)) + '  # ' + LTRIM(RTRIM(CMPNYNAM)) + ' 
    		  MoveTo field ''OK Button'' 
    		  ClickHit field ''OK Button''
    		NewActiveWin dictionary ''default''  form sheLL window sheLL 
    		NewActiveWin dictionary ''default''  form sheLL window sheLL ' +
    		CASE WHEN RTRIM(CMPNYNAM) LIKE '%' THEN
    			'
    			  # This company is set up for testing only. Do not use this company when processing live data.
    			NewActiveWin dictionary ''default''  form ''SY_Error_Message'' window ''SY_Error_Message'' 
    			  ClickHit field ''OK Button'' 
    			NewActiveWin dictionary ''default''  form sheLL window sheLL '
    		WHEN RTRIM>(CMPNYNAM) LIKE '%' THEN
    			'
    			  # This company is used for storing historical data only. Do not use this company when processing current-year data.
    			NewActiveWin dictionary ''default''  form ''SY_Error_Message'' window ''SY_Error_Message'' 
    			  ClickHit field ''OK Button'' 
    			NewActiveWin dictionary ''default''  form sheLL window sheLL '
    		WHEN CMPANYID = -1 THEN
    			'
    			  # You have chosen to use the sample company, which provides data that you can use to practice procedures or learn more about the product. When you use this sample company, the date is automatically set to April 12, 2017.
    			NewActiveWin dictionary ''default''  form DiaLog window DiaLog 
    			  ClickHit field OK 
    			NewActiveWin dictionary ''default''  form sheLL window sheLL '
    		ELSE
    			''
    		END
    FROM
    	DYNAMICS..SY01500
    ORDER BY
    	CMPNYNAM