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 ●  ● 3 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 ●

    Removing Analytical Accounting from Microsoft Dynamics GP

    ● Ian Grieve ●  ● 5 Comments   ● 

    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.

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

    SQL Script To Log Into Each Company

    ● Ian Grieve ●  ● 0 Comments   ● 

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

    SQL Script To Remove Fixed Asset Management Tables

    ● Ian Grieve ●  ● 5 Comments   ● 

    Microsoft Dynamics GPEvery so often when doing an upgrade, or implementing a module, for a client, we encounter errors when doing the GP Utilities database upgrade. This time round we encountered the error when implementing Fixed Asset Management (FAM) in Microsoft Dynamics GP 2015 R2.

    We had previously upgraded the client from Dynamics GP 2010 R2 where they had not been using Fixed Asset Management and never had. However, despite the feature not being installed in Dynamics GP 2010 R2 and the client never knowing having used it, there were tables for Fixed Asset Management in their of the 20+ company databases; this looks like one of their previous partners had done something odd when creating these companies.

    The solution in this case was to remove all of the Fixed Asset Management tables from the database. When I have done this type of thing before I have manually written scripts to do this, but have tired of doing so (the previous time I had to do this it was the HR modules.

    So I wrote a simple script using a cursor which is run against the system database and which loops through all of the company databases and generates delete scripts for all of the tables for the designated module.

    It also generates scripts to delete the rows from the DU tables in the system database.

    There are three parameters at the top which need to be set:

    1. The database at the top which should be a system database
    2. @PRODID which is the numeric product id; for FAM this 309
    3. @TablePrefix which is the alpha prefix to the table names, which for FAM is FA

    The parameters are not authenticated or verified in any so oyu need to make sure the product id and table prefix are correct before proceeding.

    When the script is run, output it to Text which will give you a series of DROP TABLE commands you can then verify you are happy with the scripts before running them. I would strongly recommend checking the scripts and running them on a test system containing a copy of live first to ensure the result is what you require.

    These fields have been highlighted in the, below, script:

    /*
    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).
    */
    USE D16R1
    GO
    
    DECLARE @PRODID INT = 309
    DECLARE @TablePrefix VARCHAR(5) = 'FA'
    
    DECLARE @SQL_Statement VARCHAR(1000)
    
    CREATE TABLE #Scripts(
    	COMMAND VARCHAR(200)
    )
    
    DECLARE
    	cursor_InterID CURSOR 
    FOR 
    	SELECT
    		RTRIM(INTERID)
    	FROM
    		SY01500
    	UNION
    		SELECT DB_NAME()
    	
    	OPEN cursor_InterID
    
    	DECLARE @INTERID VARCHAR(100)
    
    	FETCH NEXT FROM
    		cursor_InterID
    	INTO
    		@INTERID
    	WHILE (@@FETCH_STATUS <> -1)
    		BEGIN
    			IF (@@FETCH_STATUS <> -2)
    				BEGIN
    					SET @SQL_Statement = 'INSERT INTO #Scripts (COMMAND) (SELECT ''DROP TABLE ' + @INTERID + '..'' + name FROM ' + RTRIM(@INTERID) + '.sys.tables WHERE name LIKE ''' + @TablePrefix + '%'')'
    					EXEC (@SQL_Statement)
    				END
    			FETCH NEXT FROM
    				cursor_InterID
    			INTO
    				@INTERID
    		END
    	CLOSE cursor_InterID
    DEALLOCATE cursor_InterID
    
    INSERT INTO #Scripts (COMMAND) (SELECT 'DELETE FROM DB_Upgrade WHERE PRODID = ' + CAST(@PRODID AS VARCHAR(5)))
    INSERT INTO #Scripts (COMMAND) (SELECT 'DELETE FROM DU000010 WHERE PRODID = ' + CAST(@PRODID AS VARCHAR(5)))
    INSERT INTO #Scripts (COMMAND) (SELECT 'DELETE FROM DU000020 WHERE PRODID = ' + CAST(@PRODID AS VARCHAR(5)))
    INSERT INTO #Scripts (COMMAND) (SELECT 'DELETE FROM DU000030 WHERE PRODID = ' + CAST(@PRODID AS VARCHAR(5)))
    GO
    
    SELECT COMMAND + CHAR(10) + 'GO' FROM #Scripts
    GO
    
    DROP TABLE #Scripts
    GO
    ● Categories: Dynamics, Fixed Asset Management, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

    Fixing Typo in Fabrikam’s Tax Schedule Description

    ● Ian Grieve ●  ● 3 Comments   ● 

    Microsoft Dynamics GPThe script in this post fixes a typo, in the Fabrikam sample database, in the Tax Schedule Description whereby the h in Purchases was missing. Not a big thing, but I was working on a report which was configured to return the Description as well as the Tax Schedule ID and it was bugging me.

    Hence a script to fix it, so I can easily fix it again in future.

    /*
    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).
    */UPDATE
    	['Tax Schedule Header Master']
    SET
    	TXSCHDSC = REPLACE(TXSCHDSC, 'Purcase', 'Purchase')
    FROM
    	TX00101 AS ['Tax Schedule Header Master']
    WHERE
    	TXSCHDSC LIKE '%Purcase%'
    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●