How to Schedule a Skype for Business Meeting

Microsoft Dynamics GPI’m posting this because it took me far longer to work out how to schedule a Skype for Business meeting than it should have done.

The reason it took so long to find out how to do this, was because I was looking in Skype for Business itself. I should have been looking in Outlook:

Continue reading “How to Schedule a Skype for Business Meeting”

Doc Attach Button in Notes Window Missing

Microsoft Dynamics GPAfter a clients system was upgraded a while ago from Microsoft Dynamics GP 2010 SP3 to Microsoft Dynamics GP 2018, we enabled the Document Attachment feature for the client to use. Unfortunately when showing the client how to use the feaute, we found that the Doc Attach button was missing (the red square is where the button should be):

Notes

Continue reading “Doc Attach Button in Notes Window Missing”

Debugging Integration Manager Crashes

Microsoft Dynamics GPWe’ve had two separate clients recently log calls where Integration Manager was crashing while performing an integration.

The first client was doing an Inventory Items integration on Microsoft Dynamics GP 2015 R2 and the other was doing a Sales Transaction integration on Microsoft Dynamics GP 2016 R2.

Both of the integrations had been running fine for quite a long period of time before starting to crash; no changes had been made to either system and I’ve not been able to determine why Integration Manager (IM) has started crashing.

However, by the use of two ini file switches, we were able to determine what error was being encountered by IM resulted in a crash.

The two switches are already present in the ini file (C:\Program Files (x86)\Microsoft Dynamics\Integration Manager nn/Microsoft.Dynamics.GP.IntegrationManager.ini by default) where the False value needs to be changed to True:

[IMGPPrv]
ShowDynamics=False
DoUIRedraw=False

When these switches are set to True, the Microsoft Dynamics GP windows are not hidden when the integration runs.

We found that for the Inventory Items integration, accounts either didn’t exist, didn’t allow account entry or were inactive; for the Sales Transaction integration, Analytical Accounting (AA) had been enabled for one of the accounts and the integration didn’t handle AA.

I created a VB script for the Inventory Items integration to check the accounts in the Before Document script and cancel the document if the account was going to be a problem.

Access Windows Settings App Using ms-settings URIs

WindowsWindows 10 saw the introduction of a enw Settings app, which has been improved in every major update. The Settings app is a replacement for the old Control Panel, the applets of which could be accessed from either the command line or from the Run dialog.

The Settings app also allows you to directly open individual settings pages by using a URI. The list of URI’s below is for Windows 10 “Creators Update” (version 1704).

Not all dettings pages will be available in every SKU of Windows 10. The notes column details any additional requirements that must be met for a page to be available.

Continue reading “Access Windows Settings App Using ms-settings URIs”

Update Taxes In Fabrikam Sample Company to Add Current UK VAT Rates

Microsoft Dynamics GPWhile the Fabrikam sample company is very US-centric, it is still somewhat useful to use as it has data spread across lots of different modules. The sample data hasn’t been updated for years and so still has the UK VAT rate set at 17.5%; VAT was raised to 20% in the 2010 budget and came into force in January 2011.

I created a script a while ago which could be used to create VAT rates for use in both Purchasing and Sales.

In Sales only a Standard 20% rate is created, but for purchasing rates are created for the Standard 20%, reduced 5%, Exempt and Zero Rated.

/*
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).
*/
-- Sales/Purchases Tax Schedule Header Master (TX00101)
INSERT INTO TX00101
	(TAXSCHID,TXSCHDSC,NOTEINDX)
VALUES
	-- Purchasing
	('UKVATP','UK VAT All Purchasing',0)
	,('UKVATPS','UK VAT Purchasing Standard',0)
	,('UKVATPR','UK VAT Purchasing Reduced',0)
	,('UKVATPE','UK VAT Purchasing Exempt',0)
	,('UKVATPZ','UK VAT Purchasing Zero',0)
	-- Sales
	,('UKVATS','UK VAT All Sales',0)
	,('UKVATSS','UK VAT Sales Standard',0)
GO

-- Sales/Purchases Tax Schedule Master (TX00102)
INSERT INTO TX00102
	(TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate)
