SQL Script to Get Assigned Building Block Groups for Companies in Management Reporter

Microsoft Dynamics GPI recently did a Microsoft Dynamics GP and Management reporter upgrade for a client which incuded migrating the databases to a new server. After performing the upgrade, users were unable, in some companies, to see the reports in Management Reporter. When we looked into it, some of the companies had reverted to the Default building block group.

To easily identify the companies which had reverted, I created the below script which coud be rn on both the original and upgraded Management reporter databases; it lists all the companies and the assigned building block group:

/*
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). */
SELECT ['Case Control'].Code AS 'Company ID' ,['Case Control'].Name AS 'Company Name' ,['Control Specification Set'].Name AS 'Building Block ID' ,['Control Specification Set'].Description AS 'Building Block Name' FROM Reporting.ControlCompany AS ['Case Control'] INNER JOIN Reporting.ControlSpecificationSet AS ['Control Specification Set'] ON ['Control Specification Set'].ID = ['Case Control'].SpecificationSetID ORDER BY ['Case Control'].Code

SQL script to insert emails into Microsoft Dynamics GP

Microsoft Dynamics GPThis is a script I wrote years ago and thought I'd posted it here, but it seems it had never been posted, although I have posted a script to insert test emails against all customers. This script can be used to insert or update email addresses on vendors, customers or items in Microsoft Dynamics GP.

The insert doesn't validate against the records against which the email addresses will be connected, so care does need to be taken to ensure data integrity and the script should be tested first before being run on a live company.

/*
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 #SY01200_IMPORT ( Master_Type VARCHAR(3) -- VEN = Vendor / CUS = Customer / ITM = Item ,Master_ID VARCHAR(100) ,ADRSCODE VARCHAR(100) ,EmailToAddress VARCHAR(1000) ,EmailCcAddress VARCHAR(1000) ,EmailBccAddress VARCHAR(1000) ) GO BULK INSERT #SY01200_IMPORT FROM 'c:\temp\email.txt' WITH (FIELDTERMINATOR = '\t' ,ROWTERMINATOR = '\n' ,FIRSTROW = 2 ) GO -- UPDATE if Email Details present UPDATE SY SET SY.EmailToAddress = SY_I.EmailToAddress ,SY.EmailCcAddress = SY_I.EmailCcAddress ,SY.EmailBccAddress = SY_I.EmailBccAddress FROM SY01200 SY INNER JOIN #SY01200_IMPORT AS SY_I ON SY_I.Master_ID = SY.Master_ID AND SY.Master_Type = SY_I.Master_Type AND SY.ADRSCODE = SY_I.ADRSCODE GO -- Insert if no Email Details INSERT INTO SY01200 ( Master_Type ,Master_ID ,ADRSCODE ,EmailToAddress ,EmailCcAddress ,EmailBccAddress ) ( SELECT Master_Type ,Master_ID ,ADRSCODE ,EmailToAddress ,EmailCcAddress ,EmailBccAddress FROM #SY01200_IMPORT WHERE (SELECT COUNT(Master_ID) FROM SY01200 WHERE Master_Type = #SY01200_IMPORT.Master_Type AND Master_ID = #SY01200_IMPORT.Master_ID AND ADRSCODE = #SY01200_IMPORT.ADRSCODE) = 0 ) GO DROP TABLE #SY01200_IMPORT GO

Change All Microsoft Dynamics GP Posting Reports Set to Print to Screen Output

Microsoft Dynamics GPI was helping a client implement Microsoft Dynamics GP last year and advised them to review the posting report setup and decide which ones they wanted to keep. To assist them with this, I ran the below script to change all reports which were set to print the report, to output the report to screen instead.

The users can then review the report and determine if they want to keep it or not.

/*
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). */
DECLARE @SQL_Statement VARCHAR(1000) DECLARE cursor_InterID Cursor FOR SELECT INTERID FROM DYNAMICS..SY01500 OPEN cursor_InterID DECLARE @INTERID VARCHAR(100) FETCH NEXT FROM cursor_InterID INTO @INTERID WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) SET @SQL_Statement = 'UPDATE ' + RTRIM(@INTERID) + '.dbo.SY02200 SET PRTOSCNT = 1, PRTOPRNT = 0 WHERE PRTOPRNT = 1' EXEC (@SQL_Statement) FETCH NEXT FROM cursor_InterID INTO @INTERID END CLOSE cursor_InterID DEALLOCATE cursor_InterID

