SQL View To Select User, Company and Security Role Assignment

Microsoft Dynamics GPWe had a user recently who wanted to quickly see which users had access to which companies and the Security Roles which had been granted. While Dynamics GP does have a number of security reports, they are the standard ones which, while readable in the usual screen or printer output, cannot easily be exported to Excel. However, the client is on Microsoft Dynamics GP 2013 R2 and therefore has access to SmartList Designer.

While I could have done this entirely as a SmartList Designer report, I already had the majority of the SQL needed to generate this as a SQL View which SmartList Designer can access:

CREATE VIEW uv_PI_UserAccessAndGrantedSecurityRoles AS
SELECT
	['User Master'].USERID AS 'User ID'
	,['User Master'].USERNAME AS 'Username'
	,['User Master'].USRCLASS AS 'User Class'
	,ISNULL(['Class Master'].DSCRIPTN, '') AS 'User Class Description'
	,ISNULL(['Company Master'].INTERID, '') AS 'Intercompany ID'
	,ISNULL(['Company Master'].CMPNYNAM, '') AS 'Company Name'
	,ISNULL(['Security Assignment User Role'].SECURITYROLEID, '') AS 'Security Role ID'
	,ISNULL(['Security Roles Master'].SECURITYROLENAME, '') AS 'Secuity Role Name'
FROM
	SY01400 AS ['User Master']
LEFT JOIN
	SY40400 AS ['Class Master']
		ON ['Class Master'].USRCLASS = ['User Master'].USRCLASS
LEFT JOIN
	SY60100 AS ['User-Company Access']
		ON ['User-Company Access'].USERID = ['User Master'].USERID
LEFT JOIN
	SY10500 AS ['Security Assignment User Role']
		ON ['Security Assignment User Role'].CMPANYID = ['User-Company Access'].CMPANYID
			AND ['Security Assignment User Role'].USERID = ['User-Company Access'].USERID
LEFT JOIN
	SY09100 AS ['Security Roles Master']
		ON ['Security Roles Master'].SECURITYROLEID = ['Security Assignment User Role'].SECURITYROLEID
LEFT JOIN
	SY01500 AS ['Company Master']
		ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID
GO

Of course, the other reason I used the SQL rather than recreating entirely in SmartList Designer is that I can use this SQL in future, but a SmartList Designer is only usable on the system on which it is created as there is no import/export functionality.

Scripts to Reset System and Budget Passwords

Microsoft Dynamics GPIf a user forgets the password set against a Budget it cannot be amended through Microsoft Dynamics GP, but can be removed through SQL Server; the same stands true for the System Password as well.

The first script in this post, removes the password of a budget; this SQL is not mine, but was posted by Leslie Vail in this post on the Dynamics Community forum.

To run this script change the highlighted section to the name of the budget which needs the password reset:

UPDATE
	GL00200
SET
	BUDPWRD = 0x00202020202020202020202020202020
WHERE
	BUDGETID = 'budget id'
GO

A very similar script can be used to reset the System Password. If you are using a Named System Database, change the highlighted DYNAMICS text to the name of your System Database:

USE DYNAMICS
GO
UPDATE
	SY02400
SET
	Password = 0X00202020202020202020202020202020
GO

SQL Script To Add A Linked Server

