SQL Script to Prefix Email Message Subjects with Test

● Ian Grieve ●  ● 2 Comments   ● 

Microsoft Dynamics GPI had an issue reported with purchase requisitions the other day. I logged on and did a little testing and then, a short wile later, got an email from the client asking about some workflow approval emails some managers had received.

The email to the approvers did not contain the word “test” anywhere in either the subject or body. This lead to me having a horrible moment when I thought I might have done my testing on live; I was able to confirm quickly that I had not.

After this I decided I had better knock a quick script together which the client could incorporate into their live to test copy.

The script is more engineered than it might otherwise need to be as I have included code to only do the update when it is running against a test company (flagged with <TEST> at the end of the Company Name) and where the email subject isn’t already prefixed with the word TEST:

/*
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).
*/
UPDATE
	['Email Messages']
SET
	EmailMessageSubject = LEFT('TEST: ' + CAST(['Email Messages'].EmailMessageSubject AS CHAR(150)), 150)
FROM
	SY04901 AS ['Email Messages']
INNER JOIN
	DYNAMICS..SY01500 AS ['Company Master']
		ON
			['Company Master'].INTERID = DB_NAME()
WHERE
	['Company Master'].CMPNYNAM LIKE '%<TEST>'
AND
	LEFT(['Email Messages'].EmailMessageSubject, 4) <> 'TEST'
GO

As always, before running a script against a database, make sure you understand the scirpt and have a good backup of the database.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

SQL Snippet: Generate GUID

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft SQL ServerIf you’ve been following this blog, you’ll know that I write a fair bit of SQL. I’m going to post some small snippets of SQL which I had to work out how to accomplish a small task as part of a larger script.

This fourth example is going to be, by far, the shortest pioece of SQL I post. It shows how to return new GUID:

/*
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).
*/
SELECT NEWID()

I discovered this function when looking for a way to generate new GUIDs for a large Workflow implementation for a client where we are insertin the workflow steps via SQL rather than through the UI. This is very much not the recommended way of creating a workflow process, but the approval requirements resulted in a very large number of workflow steps and tackling it in this way, saved us a large amount of time.

● Categories: Microsoft, SQL Server ● Tags: , , , ,  ● Permalink ● Shortlink ●

SQL Snippet: Format Dates

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft SQL ServerIf you’ve been following this blog, you’ll know that I write a fair bit of SQL. I’m going to post some small snippets of SQL which I had to work out how to accomplish a small task as part of a larger script.

This second example, shows how to format a date in two of the most common formats I work with. Each example returns the date using the FORMAT command introduced in SQL Server 2012 and the more traditional method.

The first example, returns the date as day month year separated with /:

