Formatting SQL

Microsoft SQL ServerI create a fair amount of T-SQL in the form of selects, views and stored procedures. While I try to format my code to make it easy to use, I do encounter code from other people which is not very well formatted; and if you look at the stored procedures or views which ship with Microsoft Dynamics GP, they are generally formatted with all of the code on a single line.

There are a variety of ways of formatting SQL and I’m sure there are addins for SQL Server Management Studio; however, I often work on clients servers and can’t just install anything I want.

Therefore, a web based alternative is favourite and SQLFormat is the best one I have come across so far.

To use, simple paste your code into the big box and click the Format SQL button:

SQLFormat

You can even load a file should you wish.

I’m posting it here as a reminder to myself.

How to Install Microsoft SQL Server 2017: Download SSRS 2017

Microsoft SQL ServerIn this short series of posts, I am going to be covering the installation of Microsoft SQL Server 2017; the installation process for this version is somewhat different to previous ones, in that SQL Server Management Studio and SQL Server Reporting Services have been separated out from the main installer.

The series index can be found here. I am not a SQL expert and this series of posts is intended only for use on demo or test environment and not production ones.

This is a very late addition to this series of posts; I recently came to install SQL Server 2017 on a new machine which did not have an Internet connection and so needed to download the installer and copy itto the new machine.

In the installation post I used the download option in the installer, but didn’t include the direct download link.

Well, to remedy that, here is the download link to the latest version of SSRS 2017.

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

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.