SQL Snippet: Generate Row Numbers

● Ian Grieve ●  ● 1 Comment   ● 

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.

In thi spost, I am going to show how use ROW_NUMBER to generate a unique row number. There are three examples of code.

This first example, the simplest of the three, shows how to generate a unique number for each row in the recordset:

/*
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
	PONUMBER
	,ORD
	,ITEMNMBR
	,ITEMDESC
	,ROW_NUMBER() OVER(ORDER BY PONUMBER, ORD ASC) AS CUSTOM_ROW_ID
FROM
	POP30110
GO

The second example, shows a row number can be assigned to the lines of each transaction (this is accomplished :

/*
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
	PONUMBER
	,ORD
	,ITEMNMBR
	,ITEMDESC
	,ROW_NUMBER() OVER(PARTITION BY PONUMBER ORDER BY PONUMBER, ORD ASC) AS CUSTOM_ROW_ID
FROM
	POP30110
GO

This final example, takes the first example and shows how we can select a range of row numbers (this is useful if you are selecting data to display on a page):

/*
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).
*/
WITH POTRX AS
	(SELECT
		PONUMBER
		,ORD
		,ITEMNMBR
		,ITEMDESC
		,ROW_NUMBER() OVER(ORDER BY PONUMBER ORDER BY PONUMBER, ORD ASC) AS CUSTOM_ROW_ID
	FROM
		POP30110)
SELECT
	*
FROM
	POTRX
WHERE
	CUSTOM_ROW_ID BETWEEN 40 AND 59
GO
● Categories: Microsoft, SQL Server ● Tags: , ,  ● Permalink ● Shortlink ●

SQL Snippet: Generate GUID

● Ian Grieve ●  ● 0 Comments   ● 

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 fourth example is going to be, by far, the shortest pioece of SQL I post. It shows how to return new GUID:

/*
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 NEWID()

I discovered this function when looking for a way to generate new GUIDs for a large Workflow implementation for a client where we are insertin the workflow steps via SQL rather than through the UI. This is very much not the recommended way of creating a workflow process, but the approval requirements resulted in a very large number of workflow steps and tackling it in this way, saved us a large amount of time.

● Categories: Microsoft, SQL Server ● Tags: , , , ,  ● Permalink ● Shortlink ●

SQL Snippet: Split String By Delimiter

● Ian Grieve ●  ● 0 Comments   ● 

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.

● Categories: Microsoft, SQL Server, Uncategorized ● Tags: , , , ,  ● Permalink ● Shortlink ●

SQL Snippet: Format Dates

● Ian Grieve ●  ● 0 Comments   ● 

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')
● Categories: Microsoft, SQL Server ● Tags: , ,  ● Permalink ● Shortlink ●

Deploy SQL View to All Databases

● Ian Grieve ●  ● 1 Comment   ● 

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

    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

    SQL View to Return Budgets By Month

    ● Ian Grieve ●  ● 1 Comment   ● 

    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

    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

    Stored Procedure To Get Next Purchase Receipt Number

    ● Ian Grieve ●  ● 1 Comment   ● 

    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.

    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

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

    ● Ian Grieve ●  ● 4 Comments   ● 

    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
    ● Categories: Dynamics, GP, Microsoft, Multicurrency ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

    Restore The Account Segment Warning

    ● Ian Grieve ●  ● 4 Comments   ● 

    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
    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

    SQL Script To Activate Horizontal Scroll Arrows

    ● Ian Grieve ●  ● 4 Comments   ● 

    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).

    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●