SQL Script: Get First Email from Semi-colon delimited string

Microsoft SQL ServerI had a request to produce a SQl view for a client recently which extracted the first email address from the EmailToAddress field in the Address Electronic Funds Transfer Master (SY06000) table linked to a creditor record in Microsoft Dynamics GP. This field generally stores a single email, but sometimes stores multiple email addresses separated with a semi-colon.

The below script will extract the first email address from the field if it is delimited with a semi-colon or the entire content of the field if there is no semi-colon.

/*
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 CASE WHEN SUBSTRING(EmailToAddress, 0, CHARINDEX(';', EmailToAddress)) = '' THEN EmailToAddress ELSE SUBSTRING(EmailToAddress, 0, CHARINDEX(';', EmailToAddress)) END AS Email FROM SY01200 -- Address Electronic Funds Transfer Master (SY06000)

SQL Query to Get Login Password Expiry

Microsoft SQL ServerI don't recall exactly why this script was required, but the below SQL query can be used to get the expiration date for logins in Microsoft SQL Server. Some of the data is available directly from the sys.sql_logins table, but other pieces had to be retrieved using the LOGINPROPERTY function:

SELECT
	['SQL Logins'].name AS 'LoginName'
	,LOGINPROPERTY(['SQL Logins'].name, 'PasswordLastSetTime') AS 'PasswordLastSetTime'
	,LOGINPROPERTY(['SQL Logins'].name, 'DaysUntilExpiration') AS 'DaysUntilExpiration'
	,DATEADD(dd,CONVERT(int, LOGINPROPERTY (['SQL Logins'].name, 'DaysUntilExpiration')),CONVERT(datetime,LOGINPROPERTY(['SQL Logins'].name,'PasswordLastSetTime'))) AS 'PasswordExpiration'
	,['SQL Logins'].is_policy_checked AS 'IsPolicyChecked'
	,LOGINPROPERTY(['SQL Logins'].name, 'IsExpired'') AS 'IsExpired'
	,LOGINPROPERTY(['SQL Logins'].name, 'IsMustChange'') AS 'IsMustChange'
	,LOGINPROPERTY(['SQL Logins'].name, 'IsLocked'') AS 'IsLocked'
	,LOGINPROPERTY(['SQL Logins'].name, 'LockoutTime'') AS 'LockoutTime'
	,LOGINPROPERTY(['SQL Logins'].name, 'BadPasswordCount'') AS 'BadPasswordCount'
	,LOGINPROPERTY(['SQL Logins'].name, 'BadPasswordTime'') AS 'BadPasswordTime'
	,LOGINPROPERTY(['SQL Logins'].name, 'HistoryLength'') AS 'HistoryLength'
FROM
	sys.sql_logins AS ['SQL Logins']
WHERE
	is_expiration_checked = 1 
ORDER BY
	['SQL Logins'].name

Find text in any SQL object

Microsoft SQL ServerI needed to find custom triggers or views which referenced a certain table and although I thought I had a script which would find text in a SQL object like a trigger, view or stored procedure, I couldn't find one when I searched my site the other day.

It only took me a few minutes to write one; the first highlighted text is the text to search for and the second a limitation on the name of the SQL objects to check.

/*
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 @Search varchar(255) SET @Search='PM00200' SELECT DISTINCT ['SQL Objects'].name AS Object_Name ,['SQL Objects'].type_desc FROM sys.sql_modules AS ['SQL Modules'] INNER JOIN sys.objects AS ['SQL Objects'] ON ['SQL Objects'].object_id=['SQL Modules'].object_id WHERE ['SQL Objects'].name LIKE 'u%_AZRCRV_%' AND ['SQL Modules'].definition LIKE '%'+@Search+'%' ORDER BY ['SQL Objects'].name ,['SQL Objects'].type_desc

SQL Script to update vendor and customer email addresses

Microsoft Dynamics GPBack in August 2011 I posted a SQL script which transfers vendor email addresses from the INET1 to the Email Address field and today's script is a variation of that one.

The original script I posted only did a transfer for vendors and would migrate the INET1 one field regardless of whether there was already something in the Email Address field.

This new script only does the transfer if INET1 is longer than 1 character and the Email Address field is empty.

It also sets the Email Bcc Field to a supplied email address.

/*
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 SY01200 SET EmailToAddress = INET1 ,EmailBccAddress = 'purchase.archive@example.com' WHERE Master_Type = 'VEN' AND LEN(CAST(INET1 AS VARCHAR(100))) > 0 AND LEN(CAST(EmailToAddress AS VARCHAR(100))) = 0 GO UPDATE SY01200 SET EmailToAddress = INET1 ,EmailBccAddress = 'sales.archive@example.com' WHERE Master_Type = 'CUS' AND LEN(CAST(INET1 AS VARCHAR(100))) > 0 AND LEN(CAST(EmailToAddress AS VARCHAR(100))) = 0 GO

SQL Script to get all accounts set as available in Purchasing lookups

Microsoft Dynamics GPFollowing on from the last post, in which I posted a script to get accounts linked to a vendor, this post is a script which returns all accounts which are set as available in lookups in the Purchasing series.

The SQL is straightforward, but I had to work out the binary entries to check for by saving and checking each combination.

/*
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 ['Account Master'].ACTINDX ,['Account Index Master'].ACTNUMST ,['Account Master'].ACTDESCR FROM GL00100 AS ['Account Master'] INNER JOIN GL00105 AS ['Account Index Master'] ON ['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX WHERE DSPLKUPS IN ( 0x04000000 -- purchasing ,0x05000000 -- sales/purchasing ,0x06000000 -- inventory control/purchasing ,0x07000000 -- sales/inventory control/purchasing ,0x0C000000 -- purchasing/payroll ,0x0D000000 -- sales/purchasing/payroll ,0x0E000000 -- inventory control/purchasing/payroll ,0x0F000000 -- sales/inventory control/purchasing/payroll ,0xFFFFFF7F -- never set ) GO

SQL Script to get all vendor purchasing accounts

Microsoft Dynamics GPI recently needed to select all assigned purchasing accounts for a particular vendor; this would include both the normal purchasing one on the Vendor Account Maintenance window and the additional ones in the Additional Vendor Accounts window.

The SQL is fairly straightforward, but I had to make sure each account was only included once:

/*
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 DISTINCT ['Account Master'].ACTINDX ,['Account Index Master'].ACTNUMST ,['Account Master'].ACTDESCR FROM PM00200 AS ['PM Vendor Master'] LEFT JOIN PM00203 AS ['Vendor Accounts'] ON ['Vendor Accounts'].VENDORID = ['PM Vendor Master'].VENDORID LEFT JOIN GL00100 AS ['Account Master'] ON ['Account Master'].ACTINDX = ['Vendor Accounts'].ACTINDX OR ['Account Master'].ACTINDX = ['PM Vendor Master'].PMPRCHIX LEFT JOIN GL00105 AS ['Account Index Master'] ON ['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX WHERE ['PM Vendor Master'].VENDORID = 'ADVANCED0001' GO

SQL Script to find SQL Objects containing specific text

Microsoft SQL ServerI did some work for a client recently which involved some custom SQL objects created by a consultant at their previous VAR. It appeared that there was a consistent prefix on the created objects which meant we could use a script I've previously posted to identify them all.

However, we quickly realised there were other objects which did not adhere to the naming convention we'd identified. Fortunately, I remembered writing a script to update database names in SQL views after copying live to test which identified the views to update by using the sys.sql_modules object.

Using that oncept, I came up with the below script which will search for the text specified in the variable at the top in all objects:

/*
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 @SearchString VARCHAR(100) = 'string to find' SELECT DISTINCT ['sys objects'].name AS 'Object Name' ,['sys objects'].type_desc AS 'Object Type Description' FROM sys.sql_modules AS ['sys modules'] INNER JOIN sys.objects AS ['sys objects'] ON ['sys objects'].object_id = ['sys modules'].object_id WHERE ['sys modules'].definition like '%' + @SearchString + '%'

Hands On with Microsoft Dynamics GP October 2019 Release: System Requirements

Microsoft Dynamics GPThis post is part of the Hands On with Microsoft Dynamics GP October 2019 Release series where I am going hands on with the new version of Microsoft Dynamics GP.

The October 2019 release of Microsoft Dynamics GP largely has the same system requirements as Microsoft Dynamics GP 2018 R2 with the addition of the following:

  • Windows Server 2019
  • SQL Server 2019
  • Office 2019

Click to show/hide the Hands On with Microsoft Dynamics GP October 2019 Release Series Index

SSRS requires that you use local SQL Server relational databases… error

Microsoft Dynamics GPMicrosoft Dynamics GP ships with a set of default reports which can be deployed into SQL Server Reporting Services; before doing the deploy, there is a setting in the SSRS web.config file which needs to be changed to avoid a maximum request length error.

Recently, when performing an upgrade of a clients system to Microsoft Dynamics GP 23018 R2, the deployment of the reports went fine, but we encountered a problem while testing the deployed reports:

SSRS reports error message

The client had a licenced version of SQL Server 2018 Web Edition which worked in all other respects; the SSRS service was running locally on the SQL Server which was hosting the databases for both SSRS and Microsoft Dynamics GP itself.

This wasn't something I've encountered before, but, fortunately, a colleague had. The problem is not with the Web Edition itself, but rather with the data connection deployed by Microsoft Dynamics GP for the reports. When the data connections are deployed, there is a space created preceding the Data Source name:

Connection settings in SSRS Manager

The fix is to go through all of the datab connections and remove the space after the equals sign, so that, in the example above, Data Source= IG-SQL2017-01\GP... becomes Data Source=IG-SQL2017-01\GP....

Cannot Launch SQL Server Management Studio

Microsoft SQL ServerI work on a variety of test and demo systems and recently stumbled across an oddity with SQL Server Management Studio (SSMS); when I tried to launch it using the shortcut, the splash screen would display, stay on screen for a few seconds before disappearing with SSMS never starting, but no error message appearing or showing in the logs.

If I tried to start it by typing ssms in a command prompt I received the following error:

ssms error

ssms1

Windows cannot find 'ssms'. Make sure you rtyped the name correctly, and then try again.

I did some exploring and found this article on Stackoverflow which resolved my problem.

Find the Ssms.exe.config file and erase the line <NgenBind_OptimizeNonGac enabled="1" />:

Noterpad.exe showing Ssms.exe.config