Top Categories:
Dynamics GP
ClassicPress

Change SQL Server Analysis Services Deployment Mode from Multidimensional to Tabular

Microsoft SQL ServerI recently installed SQL Server Analysis Services (SSAS) and used the default settings during the installation. Unfortunately, this meant I installed it using a Deployment Mode of Multidimensional instead of Tabular.

I wanted to avoid uninstalling so I did some poking around and found there is a setting file which can be amended to change the Deployment Mode. The file is msmdsrv.ini which is in C:\Program Files\Microsoft SQL Server\MSAS15.GP\OLAP\Config if you have installed it into the default location:

Windows Explorer

Continue reading “Change SQL Server Analysis Services Deployment Mode from Multidimensional to Tabular”

Change All Folders to Match Current View

WindowsWindows Explorer allows you to customise a folder by changing the columns available with the changes only affecting that one folder. However, there is also a method of applying the changed view to all foldersof the same type. This means if you’re working, as I was, on a folder containing photos that you can apply any changes to the columns to all other folders which Windows sees as a photo folder. This is a fairly blunt approach, but it met the needs of what I was doing.

To apply the settings from the folder you’re in, click the View tab and select Options. When the folder options window opens, click the Apply to Folders button:

Folder Options

When prompted to make all folders of this type to match the folder you’re in, click Yes:

Question dialog

Do you want all folders of this type to match this folder's view settings?

Find SQL View in All Microsoft Dynamics GP Databases

Microsoft Dynamics GPI recently needed to find which databases in SQL Server had a specific view deployed to them. I’ve created scripts in the past to find SQL objects in all databases (table,trigger and functions), but not one for SQL views.

Some of the previous scripts looked in all databases and others were limited to only the Microsoft Dynamics GP databases; this script is one of the latter, using the SY015000 table to only search for a SQL view in the Dynamics GP databases:

/*
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 @command NVARCHAR(MAX) DECLARE @SystemDatabase VARCHAR(15) = 'D20' DECLARE @View VARCHAR(50) = 'uv_AZRCRV' CREATE TABLE #ReturnedData( DBNAME VARCHAR(15) ,VIEWNAME VARCHAR(100) ) SELECT @command = 'IF EXISTS (SELECT 1 FROM sys.databases AS dbs LEFT JOIN ' + @SystemDatabase + '..SY01500 SY ON SY.INTERID = dbs.name WHERE dbs.name = ''?'' AND (dbs.name = ''' + @SystemDatabase + ''' OR SY.INTERID IS NOT NULL)) BEGIN USE [?]; INSERT INTO #ReturnedData (dbname, VIEWNAME) (SELECT DB_NAME() AS ''DB_NAME'', o.name FROM sys.objects AS o WHERE o.type = ''V'' AND o.name LIKE ''' + @View + '%'') END' EXEC sp_MSforeachdb @command SELECT * FROM #ReturnedData DROP TABLE #ReturnedData

Integration Manager Error Importing Fixed Asset

Microsoft Dynamics GPAfter assisting a client with a server migration, an issue was reported with a fixed asset integration:

Integration Manager error log

DOC 1 ERROR: The stored procedure 'taCreateAssetID' doesn't exist.

The first thing we checked was to see if the stored procedure did exist, which it did. The next option was a suspicion of permissions. After checking in SQL I found that the service account did not have access to all of the company databases; we added the DYNGRP database role to the service account and tried the integration again and found that it now ran successfully.

Error Sending Mail Using SQL Database Mail

Microsoft SQL ServerI was recently doing some testing of a script I created for a client which used SQL Database Mail for sending emails. this was on my one of my demo environments, but I received an error when testing the script:

Error message

Microsoft Dynamics GP

Unhandled database exception: [Microsoft][SQL Server Native Client 11.0][SQL Server]The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

The solution I found was to grant permissions to public, which did work, but I’m not convinced this would be an approach to take on a clients live system:

GRANT EXECUTE on sp_send_dbmail TO public

Update EFT Transfer Method on Creditor EFT Details in Microsoft Dynamics GP

Microsoft Dynamics GPI was doing some work with a client recently where we needed to output two different formats of EFT file for the same bank account. This is possible using the EFT Transfer Method on the bank on the creditors. However, with a large number of creditors we needed a way to update the records in bulk. While it would be possible to do this with a macro, it would be fiddly and take a while.

Instead, I created a SQL script which can be run to update the Address Electronic Funds Transfer Master (SY06000) table:

/*
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 ['Address Electronic Funds Transfer Master'] SET EFTTransferMethod = 2 /* 1=Not Specified 2=Business Account 3=Corporate Account 4=Personal Account 5=Foreign Account */ FROM SY06000 AS ['Address Electronic Funds Transfer Master'] WHERE SERIES = 4 AND EFTBankType = 3 /* 2 = Ireland 3 = United Kingdom 26 = Other 1 27 = Other 2 30 = Canada 31 = United States */ GO

