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) WHERE 'TWO'
DECLARE @NewDatabase AS VARCHAR(5) WHERE DB_NAME()

DECLARE @OldServer AS VARCHAR(50) WHERE '2018SQL1'
DECLARE @NewServer AS VARCHAR(50) WHERE @@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 WHERE ['All Views'].object_id
	WHERE
		['SQL Modules'].definition LIKE '%Srv=' + @OldServer + '&Cmp=' + @OldDatabase + '%')

DECLARE
	cursor_Views Cursor
FORM
	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.

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()

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

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

Automate SQL Server Express Backups

Microsoft SQL ServerWe have a couple of small clients who are hosting Microsoft Dynamics GP on SQL Server Express; this is a supported platform, but does not include SQL Server Agent. This means that backups cannot be automated in SQL Server as it is the Agent which provides this functionality.

However, there are still ways in which backups can be automated; one way is to create a DOS batch file and execute it using Windows Scheduler.

The below is a SQL statement which can be executed to generate the DOS batch file commands to backup all of the databases for a Microsoft Dynamics GP implementation.

/*
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).
*/
DECLARE @SQL NVARCHAR(MAX) = 'echo off
cls
echo -- BACKUP DATABASE --
set SERVERNAME=2018SQL1\GP
set DATESTAMP=%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%
set SYSTEMDATABASENAME=DYNAMICS
set BACKUPFILENAME=C:\SQLServer\Backup\%SYSTEMDATABASENAME%_%DATESTAMP%.bak
echo.
sqlcmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%SYSTEMDATABASENAME%] TO DISK = N''%BACKUPFILENAME%'' WITH NOFORMAT, INIT, NAME = N''%SYSTEMDATABASENAME%-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
echo.'

SELECT @SQL = @SQL + STUFF((
					SELECT 
						'
set DATABASENAME=' + RTRIM(INTERID) + '
set BACKUPFILENAME=C:\SQLServer\Backup\%DATABASENAME%_%DATESTAMP%.bak
sqlcmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%DATABASENAME%] TO DISK = N''%BACKUPFILENAME%'' WITH NOFORMAT, INIT, NAME = N''%DATABASENAME%-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
echo.'
					FROM
						DYNAMICS.dbo.SY01500
					FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SELECT @SQL

If you are using a named system database, the two highlighted sections need to be amended.

To run the script, set the output to file and then copy and paste the resulting script into a file with the .bat extension; this batch file can then be scheduled to run automatically using Windows Scheduler.

Problems Logging into Microsoft Dynamics GP as sa After Password Change

Microsoft Dynamics GPI’ve been working with a client recently on an upgrade to Microsoft Dynamics GP 2018 and all has gone well overall. However, they had a security project running at the same time, where they were changing the passwords for a number of accounts including the SQL Srver sa account.

Unfortunately, the password length they were setting was 20 characters,. They supplied the new password to me, but I found I couldn’t log in. When I then tried to copy and paste the password into the field, nothing happened (which is what clued me into the issue).

The cause of this login issue, is that the Password field on the Users Master (SY01400) table has a maximum Keyable Length of 15 characters, and this restriction is enforced on all user ids entered on the login window.

So for me to complete the final steps of the upgrade, the password for the sa account was changed to a 15 character one; after the upgrade was complete, the password was again changed to a longer one and then the account disabled.

Best practice is to use the sa account to administer only SQL Server and to use either a named user account in Dynamics GP or to use the DYNSA account. I am encouraging all clients to ensure this is the case.

There is a wide range of ways you can better secure your system. One way of working through these is to engage with your partner and another is to purchase the Microsoft Dynamics GP Security and Audit Field Manual: Dynamics GP 2016 book by MVP Mark Polino and Andy Snook. This book is for the 2016 version of Dynamics GP, but still fully applies to Microsoft Dynamics GP 2018 (as well as earlier versions).

How to Install Microsoft SQL Server 2017: SSRS 2017 Not Supported on a Domain Controller

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.

I often create two types of demo or test environment; one will have multiple virtual machiens createdin order to replicate a typical live environment, and the second has only a single virtual machine.

While prepping a test environment of the latter type in readiness for the launch of Microsoft Dynamics GP 2018 RTM, I encountered a problem with SSRS:

Microsoft SQL Server 2017 Reporting Services installation issue

Microsoft SQL Server 2017 Reporting Services

Setup blocked

The following issues blocked Setup

Installing Reporting Services on a domain controller is not supported

This is a major departure from SQL Server 2016 and previous, all of which allowed SSRS to be installed on a Domain Controller; for most systems this will be absolutely fine, but I’ll no longer be able to use single virtual machine demo or test environments.

Click to show/hide the How to Install Microsoft SQL Server 2017 Series Index