VALUES
	-- Purchasing
	('ALL DETAILS','UKVATPS',3,0,0)
	,('ALL DETAILS','UKVATPR',3,0,0)
	,('ALL DETAILS','UKVATPE',3,0,0)
	,('ALL DETAILS','UKVATPZ',3,0,0)
	,('UKVATP','UKVATPS',3,0,0)
	,('UKVATP','UKVATPR',3,0,0)
	,('UKVATP','UKVATPE',3,0,0)
	,('UKVATP','UKVATPZ',3,0,0)
	,('UKVATPS','UKVATPS',3,0,0)
	,('UKVATPR','UKVATPR',3,0,0)
	,('UKVATPE','UKVATPE',3,0,0)
	,('UKVATPZ','UKVATPZ',3,0,0)
	--Sales
	,('ALL DETAILS','UKVATSS',3,0,0)
	,('UKVATS','UKVATSS',3,0,0)
	,('UKVATSS','UKVATSS',3,0,0)
GO

--Sales/Purchases Tax Master (TX00201)
INSERT INTO TX00201
	(TAXDTLID,TXDTLDSC,TXDTLTYP,ACTINDX,TXIDNMBR,TXDTLBSE,TXDTLPCT,TXDTLAMT,TDTLRNDG,TXDBODTL,TDTABMIN,TDTABMAX,TDTAXMIN,TDTAXMAX,TDRNGTYP,TXDTQUAL,TDTAXTAX,TXDTLPDC,TXDTLPCH,TXDXDISC,CMNYTXID,NOTEINDX,NAME,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAX,TXUSRDF1,TXUSRDF2,VATREGTX,TaxInvReqd,TaxPostToAcct,TaxBoxes,IGNRGRSSAMNT,TDTABPCT)
VALUES
	-- Purchasing
	('UKVATPS','UK VAT Purchasing Standard',2,90,'',3,20.00000,0,1,'',0,0,0,0,1,1,0,0,'',0,'',0,'','','','','','','','','','','','','','','',0,0,1,0x00000000,0,0)
	,('UKVATPR','UK VAT Purchasing Reduced',2,90,'',3,5.00000,0,1,'',0,0,0,0,1,1,0,0,'',0,'',0,'','','','','','','','','','','','','','','',0,0,1,0x00000000,0,0)
	,('UKVATPE','UK VAT Purchasing Exempt',2,90,'',3,0.00000,0,1,'',0,0,0,0,1,1,0,0,'',0,'',0,'','','','','','','','','','','','','','','',0,0,1,0x00000000,0,0)
	,('UKVATPZ','UK VAT Purchasing Zero',2,90,'',3,0.00000,0,1,'',0,0,0,0,1,1,0,0,'',0,'',0,'','','','','','','','','','','','','','','',0,0,1,0x00000000,0,0)
	-- Sales
	,('UKVATSS','UK VAT Sales Standard',1,89,'',3,20.00000,0,1,'',0,0,0,0,1,1,0,0,'',0,'',0,'','','','','','','','','','','','','','','',0,0,1,0x00000000,0,0)
GO

--Sales/Purchases Tax Summary Master (TX00202)
INSERT INTO TX00202
	(TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST)
VALUES
	-- Purchases
	('UKVATPS',0,0,0,0,0,0,1,1)
	,('UKVATPR',0,0,0,0,0,0,1,1)
	,('UKVATPE',0,0,0,0,0,0,1,1)
	,('UKVATPZ',0,0,0,0,0,0,1,1)
	-- Sales
	,('UKVATSS',0,0,0,0,0,0,1,1)
GO

This script has only ever been tested in the Fabrikam sample company; the script will need to be run again any time you redeploy the sample company using GP Utilities.

SQL Script to Alter Server and Database Views After Copying Live To Test

Microsoft Dynamics GPMicrosoft Dynamics GP includes a number of views in the database which are used to drive some of the reporting (such as the Refreshable Excel Reports); these reports contain drill down links. I’ve never really done that much work with the views myself, but I was alerted to an issue by a partner organisation who was creating some reports from Dynamics GP integrated with data from their system.

As development was in progress, the partner was working on the clients standalone test system, but when they tested the drill downs, the data was coming from the live system. I did some exploring of the views and found that the drilldowns are coded during deployment to include the server and database:

'Account Index For Drillback' = 'dgpp://DGPB/?Db=GP&Srv=2018SQL1\GP&Cmp=TEST&Prod=0' +dbo.dgppAccountIndex(1,['Account Master'].[ACTINDX] )

This issue will affect the views not only when the databases are copied to a new system, but also when a live company is copied to a test company on the same server.

The solution was to create a script which would alter the views to the new server or database:

/*
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 @ViewDefinition AS NVARCHAR(max)

DECLARE @OldDatabase AS VARCHAR(5) = 'TWO'
DECLARE @NewDatabase AS VARCHAR(5) = DB_NAME()

DECLARE @OldServer AS VARCHAR(50) = '2018SQL1'
DECLARE @NewServer AS VARCHAR(50) = @@ServerName

CREATE TABLE #ViewDefinitions(
	ViewDefinition NVARCHAR(MAX)
	,ROW_ID INT IDENTITY
)

INSERT INTO #ViewDefinitions
	(ViewDefinition)
--VALUES
	(SELECT
		REPLACE(
			REPLACE(['SQL Modules'].definition, 'CREATE VIEW', 'ALTER VIEW')
		,'Srv=' + @OldServer + '&Cmp=' + @OldDatabase,'Srv=' + @NewServer + '&Cmp=' + @NewDatabase)
	FROM
		sys.all_views AS ['All Views']
	JOIN
		sys.sql_modules AS ['SQL Modules']
			ON
				['SQL Modules'].object_id = ['All Views'].object_id
	and
		['SQL Modules'].definition LIKE '%Srv=' + @OldServer + '&Cmp=' + @OldDatabase + '%')

DECLARE
	cursor_Views Cursor
FOR
	SELECT
		ViewDefinition
	FROM
		#ViewDefinitions
	
	Open cursor_Views

	FETCH NEXT FROM
		cursor_Views
	INTO
		@ViewDefinition
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)
			EXEC (@ViewDefinition)
			FETCH NEXT FROM
				cursor_Views
			INTO
				@ViewDefinition
		END
	CLOSE cursor_Views
DEALLOCATE cursor_Views

DROP TABLE #ViewDefinitions
GO

The script uses the @@ServerName and DB_Name functions for the new server and database names; the two highlighted sections are the parameters for the old server and database which need to be amended.

As this script updates the views, make sure you have a good database backup before running.

Updated 24/10/2018 following feedback fromDavid Morinello

Logical File Name SQL Scripts: SQL Script to Create Restore Scripts for all Dynamics Databases

Microsoft SQL ServerSince the release of Microsoft Dynamics GP 2018, on the 1st December 2018, we have been busy with a few upgrade projects for clients. Our standard recommendation is to have a standalone test system for performing UAT and this means backing up and restoring databases.

While the backups are easy to do, the restores can be trickier when one database has been restored into another, as this means the logical file name will not match the physical one.

This isn’t an issue when the client has only a handful of databases, but when there are 30 or even a hundred, it becomes much more of an issue. However, you can select database details in SQL Server from the sys.master_files single, system-wide view.

This script creates restore scripts for all Dynamics GP databases and will use the correct logical filename whether it matches the . The script is configured with the assumption that the system database is called DYNAMICS; if you are using a named system database, change 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).
*/
USE DYNAMICS
GO