/*
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 @DATE DATETIME = '2017-05-31 11:59:59.000'

SELECT
	CONVERT(VARCHAR(10), @DATE, 103)
	,FORMAT(@DATE, 'dd/MM/yyyy')

The second returns the date in ISO 8601 format:

/*
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 @DATE DATETIME = '2017-05-31 11:59:59.000'

SELECT
	CONVERT(VARCHAR(10), @DATE, 126)
	,FORMAT(@DATE, 'yyyy-MM-dd')
● Categories: Microsoft, SQL Server ● Tags: , ,  ● Permalink ● Shortlink ●

SQL Script To Get All Company Database Sizes

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPFollowing on from my recent script to return the functional currency for all companies I hve revisited an old script which gets the size of database and updated it to work in the same way.

This script needs to be run against your system database (called DYNAMICS by default) and it will return the size of the data and log files alongside the total size of the database for all companies.

/*
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 @SQL NVARCHAR(MAX)

CREATE TABLE #DatabaseSizes(
	INTERID VARCHAR(5)
	=CMPNYNAM VARCHAR(65)
	=DataSize VARCHAR(20)
	=LogSize VARCHAR(20)
	=TotalSize VARCHAR(20)
)

SELECT
	@SQL = STUFF((
			SELECT 
				CHAR(13) + '
				SELECT
					''' + INTERID + '''
					,''' + CMPNYNAM + '''
					,LTRIM(STR(CONVERT(DEC (15,2),SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 = 0 THEN SIZE ELSE 0 END))) * 8192 / 1048576,15,2) + '' MB'')
					,LTRIM(STR(CONVERT(DEC (15,2),SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 <> 0 THEN SIZE ELSE 0 END))) * 8192 / 1048576,15,2) + '' MB'')
					,LTRIM(STR(CONVERT(DEC (15,2),SUM(CONVERT(BIGINT,SIZE))) * 8192 / 1048576,15,2) + '' MB'')
				FROM
					' + INTERID + '.dbo.sysfiles'
			FROM
				SY01500
			ORDER BY
				CMPNYNAM
			FOR
				XML PATH(''), TYPE(.value('.', 'NVARCHAR(MAX)'), 1, 1, '')

INSERT INTO #DatabaseSizes
	EXEC sys.sp_executesql @SQL
GO

SELECT
	*
FROM
	#DatabaseSizes
GO

DROP TABLE #DatabaseSizes
GO
● Categories: Dynamics, GP, Microsoft ● Tags: , , , ,  ● Permalink ● Shortlink ●

Find Column In SQL Using INFORMATION_SCHEMA

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPLast year I posted a script to find tables containing a particular column using sys objects. Steve Endow of Dynamics GP Land suggested using the INFORMATION_SCHEMA instead as he found it easier to use.

I’ve recently had reason to search for tables with a particular column in them, so I took a look at using a script using INFORMATION_SCHEMA.COLUMNS. However, when taking a detailed look at the results I found a few anomalies; the issue was that INFORMATION_SCHEMA.COLUMNS returns results for columns in not only tables, but also views. Which does make sense as both tables and views have columns. For what I was working on I needed a list of only tables.

I did a little exploring of the INFORMATION_SCHEMA and determined that I could join to INFORMATION_SCHEMA.TABLES and filter on TABLE_TYPE <> ‘VIEW’ to get a result set of only tables:

/*
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 @ColumnToFind VARCHAR(20) = 'PAYRCORD'
SELECT
	['Tables'].TABLE_SCHEMA AS 'Schema'
	,['Tables'].TABLE_NAME AS 'Table'
FROM
	INFORMATION_SCHEMA.COLUMNS AS ['Columms']
INNER JOIN
	INFORMATION_SCHEMA.TABLES AS ['Tables']
		ON
			['Tables'].TABLE_CATALOG = ['Columms'].TABLE_CATALOG
		AND
			['Tables'].TABLE_SCHEMA = ['Columms'].TABLE_SCHEMA
		AND
			['Tables'].TABLE_NAME = ['Columms'].TABLE_NAME
		AND
			['Tables'].TABLE_TYPE <> 'VIEW'
WHERE
	COLUMN_NAME = @ColumnToFind
ORDER BY
	'Schema'
	,'Table'

In the original posts script I was using the sys objects directly, but was filtering out the views by joining to sys.tables which contains only tables. Both the original script and the above one return exactly the same result set.

So, what’s the difference?

INFORMATION_SCHEMA, or System Information Schema Views to give the full name, is one of several methods SQL Server provides to get an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes may have been made to the underlying system tables. The information schema views included in SQL Server originally complied with the ISO standard definition for the INFORMATION_SCHEMA, but appear to have diverged from the standard as new standards have been introduced.

The metadata returned by INFORMATION_SCHEMA, comes from the sys objects. So by using the former you are getting information from the latter, but in a way which should be future proofed against database changes.

● Categories: Microsoft, SQL Server ● Tags: , , ,  ● Permalink ● Shortlink ●

SQL Script To Return Functional Currencies For All Companies Without a Cursor

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPI posted a script a while ago which used a cursor to return the functional currencies for all companies connected to a system database. However, I have recently revisited this script and created a version which does not use a cursor.

This script has been written to only return the companies which do not have a functional currency set; if you want to see all companies, regardless of the functional currency, remove 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).
*/
CREATE TABLE #FunctionalCurrencies(
	INTERID VARCHAR(5)
	,FUNLCURR VARCHAR(20)
)
GO

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
					SELECT 
						CHAR(13) 
							+ 'SELECT 
								''' + INTERID + '''
								,FUNLCURR
							FROM
								' + INTERID + '.dbo.MC40000
							WHERE
								LEN(FUNLCURR) = 0'
					FROM
						DYNAMICS.dbo.SY01500
					FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

INSERT INTO #FunctionalCurrencies
	EXEC sys.sp_executesql @SQL
GO

SELECT * FROM #FunctionalCurrencies
GO

DROP TABLE #FunctionalCurrencies
GO
● Categories: Dynamics, GP, Microsoft, Multicurrency ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

Restore The Account Segment Warning

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPWhen new accounts are being created in Microsoft Dynamics GP a warning message is displayed if an entered segment does not exist in the Segment Master (GL40200) table:

Restore The Account Segment Warning

If the checkbox is marked then the warning message is not displayed for that user anymore and there is no way to restore the message through the front end of the system.

The below script, allows the message to be restored for a named user (change the highlighted text to the required user):

/*
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 @USERID VARCHAR(20)
SET @USERID = 'userid'

DELETE FROM
	SY01401
WHERE
	coDefaultType = 13
AND
	USERID = @USERID
GO
● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

SQL Script To Activate Horizontal Scroll Arrows

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPThe Account field in Microsoft Dynamics GP is of a fixed width, but the maximum width of the account string themselves can be much longer than the field.

The solution Microsoft have supplied is an option which activates horizontal scroll arrows in the Account field:

Acount Maintenance

This is activated, on a per user basis, via the User Preferences window (Microsoft Dynamics GP menu >> User Preferences) by marking the Horizontal Scroll Arrows:

User Preferences

However, if you have a lot of users, this can sometimes be a difficult message to disseminate.

An alternative I came up with for a client a wile ago was to create a trigger on the Users Master (SY01400) table which updates the field after a new user is created:

/*
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).
*/
CREATE TRIGGER
	utr_AZRCRV_UpdateSY01400ActivateHorizontalScrollArrows