This script updates the posting report output in every Dynamics GP company, so run it with care and after taking a good backup or all companies. I'd also recommend running it on a test system first, before running it on a live system to make sure you understand the impact it will have.

I've created other scripts to manipulate the posting reports previously:

Identify and Fix Corrupt SOP Transactions in Microsoft Dynamics GP

Microsoft Dynamics GPA client recently logged a support call whereby reports were showing incorrect information, including for transactions which had been deleted. I did some exploring mof data and found that the Sales Transaction Amounts Work (SOP10200) and Sales User-Defined Work History (SOP10106) tables contained rows for transactions which were not in the Sales Transaction Work (SOP10100) table.

From reviewing the data, deleted transactions which had an entry in Sales User-Defined Work History (SOP10106) would also have one in Sales Transaction Amounts Work (SOP10200) making the job of identifying the corrupt ones somewhat easier (Sales User-Defined Work History (SOP10106) contains both Work and History rows).

The first script identifies rows in Sales User-Defined Work History (SOP10106) which are orphaned:

/*
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). */
SELECT DISTINCT SOPL.SOPNUMBE ,SOPL.SOPTYPE FROM SOP10200 AS SOPL --Sales Transaction Amounts Work (SOP10200) INNER JOIN SOP10106 AS SOPU --Sales User-Defined Work History (SOP10106) ON SOPU.SOPNUMBE = SOPL.SOPNUMBE AND SOPU.SOPTYPE = SOPL.SOPTYPE LEFT JOIN SOP10100 AS SOPH --Sales Transaction Work (SOP10100) ON SOPH.SOPNUMBE = SOPL.SOPNUMBE AND SOPH.SOPTYPE = SOPL.SOPTYPE WHERE SOPH.SOPNUMBE IS NULL GO

Continue reading "Identify and Fix Corrupt SOP Transactions in Microsoft Dynamics GP"

Insert Segments from Text File/CSV into Microsoft Dynamics GP

Microsoft Dynamics GPIt seems that while I've posted about how to update segment descriptions from CSV in Microsoft Dynamics GP and how to copy them to a new company or even all companies, I've never actually posted the script I use to insert them.

I needed this script the other day and ended up using the update descriptions one as the basis for a new script to insert segments into Dynamics GP; this script will pick the text file (or CSV file if you change the second highlighted section to a comma), update any existing segments and insert new segments into the Segment Description Master (GL40200) table.

