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

Stored procedure to get next Dynamics GP Payables Voucher Number

Microsoft Dynamics GPI’ve previously posted SQL stored procedures to get the next GL Journal Number and the next PO Receipt number and today it is the turn of a stored procedure to get the next PM Voucher Number.

The stored procedure will call the eConnect stored procedure which gets the next number and increments the stored value. This code was written so I could easily call it from VBA in Integration Manager for an integration which needed to insert some data into a custom table. This allowed me to get the voucher number up front and use it in the VBA.

-- drop stored proc if it exists
IF OBJECT_ID(N'usp_AZRCRV_GetNextPMVoucherNumber', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_GetNextPMVoucherNumber
GO -- create stored proc CREATE PROCEDURE usp_AZRCRV_GetNextPMVoucherNumber AS /*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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 AS INT DECLARE @O_vCNTRLNUM AS VARCHAR(17) DECLARE @I_sCNTRLTYP AS TINYINT = 0
DECLARE @O_iErrorState AS INT

EXEC @return_value = taGetPMNextVoucherNumber
@I_sCNTRLTYP = @I_sCNTRLTYP
,@O_iErrorState = @O_iErrorState OUTPUT ,@O_vCNTRLNUM = @O_vCNTRLNUM OUTPUT SELECT @O_vCNTRLNUM AS VCHRNMBR
END
GO
-- grant execute permission on stored proc to DYNGRP GRANT EXECUTE ON usp_AZRCRV_GetNextPMVoucherNumber TO DYNGRP
GO

The stored procedure can be executed using this command:

-- execute stored proc
EXEC usp_AZRCRV_GetNextPMVoucherNumber
GO

SQL Stored Procedure to Generate Sequential Number

Microsoft SQL ServerWhile much of the work I do is directly with Microsoft Dynamics GP, I also do work for clients which isn’t directly related. I’ve created code to generate numbers a few times in the past and figured I might as well post the base code I use for this to make it easier to find in future.

I’ve created it in such a way that several unique numbers can be stored and incremented.

The first part of the code creates a table to hold the number type and next number:

-- drop table if it exists
IF OBJECT_ID (N'ut_AZRCRV_NextNumber', N'U') IS NOT NULL
	DROP TABLE ut_AZRCRV_NextNumber
GO -- create table CREATE TABLE ut_AZRCRV_NextNumber( NMBRTYPE VARCHAR(50) ,NEXTNMBR INT ) GO
Next, I create a stored procedure which will increment and return the next number:
-- drop stored proc if it exists
IF OBJECT_ID (N'usp_AZRCRV_GetNextNumber', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_GetNextNumber
GO -- create stored proc CREATE PROCEDURE [dbo].[usp_AZRCRV_GetNextNumber] ( @NMBRTYPE VARCHAR(50) ,@NEXTNMBR INT OUTPUT ) 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). */
SET NOCOUNT ON

BEGIN TRAN -- if this is the first value generated for this table, start with one IF NOT EXISTS (SELECT * FROM ut_AZRCRV_NextNumber WHERE NMBRTYPE = @NMBRTYPE) INSERT INTO ut_AZRCRV_NextNumber
(NMBRTYPE,NEXTNMBR) VALUES (@NMBRTYPE,1)

-- select next number from table into variable SELECT @NEXTNMBR = NEXTNMBR FROM ut_AZRCRV_NextNumber WHERE NMBRTYPE = @NMBRTYPE

-- increment number by 1 UPDATE ut_AZRCRV_NextNumber
SET NEXTNMBR = NEXTNMBR + 1
WHERE NMBRTYPE = @NMBRTYPE
COMMIT TRAN

-- return variable containing next number RETURN @NEXTNMBR
GO

Then, I grant execute permissions to the relevant database role:
-- grant execute permission on stored proc to ur_AZRCRV_InvoiceUser
GRANT EXECUTE ON usp_AZRCRV_GetNextNumber TO ur_AZRCRV_InvoiceUser
GO
And finally, I have the SQL code which will generate the next number:
-- code to get next number
DECLARE @NMBRTYPE VARCHAR(50) = 'Sales Invoice'
DECLARE @NEXTNMBR INT
EXEC [usp_AZRCRV_GetNextNumber] @NMBRTYPE, @NEXTNMBR OUTPUT
SELECT @NEXTNMBR
GO

SQL Stored Procedure to Remove Prior Day Logins

Microsoft Dynamics GPIn the last post, I posted a SQL view which returned a list of users who had logged in before the current date. This post contains a SQL stored procedure which will delete any prior day login; this could be scheduled to run using SQL Server Agent:

CREATE PROCEDURE usp_AZRCRV_RemovePriorDayLogins AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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).
*/
	DELETE FROM
		ACTIVITY
	WHERE
		['User Activity'].LOGINDAT <= DATEADD(DAY, -1, GETDATE())
GO
GRANT EXECUTE ONusp_AZRCRV_RemovePriorDayLogins TO DYNGRP
GO

Before using this script on a live system, I’d recommend testing it on a standalone test system first.

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 (https://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.