DECLARE @BackupLocation VARCHAR(300) = 'I:\Live Upgrade\2016R2\'
DECLARE @DataLocation VARCHAR(300) = 'S:\Data\'
DECLARE @LogLocation VARCHAR(300) = 'L:\Logs\'
DECLARE @DateParameter VARCHAR(10) = FORMAT(GETDATE(), 'yyyy-MM-dd')
DECLARE @SQLStatement NVARCHAR(MAX)
SELECT @SQLStatement =	'
	USE master
	GO
	RESTORE DATABASE ' + DB_NAME() + ' FROM  
	DISK = N''' + @BackupLocation + DB_NAME() + '_' + @DateParameter + '.bak'' WITH  FILE = 1,  
	MOVE N''' + ['databases-data'].name + ''' TO N''' + @DataLocation + RIGHT(['databases-data'].physical_name, CHARINDEX('\', REVERSE(['databases-data'].physical_name)) - 1) + ''',  
	MOVE N''' + ['databases-log'].name + ''' TO N''' + @LogLocation + RIGHT(['databases-log'].physical_name, CHARINDEX('\', REVERSE(['databases-log'].physical_name)) - 1) + ''',  NOUNLOAD,  STATS = 5
	GO
	'
FROM
	sys.master_files AS ['databases-data']
INNER JOIN
	sys.master_files AS ['databases-log']
		ON
			DB_NAME(['databases-log'].database_id) = DB_NAME(['databases-data'].database_id)
WHERE
	DB_NAME(['databases-data'].database_id) = DB_NAME() and ['databases-data'].name like '%.mdf'

SELECT @SQLStatement = @SQLStatement + 
	'RESTORE DATABASE ' + RTRIM(['Company Master'].INTERID) + ' FROM  
	DISK = N''' + @BackupLocation + RTRIM(['Company Master'].INTERID) + '_' + @DateParameter + '.bak'' WITH  FILE = 1,  
	MOVE N''' + ['databases-data'].name + ''' TO N''' + @DataLocation + RIGHT(['databases-data'].physical_name, CHARINDEX('\', REVERSE(['databases-data'].physical_name)) - 1) + ''',  
	MOVE N''' + ['databases-log'].name + ''' TO N''' + @LogLocation + RIGHT(['databases-log'].physical_name, CHARINDEX('\', REVERSE(['databases-log'].physical_name)) - 1) + ''',  NOUNLOAD,  STATS = 5
	GO
	'
FROM
	SY01500 AS ['Company Master']
INNER JOIN
	sys.master_files AS ['databases-data']
		ON
			DB_NAME(['databases-data'].database_id) = ['Company Master'].INTERID AND ['databases-data'].name LIKE '%mdf'
INNER JOIN
	sys.master_files AS ['databases-log']
		ON
			DB_NAME(['databases-log'].database_id) = ['Company Master'].INTERID AND ['databases-log'].name LIKE '%ldf'
WHERE
	['databases-data'].name NOT LIKE '%<TEST>'
AND
	['Company Master'].CMPANYID > 0
ORDER BY
	['Company Master'].INTERID
	,['databases-data'].name

SELECT @SQLStatement

Logical File Name SQL Scripts: SQL Script to Create Backup Scripts for all Dynamics Databases

Microsoft SQL ServerSince the release of Microsoft Dynamics GP 2018, on the 1st December 2018, we have been busy with a few upgrade projects for clients. Our standard recommendation is to have a standalone test system for performing UAT and this means backing up and restoring databases.

While the backups are easy to do, the restores can be trickier when one database has been restored into another, as this means the logical file name will not match the physical one.

This isn’t an issue when the client has only a handful of databases, but when there are 30 or even a hundred, it becomes much more of an issue. However, you can select database details in SQL Server from the sys.master_files single, system-wide view.

This script creates backup scripts for all Dynamics GP databases. The script is configured with the assumption that the system database is called DYNAMICS; if you are using a named system database, change 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).
*/
USE DYNAMICS
GO

DECLARE @BackupLocation VARCHAR(300) = 'I:\Live Upgrade\2016R2\'
DECLARE @DateParameter VARCHAR(10) = FORMAT(GETDATE(), 'yyyy-MM-dd')
DECLARE @SQLStatement NVARCHAR(MAX) =	'
	USE master
	GO
	BACKUP DATABASE ' + DB_NAME() + ' TO
	DISK = N''' + @BackupLocation + DB_NAME() + '_' + @DateParameter + '.bak'' WITH NOFORMAT, INIT,    
	NAME = N''' + DB_NAME() + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
	GO
	'

SELECT @SQLStatement = @SQLStatement +
	'BACKUP DATABASE ' + RTRIM(['Company Master'].INTERID) + ' TO
	DISK = N''' + @BackupLocation + RTRIM(['Company Master'].INTERID) + '_' + @DateParameter + '.bak'' WITH NOFORMAT, INIT,    
	NAME = N''' + RTRIM(['Company Master'].INTERID) + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
	GO
	'
FROM
	SY01500 AS ['Company Master']
INNER JOIN
	sys.master_files ['databases-data']
		ON
			DB_NAME(['databases-data'].database_id) = ['Company Master'].INTERID AND ['databases-data'].name LIKE '%mdf'
WHERE
	['databases-data'].name NOT LIKE '%<TEST>'
AND
	['Company Master'].CMPANYID > 0
ORDER BY
	['Company Master'].INTERID
	,['databases-data'].name

SELECT @SQLStatement

Logical File Name SQL Scripts: SQL Script to Alter Logical Filenames of all Dynamics GP Company Databases

Microsoft SQL ServerSince the release of Microsoft Dynamics GP 2018, on the 1st December 2018, we have been busy with a few upgrade projects for clients. Our standard recommendation is to have a standalone test system for performing UAT and this means backing up and restoring databases.

While the backups are easy to do, the restores can be trickier when one database has been restored into another, as this means the logical file name will not match the physical one.

This isn’t an issue when the client has only a handful of databases, but when there are 30 or even a hundred, it becomes much more of an issue. However, you can select database details in SQL Server from the sys.master_files single, system-wide view.

This script creates an alter script for all Dynamics GP company databases’ logical file names which will ensure that all databases have a logical filename which matches the physical name. The script is configured with the assumption that the system database is called DYNAMICS; if you are using a named system database, change 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).
*/
USE DYNAMICS
GO

SELECT
	CASE WHEN ['databases'].name LIKE '%Dat%' THEN
	'ALTER DATABASE
		' + ['Company Master'].INTERID + '
	MODIFY FILE 
		(
		NAME = [' + RTRIM(['databases'].name) + ']
		,NEWNAME = [GPS' + RTRIM(['Company Master'].INTERID) + 'Dat.mdf]
		)
	 GO'
	ELSE
	'ALTER DATABASE
		' + ['Company Master'].INTERID + '
	MODIFY FILE 
		(
		NAME = [' + RTRIM(['databases'].name) + ']
		,NEWNAME = [GPS' + RTRIM(['Company Master'].INTERID) + 'Log.ldf]
		)
	 GO'
	 END
FROM
	SY01500 AS ['Company Master']
INNER JOIN
	sys.master_files AS ['databases']
		ON
			DB_NAME(['databases'].database_id) = ['Company Master'].INTERID
WHERE
	['Company Master'].CMPANYID > 0
AND
	(RTRIM(['databases'].name) <> 'GPS' + RTRIM(['Company Master'].INTERID) + 'Dat.mdf'
AND
	RTRIM(['databases'].name) <> 'GPS' + RTRIM(['Company Master'].INTERID) + 'Logldf')
ORDER BY
	['Company Master'].INTERID
	,['databases'].name

Logical File Name SQL Scripts: SQL Script to Return Logical and Physical Names for Dynamics GP Company Databases

Microsoft SQL ServerSince the release of Microsoft Dynamics GP 2018, on the 1st December 2018, we have been busy with a few upgrade projects for clients. Our standard recommendation is to have a standalone test system for performing UAT and this means backing up and restoring databases.

While the backups are easy to do, the restores can be trickier when one database has been restored into another, as this means the logical filename will not match the physical one.

This isn’t an issue when the client has only a handful of databases, but when there are 30 or even a hundred, it becomes much more of an issue. However, you can select database details in SQL Server from the sys.master_files single, system-wide view.

This script gets the logical file names for all Dynamics GP company databases attached to a system database which can be used to update the restore script so you can easily create backup scripts rather than having to go through the Restore Database for each database individually.

The script is configured with the assumption that the system database is called DYNAMICS; if you are using a named system database, change 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).
*/
USE DYNAMICS
GO

SELECT
	['Company Master'].INTERID AS InterID
	,['Company Master'].CMPNYNAM AS CompanyName
	,['databases'].name AS LogicalFileName
	,['databases'].physical_name AS PhysicalFileName 
FROM
	SY01500 AS ['Company Master']
INNER JOIN
	sys.master_files ['databases']
		ON
			DB_NAME(['databases'].database_id) = ['Company Master'].INTERID
WHERE
	['Company Master'].CMPANYID > 0
ORDER BY
	['Company Master'].INTERID
	,['databases'].name

This gets the logical file names which can be used to update the restore script so you can easily create backup scripts rather than having to go through the Restore Database for each database individually.