/*
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 #Segments ( SGMTNUMB VARCHAR(100) ,SGMNTID VARCHAR(100) ,DSCRIPTN VARCHAR(100) ) GO BULK INSERT #Segments FROM 'C:\Integrations\COA\Segments.txt' WITH ( FIELDTERMINATOR = '\t' ,ROWTERMINATOR = '\n' ,FIRSTROW = 2 ) GO UPDATE Segments SET Segments.SGMTNUMB = NewSegments.SGMTNUMB ,Segments.SGMNTID = NewSegments.SGMNTID ,Segments.DSCRIPTN = Left(NewSegments.DSCRIPTN, 31) FROM GL40200 AS Segments INNER JOIN #Segments AS NewSegments ON NewSegments.SGMTNUMB = Segments.SGMTNUMB AND NewSegments.SGMNTID = Segments.SGMNTID GO INSERT INTO GL40200 ( SGMTNUMB ,SGMNTID ,DSCRIPTN ) --VALUES ( SELECT SGMTNUMB ,SGMNTID ,LEFT(DSCRIPTN, 31) FROM #Segments AS NewSegments WHERE ( SELECT COUNT(*) FROM GL40200 AS Segments WHERE Segments.SGMTNUMB = NewSegments.SGMTNUMB AND Segments.SGMNTID = NewSegments.SGMNTID ) = 0 ) GO DROP TABLE #Segments GO

As always with a SQL script which makes changes, I'd make sure you have a good backup and test the script before running it in case you encounter problems.

Microsoft Dynamics GP Analytical Accounting Open Transactions SQL Query

Microsoft Dynamics GPI was looking into a problem reported by a client recently and needed to check the assignment of Analytical Accounting Transaction Dimension Codes to the AA GL transactions and so wrote the below SQL which I am posting to keep it easily accessible should I need it again.

/*
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). */
SELECT * FROM AAG30000 AS aaGLHdr INNER JOIN AAG30001 AS aaGLDist ON aaGLDist.aaGLHdrID = aaGLHdr.aaGLHdrID INNER JOIN AAG30002 AS GLAssign ON GLAssign.aaGLHdrID = aaGLDist.aaGLHdrID AND GLAssign.aaGLDistID = aaGLDist.aaGLDistID INNER JOIN AAG30003 AS aaGLCode ON aaGLCode.aaGLHdrID = GL Assign.aaGLHdrID AND aaGLCode.aaGLDistID = GLAssign.aaGLDistID AND aaGLCode.aaGLAssignID = GLAssign.aaGLAssignID INNER JOIN AAG00401 AS aaTrxDimCodeSetp ON aaTrxDimCodeSetp.aaTrxDimID = aaGLCode.aaTrxDimID AND aaTrxDimCodeSetp.aaTrxDimCodeID = aaGLCode.aaTrxCodeID

Find text in any SQL object

Microsoft SQL ServerI needed to find custom triggers or views which referenced a certain table and although I thought I had a script which would find text in a SQL object like a trigger, view or stored procedure, I couldn't find one when I searched my site the other day.

It only took me a few minutes to write one; the first highlighted text is the text to search for and the second a limitation on the name of the SQL objects to check.

/*
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). */
DECLARE @Search varchar(255) SET @Search='PM00200' SELECT DISTINCT ['SQL Objects'].name AS Object_Name ,['SQL Objects'].type_desc FROM sys.sql_modules AS ['SQL Modules'] INNER JOIN sys.objects AS ['SQL Objects'] ON ['SQL Objects'].object_id=['SQL Modules'].object_id WHERE ['SQL Objects'].name LIKE 'u%_AZRCRV_%' AND ['SQL Modules'].definition LIKE '%'+@Search+'%' ORDER BY ['SQL Objects'].name ,['SQL Objects'].type_desc

SQL script to create macro to delete items in Microsoft Dynamics GP

Microsoft Dynamics GPI've been doing some work with SmartConnect for a client recently where one of the integrations was creating new items. As the project progressed, some of the items which had been imported needed to be replaced with different item numbers. To ensure we didn;t cause problems, I didn't want to delete items through SQL directly, due to the number and variety of tables involved, so needed to come up with a way of generating the macro.

Macros are useful ways of repeating an action, such as deleting items, but any variation to the data and the macro will fall over. One variation was that some items had posted transactions against them so I needed to avoid these transactions.

I created the below script to create the macro for me, with SSMS set to output to text, and built in joins and checks on all of the relevant tables in Purchase Order Processing, Inventory and Sales Order Processing which may have contained data. The script is probably a little overkill on the checks it does, but I wanted to make sure it caught as much as possible.

Once the script has been run, you can copy the macro text into a file and play the macro to delete the items (if you have a lot of items, you can play the macro fast).