Microsoft Dynamics GPAfter doing a little work linking databases together for a report, I ended up creating a script to be used within a stored procedure to quickly and easily re-add the linked server (which is required after a reboot of the SQL Server.

To run the script, change the three highlighted fields (server-name, username and password) and click Execute in SQL Server Management Studio:

DECLARE @Server AS VARCHAR(50)
DECLARE @Username AS VARCHAR(50)
DECLARE @Password AS VARCHAR(50)

SET @Server = 'server-name'
SET @Username = 'username'
SET @Password = 'password'

CREATE TABLE #linkedservers
	(SRV_NAME VARCHAR(50)
	,PROV_NAME VARCHAR(50)
	,SRV_PROD VARCHAR(50)
	,SRV_DATA VARCHAR(50)
	,SRV_STRING VARCHAR(50
	,SRV_LOC VARCHAR(50)
	,SRV_CAT VARCHAR(50))

INSERT INTO #linkedservers
EXEC sp_linkedservers

IF (SELECT COUNT(*) FROM #linkedservers WHERE SRV_NAME = @Server) < 1
	EXEC sp_addlinkedserver @Server, 'SQL Server'

DROP TABLE #linkedservers

EXEC sp_addlinkedsrvlogin @Server, 'false', NULL, @Username, @Password

SQL Script To Insert Creditor Bank Details From CSV

Microsoft Dynamics GPIntegration Manager is a very good tool, but it doesn’t allow the import of all record types into Microsoft Dynamics GP. One of the main types of record I need to import when implementing a new client is the Creditor (Vendor to the American readers) EFT details.

While the EFT data is stored in one table and can therefore be imported using Table Import, I find that configuring Table Import for each client is a bit of a annoyance, so I developed a SQL Script a while ago which I figured I might as well post here for easy access.

As always when using SQL to update tables in Microsoft Dynamics GP, make sure you have a good backup of the database before you begin and check the imported data afterwards. Read on for the script…

Continue reading

Script To Copy Segments To All Companies

Microsoft Dynamics GPFollowing the last post I did, on copying segments to a new company, I did some thinking and realised that with a little more work on the script I could make it even more useful when setting up clients with multiple companies and the same chart of account structure.

I have added a cursor to the script which selects all company databases from the DYNAMICS System Database (change the highlighted DYNAMICS if you’re using a named system database) and then loops though them doing the insert from the SourceDatabase.

As before, the script checks to make sure the Segments don’t already exist in the destination before doing the insert.

DECLARE @SourceCompany AS VARCHAR(5)
DECLARE @DestinationCompany AS VARCHAR(5)
DECLARE @SQLStatement AS VARCHAR(2000)

SET @SourceCompany = 'TWO'

DECLARE
	cursor_InterID Cursor 
FOR 
	SELECT
		INTERID
	FROM
		DYNAMICS..SY01500
	INNER JOIN
		master..sysdatabases
	ON
		name = INTERID
	WHERE
		INTERID <> @SourceCompany
	
	Open cursor_InterID

	FETCH NEXT FROM
		cursor_InterID
	INTO
		@DestinationCompany
	While (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)
			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..GL40200
				(SGMTNUMB
				,SGMNTID
				,DSCRIPTN
				,SEGCOUNT
				,NOTEINDX)
				
				(SELECT
					SGMTNUMB
					,SGMNTID
					,Left(DSCRIPTN, 30)
					,0
					,0
				FROM
					' + @SourceCompany + '..GL40200 AS SD
				WHERE (SELECT COUNT(GL.SGMNTID) FROM ' + @DestinationCompany + '..GL40200 GL
					WHERE GL.SGMTNUMB = SD.SGMTNUMB AND GL.SGMNTID = SD.SGMNTID) = 0)'

			EXEC (@SQLStatement)
			FETCH NEXT FROM
				cursor_InterID
			INTO
				@DestinationCompany
		END
	CLOSE cursor_InterID
DEALLOCATE cursor_InterID

If you run this script, please be careful and ensure you have a good backup before running the script (as I don’t supply a warranty with any script I post here; that said I am happy to talk to people if they have questions or would like the script extending).

Script To Copy Segments To A New Company

Microsoft Dynamics GPI have been doing some work with a client recently where we were creating a number of new companies which were to share the same chart of accounts (with only the first segment representing the company being different).

So we planned to use Integration Manager to to integrate a file containing the new chart of accounts. However, before we could load the accounts themselves we needed to get the Segments loaded.

Tis could be done by creating a SmartList in SmartList Designer to get the Segment Number, Segment ID and Segment Description and then use File Import to load the segments into the new company; or I could knock together a quick SQL script to do the job.

Obviously, as I am writing this post, I opted to go the route of writing an SQL Script. The highlighted pieces at the top are the Source and Destination Company databases; change these to the relevant databases.

The script also checks to make sure the Segments don’t already exist in the destination before doing the insert.

DECLARE @SourceCompany AS VARCHAR(5)
DECLARE @DestinationCompany AS VARCHAR(5)
DECLARE @SQLStatement AS VARCHAR(2000)

SET @SourceCompany = 'TWO'
SET @DestinationCompany = 'TWOA'

SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..GL40200
	(SGMTNUMB
	,SGMNTID
	,DSCRIPTN
	,SEGCOUNT
	,NOTEINDX)
	
	(SELECT
		SGMTNUMB
		,SGMNTID
		,Left(DSCRIPTN, 30)
		,0
		,0
	FROM
		' + @SourceCompany + '..GL40200 AS SM
	WHERE (SELECT COUNT(GL.SGMNTID) FROM ' + @DestinationCompany + '..GL40200 GL
		WHERE GL.SGMTNUMB = SM.SGMTNUMB AND GL.SGMNTID = SM.SGMNTID) = 0)'

EXEC (@SQLStatement)
GO

If you run this script, please be careful and ensure you have a good backup before running the script (as I don’t supply a warranty with any script I post here; that said I am happy to talk to people if they have questions or would like the script extending).

SQL Query To Determine Quantity To Order

I recently had cause to knock together a fairly simple SQL script to determine the quantity to order based on Sales transactions in the system taking into account the Order Point Quantity and Quantity To Order for the Item/Site combination in the Inventory module:

SELECT
   SOP10200.LOCNCODE AS 'Site'
   ,SOP10200.ITEMNMBR AS 'Item Number'
   ,SUM(SOP10200.QUANTITY) AS 'Quantity Required'
   ,IV00102.ORDRUPTOLVL - SUM(SOP10200.QTYTORDR) AS 'Quantity To Order'
FROM
   SOP10200 (NOLOCK)
LEFT OUTER JOIN
   IV00102 (NOLOCK)
      ON IV00102.ITEMNMBR = SOP10200.ITEMNMBR AND IV00102.LOCNCODE = SOP10200.LOCNCODE
WHERE
   SOP10200.QTYTORDR <= ORDRPNTQTY AND SOP10200.QTYTORDR <> 0
GROUP BY
   SOP10200.ITEMNMBR, SOP10200.LOCNCODE, IV00102.ORDRUPTOLVL

Extract ABR Transactions For Import As Statement

this is a simple script, but I’ve written it about four times now so I figured I’d post it so I can find it easily next time I lose my local copy.

Perfect Image are resellers of the Advanced Bank Reconciliation module from Nolan Business Solutions (along with the other add-ons they’ve written for Dynamics GP) and I often need to demo this replacement for the standard Bank Rec module.

One item I typically show is the auto-Propose function which matches transactions against statement lines in the Reconcile Bank Transactions window (Transactions >> Financial >> Advanced Bank Reconciliation >> Reconcile Bank Transactions).

To do this I need to be able to import statement lines which match the transactions in Dynamics GP; the easiest way of doing this is to extract the transactions.

This can be done with a very simple SQL script:

SELECT
   ORPSTDDT
   ,ORDOCNUM
   ,TRXAMNT
   ,SOURCDOC

FROM
   NCABR012

Once the data has been extracted it can be imported during the demo using the standard ABR Import Statement routine.

SQL Script To Locate Columns With Different Collation

A while ago I posted about a problem with a collation conflict on a couple of columns in the Tax table. It seems I posted about how to fix the problem, but it seems I didn’t post how I found the problem columns.

I did this with a fairly simple SQL script:

DECLARE @Collation SYSNAME SET @Collation = 'SQL_Latin1_General_CP1_CI_AS'

SELECT
   TABLE_NAME AS 'Table'
   ,COLUMN_NAME AS 'Column'
   ,DATA_TYPE AS 'Data Type'
   ,COLLATION_NAME AS 'Collation Name'
FROM
   INFORMATION_SCHEMA.COLUMNS
WHERE
   DATA_TYPE IN ('varchar','char','nvarchar','nchar','text','ntext')
AND
   COLLATION_NAME <> @Collation

Deactivate All Reports (Except PM EFT Payment Register) In All Companies

Microsoft Dynamics GP includes a large number of reports which will automatically print off when a transaction or batch is posted; and some postings produce many different reports.

When I implement a new client I typically leave them on during training with an instruction to identify which ones they want to use after go-live as I will switch off any I’m not told to leave on.

I used to do it the other way and tell people to let me know which ones they wanted switched off; however, this lead to all reports being left on and then it being mentioned a year or so down the line that they get all these reports printing that they don’t want.

So now, all get switched off unless I am specifically told to leave them on. As I’ve dealt with clients with many companies (I think the largest is 180 companies) this is not something I want to do manually.

Fortunately, the settings for whether the reports should print is stored in SQL table which means a SQL script can be written to switch them off in bulk.

Continue reading