ON
	SY01400
AFTER INSERT AS
	UPDATE
		['Users Master']
	SET
		HSCRLARW = 1
	FROM
		SY01400 AS ['Users Master']
	INNER JOIN
		inserted AS INS
			ON
				INS.USERID = ['Users Master'].USERID
GO

If users have already been created, then the following script can be used to activate the horizontal scroll arrows for them:

/*
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).
*/
UPDATE
	SY01400
SET
	HSCRLARW = 1
WHERE
	HSCRLARW = 0
GO

We actually decided to remove the trigger and apply the update script via a SQL Server Agent scheduled job which runs on a period basis (if I recall correctly, it was configured to run each evening at 2100 (avoiding backup jobs).

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

SQL Script To Log Into Each Company

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPI have a few of clients who have a large number of companies. This generally isn’t a problem as Microsoft Dynamics GP supports this very well.

However, after doing an upgrade of a system which has the VAT Daybook module installed, you need to log into each and every database using the sa account.

Doesn’t sound so bad on the surface, but when you have two hundred companies, this soon adds up to a substantial length of time.

I did a little looking around a while ago for ways to automate this process and found a post by Aaron Berquist from 2011 where he had done exactly this.

However, when we tried his script we got a few errors, so I made a few changes and improvements; my script automatically adds OK button clicks for Test, Historic and Fabrikam sample company databases.

The Test and Historic databases are identified by having or at the end of their names; this is what Dynamics GP uses to display the message.

I removed the temporary table from Aaron’s script as well; when my version of the script is run, output the results to text (make sure you have increased the query results length).

You can copy and paste the output into a Notepad document which can be saved with a .mac extension.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
Based on idea and code from http://www.aaronberquist.com/2011/07/use-dynamic-sql-to-generate-a-dynamics-gp-login-macro/
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	'# DEXVERSION=16.00.0033.000 2 2
	  CommandExec dictionary ''default''  form ''Command_System'' command ''Switch Company'' 
		NewActiveWin dictionary ''default''  form ''Switch Company'' window ''Switch Company'' 
		  ClickHit field ''(L) Company Names'' item ' + CONVERT(VARCHAR(3), ROW_NUMBER() OVER (ORDER BY CMPANYID)) + '  # ' + LTRIM(RTRIM(CMPNYNAM)) + ' 
		  MoveTo field ''OK Button'' 
		  ClickHit field ''OK Button''
		NewActiveWin dictionary ''default''  form sheLL window sheLL 
		NewActiveWin dictionary ''default''  form sheLL window sheLL ' +
		CASE WHEN RTRIM(CMPNYNAM) LIKE '%' THEN
			'
			  # This company is set up for testing only. Do not use this company when processing live data.
			NewActiveWin dictionary ''default''  form ''SY_Error_Message'' window ''SY_Error_Message'' 
			  ClickHit field ''OK Button'' 
			NewActiveWin dictionary ''default''  form sheLL window sheLL '
		WHEN RTRIM>(CMPNYNAM) LIKE '%' THEN
			'
			  # This company is used for storing historical data only. Do not use this company when processing current-year data.
			NewActiveWin dictionary ''default''  form ''SY_Error_Message'' window ''SY_Error_Message'' 
			  ClickHit field ''OK Button'' 
			NewActiveWin dictionary ''default''  form sheLL window sheLL '
		WHEN CMPANYID = -1 THEN
			'
			  # You have chosen to use the sample company, which provides data that you can use to practice procedures or learn more about the product. When you use this sample company, the date is automatically set to April 12, 2017.
			NewActiveWin dictionary ''default''  form DiaLog window DiaLog 
			  ClickHit field OK 
			NewActiveWin dictionary ''default''  form sheLL window sheLL '
		ELSE
			''
		END
FROM
	DYNAMICS..SY01500
ORDER BY
	CMPNYNAM
● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

Upgrading to Microsoft Dynamics GP 2016 R2: Adding Missing Security Roles

● Ian Grieve ●  ● 3 Comments   ● 

Microsoft Dynamics GPWith Microsoft Dynamics GP 2016 R2 now being available, there are some new security roles and tasks which need to be added.

As they have for the last few versions, Microsoft have made available some SQL Scripts to insert these roles and tasks.

The Dynamics GP 2016 R2 ones can be downloaded from directly from Microsoft or I have a post containing links to all of the versions I can find, which has been updated for Dynamics GP 2016, which is here.

The scripts tend to be cumulative, so if you upgrade from Microsoft Dynamics GP 2013 SP2 to 2016 R2, you only need to run the final script.

Not sure why I thought the scripts were cumulative, but they are not. All of the scripts need to be run in sequence for your upgrade path.

As always, when running scripts against Dynamics GP databases, ensure you have a good backup before starting.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●