SQL Snippet: Return Comma Delimited String

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 first example, shows how to return a comma delimited string of vlues from a select instead of the usual multiline recordset:

/*
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 @DOCDATE DATETIME = '2017-04-12'
SELECT (STUFF((
        SELECT
			', ' + RTRIM(CNTRLNUM)
        FROM
			PM00400
        WHERE
			DOCDATE = @DOCDATE
        ORDER BY
			CNTRLNUM
        FOR XML PATH('')
        ), 1, 2, '')
    ) AS ReturnString

The example above, is created against the Microsoft Dynamics GP sample database and returns a comma delimited list of vouchers for a particular date.

After Removing Fixed Asset Management Tables Add Entry To DB_Upgrade

Microsoft Dynamics GPIn the previous post, I posted a SQL script which can be used to delete all tables from a MIcrosoft Dynamics GP implementation for a specified product. That script will generate you the drop and delete commands required to remove a product, but may, for some products, still cause an error if you try to add the product again. I don’t have a screenshot of the error, but it is one where GP Utilities complains the module is too old to upgrade.

This is the case for Fixed Asset Management (FAM); I’ve also had the same problem when adding FAM for the first time. The below script can be used to add an entry to the DB_Upgrade table which will resolve the error.

The highlighted parameters, at the top of the script, will need to be defined; the versions should be the same as the products installed in Dynamics GP; I would avoid setting them to the same as the Dynamics GP product id as this is often different to the other features.

For fixed assets on Microsoft Dynamics GP 2016 R1, the major version and build number are set to 16 and 389 respectively.

If you have already tried to run GP Utilities, a row will have been inserted into DB_Upgrade table which will need to be upgraded.

But worry not, the script will update an existing row or insert a new one as appropriate.

Before running the script make sure that the update statement will not overwrite existing data with an invaoid value.

/*
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 D16R1
GO

DECLARE @PRODID INT = 309
DECLARE @db_verMajor INT = 16
DECLARE @db_verBuild INT = 389

UPDATE DB_Upgrade SET db_verMajor = @db_verMajor, db_verBuild = @db_verBuild,db_verOldMajor = @db_verMajor, db_verOldBuild = @db_verBuild WHERE db_name = DB_NAME() AND PRODID = @PRODID

IF (SELECT COUNT(*) FROM DB_Upgrade WHERE db_name = DB_NAME() AND PRODID = @PRODID) = 0
	INSERT INTO DB_Upgrade (db_name,PRODID,db_verMajor,db_verMinor,db_verBuild,db_verOldMajor,db_verOldMinor,db_verOldBuild,db_status) VALUES (DB_NAME(),@PRODID,@db_verMajor,0,@db_verBuild,@db_verMajor,0,@db_verBuild,0)
GO

SQL Script To Bulk Alter Users With Logins

Microsoft Dynamics GPBack in July 2013 I did a post where I looked at a problem copying live to test. The basic issue was that the Microsoft Dynamics GP user is also a login (at the SQL Server level) and a user (at the SQL Server database level) and when a database is copied from the live server to the test server (or from the current live top the new live) you can run a script to transfer across the logins, but the users come across with the database and will have different SIDs (Security IDs).

You can use the ALTER USER command in SQL to re-link the login with the user, but this is one statement per user per database. The old post showed how to do this, but this quickly becomes a pain when there are more than a handful of users.

As Perfect Image has grown we have clients with more and more users and/or company databases. Our largest client has over 250 users in their Dynamics GP installation while another has fewer users, but well over 100 companies. Both of these can make copying live to test problematic, especially when only a company database might be copied over rather than the whole system.

I needed to automate the process of altering the login to match the user; the below script is the result of this need. Continue reading “SQL Script To Bulk Alter Users With Logins”