The script is currently set up to update only UK banks and to set the EFT Transfer Method to Business Account. The green comments show some of the other values which you can use.

If you use the script, make sure you have a good backup before.

Select Microsoft Dynamics GP GL Accounts for Import

Microsoft Dynamics GPWorking on a recent project for a client we needed to extract the chart of accounts from one Microsoft Dynamics GP company and import it into a couple of others (altering the first segment during the process. The below script was created to extract all of the chart of account data in the format required for import through Integration Manager:


SELECT
	['Account Index Master'].ACTNUMST AS 'Account Number'
	,['Account Master'].ACTDESCR AS 'Account Description'
	,CASE WHEN ['Account Master'].ACTIVE = 1 THEN 'Active' ELSE 'Inactive' END AS 'Active'
	,['Account Master'].ACCTENTR AS 'Allow Account Entry'
	,['Account Master'].ACTALIAS AS 'Account Alias'
	,['Account Category Master'].ACCATDSC AS 'Account Category Description'
	,CASE WHEN ['Account Master'].PSTNGTYP = 0 THEN 'Balance Sheet' ELSE 'Profit and Loss' END AS 'Posting Type'
	,CASE WHEN ['Account Master'].TPCLBLNC = 0 THEN 'Debit' ELSE 'Credit' END AS 'Typical Balance'
	,['Account Master'].USERDEF1 AS 'User Defined 1'
	,['Account Master'].USERDEF2 AS 'User Defined 2'
	,['Account Master'].USRDEFS1 AS 'User Defined 3'
	,['Account Master'].USRDEFS2 AS 'User Defined 4'
FROM
	GL00100 AS ['Account Master']
INNER JOIN
	GL00105 AS ['Account Index Master']
		ON
			['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX
INNER JOIN
	GL00102 AS ['Account Category Master']
		ON
			['Account Category Master'].ACCATNUM = ['Account Master'].ACCATNUM[

Assign a Microsoft SQL Server Role to a User in All Microsoft Dynamics GP Databases

Microsoft SQL ServerI did some work for a client recently which involved creating a report which could be run against any database; we therefore needed to add the user to a database role in all databases which gave access to the relevant SQL objects.

The below script generates a SQL script which can be used to alter the role to assign it to the specified user in all of the Microsoft Dynamics GP company databases.

The script assumes the user already has a server login and that the role exists in all databases:

DECLARE @DatabaseRole VARCHAR(140) = 'db_reports'
DECLARE @Username VARCHAR(140) = 'AZRCRV\iang'

SELECT 'USE [' + RTRIM(INTERID) + ']
GO
ALTER ROLE [' + @DatabaseRole + '] ADD MEMBER [' + @Username + ']
GO'
FROM
	SY01500 AS ['Company Master']
GO

Fixing a OneDrive Which Has Stopped Synchronising

OneDriveI’ve been using OneDrive for quite a long time, but recently became aware that files created or amended on one PC were not being synchronised to others although it would download files created on other machines, which was why I hadn’t immediately become aware of the issue.

I did some general poking around OneDrive and couldn’t find anything wrong so did some searching and found a discussion which led me to a support article which shows how to reset OneDrive.

I gave it a go running the following command and then needed to sign back in, but everything did then start working correctly. The command I ran was:

%localappdata%\Microsoft\OneDrive\onedrive.exe /reset

Enable Workflow Step Email Notifications in Microsoft Dynamics GP

Microsoft Dynamics GPI was doing some work for a client to build a workflow process in Microsoft Dynamics GP recently and we only got the SMTP details quite late in the process. This meant that we built quite a large workflow process without being able to enable the email notifications. Once we did have the SMTP available we needed to go back through all of the steps and enable the email notifications.

Rather than do this manually, which would have been a time consuming process, I looked at the tables and identified the table and fields which would need to be updated. This was the Workflow Step Table (WF100003) table; the below script can be used to enable the notifications, set the Message ID and enable the include Document Attachments (this was for a payables batch approval process):

/*
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 WF100003 SET EmailMessageID = '^ASSIGN PO APPROVAL' ,Workflow_Step_Send_Email = 1 ,WFIncludeDocumentAttach = 1 WHERE Workflow_Step_Send_Email = 0

As always with scripts, make sure you have a good backup and test before using on a live system.