SQL Snippet: Get Dates for Accruals

Microsoft SQL ServerAs I’ve mentioned before I write a fair bit of SQL code for various projects or support calls and will be posting some of it here.

I recently created a report for a client to use to extract transaction lines to use to import as an accruals journal; as part of the extract I worked out the last day of the one month and the first day of the next to use as the transaction and reversing dates on the journal.

The scripts below has versions for both before and after SQL 2012 (with the introduction of the EOMONTH function in 2012, getting these dates became easier).

/*
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 date variable
DECLARE @Date DATETIME = GETDATE()

-- get last date of this month
SELECT CONVERT(VARCHAR(10), DATEADD(month, ((YEAR(@Date) - 1900) * 12) + month(@Date), -1), 126)

-- get last date of this month in SQL 2012
SELECT EOMONTH(@Date)

-- get first date of next month
SELECT CONVERT(VARCHAR(10), DATEADD(month, DATEDIFF(month, 1, DATEADD( month, 1, @Date )), 0), 126)

-- get first date of next month in SQL 2012
SELECT DATEADD(day, 1, EOMONTH(@Date))

SQL Collation – SQL_Latin1_General_CP1_CI_AS vs Latin1_General_CI_AS by Craig Verster via WinthropDC

Microsoft Dynamics GPOver the last few years as we have grown the ERP Practice at Perfect Image we have taken over a number of clients from other Microsoft Dynamics GP partners, both in the UK and elsewhere. Far too many of them have had Dynamics GP installed with the incorrect collation.

According to the system requirements, Microsoft Dynamics GP is supported with two collations:

  • SQL_Latin1_General_CP1_CI_AS (the recommend one as it is case insensitive).
  • Latin1_General_BIN

Whenever I install SQL Server, I am careful to ensure that I pick the SQL_Latin1_General_CP1_CI_AS collation which is supported; this is important to do, as a UK language server has Latin1_General_CI_AS as the default and you need to proactively make the change. A US server has the correct collation by default.

If I’m honest, I couldn’t have told you why this was important, but it is something I have always been very careful to do. I couldn’t have told you why this was important, until today that is, when I read a guest post by Craig Verster, Senior Microsoft Dynamics GP Consultant at Microchannel Services, on the a href=’https://winthropdc.wordpress.com/’>Winthrop DC blog.

The post by Craig explains why it is important to use the correct collation. Short version is that while both SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS are 1252 character collations, they handle the Þ character different which can cause incorrect data to be returned in the Reporting Service Reports; there could also be other places where this manifests.

I’d encourage everyone (especially consultants) to read and understand the post so we can stop clients having an incorrect collation installed on the SQL Server used with Microsoft Dynamics GP.

New GP Compatibility Page Available

Microsoft Dynamics GPI’ve added a GP Resources section to this site; as well as links to the GP Table Reference, I’ve created a Microsoft Dynamics GP Compatibility page which shows the compatibility with SQL Server, Windows Server and Windows from Dynamics GP 2010 through to 2018.

If you see any errors, or can contribute information on supported versions, prior to 2010, please contact me with the information.

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

Click to show/hide the Logical File Name SQL Scripts Series Index

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

Click to show/hide the Logical File Name SQL Scripts Series Index

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.

Logical File Name SQL Scripts: SQL Script to Return Logical and Physical Names for 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:

/*
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
	DB_NAME(database_id) AS DatabaseName
	,name AS LogicalFileName
	,physical_name AS PhysicalFileName 
FROM
	sys.master_files
ORDER BY
	DB_NAME(database_id)

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.

Click to show/hide the Logical File Name SQL Scripts Series Index

Logical File Name SQL Scripts: Series Index

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.

I started off with a simple script and then developed it through to generating the backup and restore scripts directly.

This small series includes the four scripts I created.

Click to show/hide the Logical File Name SQL Scripts Series Index