/*
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). */
SELECT '# DEXVERSION=14.00.0085.000 2 2 CheckActiveWin dictionary ''default'' form ''IV_Item_Maintenance'' window ''IV_Item_Maintenance'' TypeTo field ''Item Number'' , ''' + CAST(RTRIM(['Item Master'].ITEMNMBR) AS VARCHAR(50)) + ''' MoveTo field Inactive # ''FALSE'' CommandExec dictionary ''default'' form ''IV_Item_Maintenance'' command ''Delete Button_w_IV_Item_Maintenance_f_IV_Item_Maintenance'' # Are you sure you want to delete this record? NewActiveWin dictionary ''default'' form DiaLog window DiaLog ClickHit field OK NewActiveWin dictionary ''default'' form ''IV_Item_Maintenance'' window ''IV_Item_Maintenance'' ' FROM IV00101 AS ['Item Master'] LEFT JOIN ( SELECT ITEMNMBR FROM POP10110 UNION SELECT ITEMNMBR FROM POP30110 UNION SELECT ITEMNMBR FROM POP10210 UNION SELECT ITEMNMBR FROM POP30210 UNION SELECT ITEMNMBR FROM SOP10200 UNION SELECT ITEMNMBR FROM SOP30300 UNION SELECT ITEMNMBR FROM IV30300 ) AS ['Used Items'] ON ['Used Items'].ITEMNMBR = ['Item Master'].ITEMNMBR INNER JOIN IV00102 AS ['Item Quantities'] ON ['Item Quantities'].ITEMNMBR = ['Item Master'].ITEMNMBR AND ['Item Quantities'].LOCNCODE = '' LEFT JOIN IV10301 AS ['Stock Count Line'] ON ['Stock Count Line'].ITEMNMBR = ['Item Master'].ITEMNMBR WHERE ['Used Items'].ITEMNMBR IS NULL AND ['Item Quantities'].QTYONHND = 0 AND ['Item Quantities'].QTYRTRND = 0 AND ['Item Quantities'].QTYINUSE = 0 AND ['Item Quantities'].QTYINSVC = 0 AND ['Item Quantities'].QTYDMGED = 0 AND ['Item Quantities'].ATYALLOC = 0 AND ['Stock Count Line'].ITEMNMBR IS NULL

If when you run the script you only get part of the macro text, you can change the query results length.

Select all user views and create GRANT SELECT TO DYNGRP statement

Microsoft Dynamics GPI have a Microsoft Dynamics GP project deployment coming up soon for a client project for which a number of SQL views have been created; I can easily grab a create script for the views from Object Explorer Details in SQL Server Management Studio, but I also need to create the grant statement to give permissions to Dynamic GP users to use the SmartLists Builder objects which use these views.

INFORMATION_SCHEMA.VIEWS is available for querying in SQL Server and allows you to get a listing of the views. The following script selects all views with my custom user view prefix (highlighted).

/*
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). */
SELECT CONCAT('GRANT SELECT ON ', TABLE_NAME,' TO DYNGRP', CHAR(13), 'GO') FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME LIKE 'uv_AZRCRV_%'

Run the script with the output set to text as for each view the script creates the GRANT and subsequent GO commands.

SQL Stored Procedure to remove Jet Reports’ Report Run History

Jet ReportsIf you're using Jet Hub with Jet Reports, then when you run a report, the report run is stored in the Jet Services database;through time, this report run history can grow quite large if you either have large reports or are generating lots of reports; combine these together and the history can grow to potentially massive sizes.

Jet Hub does not, unfortunately, include an automated clear-down routine for the report run history, but, fortunately, the history is only stored in a single table.

The below stored procedure can be created against the Jet Services database and scheduled to run with SQL Server Agent; the highlighted parameter at the top can be changed to alter the number of months for which history should be kept:

IF OBJECT_ID (N'usp_AZRCRV_DeleteJetReportsReportRuns', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_DeleteJetReportsReportRuns
GO

CREATE PROCEDURE dbo.usp_AZRCRV_DeleteJetReportsReportRuns
	@iAge INTEGER = 12
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 4.0 International (CC BY-NC-SA 4.0 Int). */
DELETE FROM ReportRuns WHERE Runtime < DATEADD(month, -@iAge, GETDATE()) GO

As always, test the script before using against a live system and ensure you have a good backup before