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”

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

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

Create Grant Statement for All Custom Views in Microsoft SQL

Microsoft SQL ServerOn a recent project, I needed to create a new database role for some new SQL views which had been created.

Creating the role is easy enough, but to make sure all the relevant views (and there were quite a few) looked like a bigger job until I realised I could select from sys.views and generate the grant script at the same time.

In the below script, the first highlighted section is the database role and the second the start of the views to have select permissions granted (I always use a uv_ prefix for views):

/*
Created by Ian Grieve of azurecurve|Ramblings of an IT Professional (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 'GRANT SELECT ON ' + name + ' TO urpt_SalesViews' + CHAR(10) + 'GO' FROM sys.views WHERE name LIKE 'uv_[/highlight%'

The output of the above should be returned to text and can then be copied and pasted into a new script window.

SQL Snippets: SELECT INTO

Microsoft SQL ServerThis post is part of the series on SQL Snippets.

I occasionally need to make a backup of a SQL table and all rows within; while you can create a table and insert the content, this is the long way of doing. Instead you can use SELECT INTO to both create a table with the correct columns and also to copy the data.

In the below example I am creating a new table suffixed with the date; the first highlighted section is the new destination table to be created and the second the source table:

SELECT *
INTO IV00101_20210910
FROM IV00101

Microsoft Dynamics GP Reporting Services Reports Not Supported on SQL Express

Microsoft Dynamics GPUsually when installing or upgrading Microsoft Dynamics GP for clients they will use SQL Server Standard and sometimes Enterprise; very rarely, I’ll encounter a mall client who is using or wants to use SQL Server Express.

SQL Server Express is a free version of SQL Server, but which has some limitations. SQL Server Reporting Services (SSRS) is not one of those limitations; you can install SSRS nd it will work happily with SQL Server Express as long as both are installed on the same server.

However, Microsoft Dynamics GP will not deploy reports to SSRS when it is being used with SQL Server Express. if you try you get this error message:

SSRS deployment error

Microsoft Dynamics GP

You do not have security access to the location where you want the reports to be deployed.

You will also be prompted to supply a username and password, but these will never be accepted. The Microsoft Dynamics GP System Requirements say that Dynamics GP will work with Standard, Enterprise and Express editions of SQL Server, but does not mention the limitation of SSRS and SQL Express.

There are some forums posts and such mentioning the restriction, but thought it worth a post as a general reminder.

SQL Snippets: Create and Use Database Role to Restrict Access

Microsoft SQL ServerThis post is part of the series on SQL Snippets.

I create a lot of SQL objects such as views, tables, stored procedures and so on. If they will be used from within Microsoft Dynamics then they can simply be assigned to the DYNSA role, but if they are for use in custom reporting or integrations then separate security will be required.

Usually for reporting, and especially for integrations, security will be as locked down as possible to minimise any possible attack vector. This can be done in SQL through a security role with only limited security granted to the necessary users.

The first step is to create the role itself; I always do this using a role with a name prefixed with urpt_ so that it can easily be identified as a custom user role:

-- creatr security role on database
CREATE ROLE [urpt_Role] AUTHORIZATION [dbo]
GO

Then we grant the relevant permissions to the new role. For a report this would mean only granting select permissions on the relevant views and tables; for an integration there may be inserts and updates granted.

-- grant permission to role on object
GRANT SELECT ON [dbo].[uv_SQLView] TO [urpt_Role]
GRANT SELECT ON [dbo].[GL20000] TO [urpt_Role]
GRANT SELECT ON [dbo].[GL30000] TO [urpt_Role]
GO

For a report, you may need to create a database user for the user or AD group:

-- create user on database
CREATE USER [DOMAIN\user]
GO

The final step is to assign the role to the relevant users or AD groups:

-- assign role to user on database
ALTER ROLE [urpt_Role] ADD MEMBER [DOMAIN\user]
GO

SQL Snippets: Manage Data Containing an Apostrophe

Microsoft SQL ServerThis post is part of the series on SQL Snippets.

I was doing some work with a client recently and they had an issue with some data in SQL Server which they needed change, but weren’t able to do it through the application.

The issue was that they had some names in a column marked as a key field which contained an apostrophe and it was causing them problems; the data should not have had an apostrophe, but they a user had managed to do it and they wanted to do an update to remove it.

There SQL was a little more limited than mine so they asked for assistance. I created the following SQL as an example for them on how data with apostrophe in can be managed.

The example shows how to insert data containing an apostrophe as well as two ways of changing data; one was doing it globally an the other just changing a specific record.

The key to this is that in Microsoft SQL Server you can use one apostrophe to escape another.

-- CREATE TEMP TABLE
CREATE TABLE #Temp(
	USERNAME VARCHAR(15)
)
GO

-- INSERT DATA INTO TEMP TABLE
INSERT INTO #Temp (USERNAME) VALUES ('AJ''ones')
GO

-- SELECT DATA TO CHECK
SELECT * FROM #Temp
GO

-- RUN ONE OF THE TWO UPDATE STATEMENTS
-- remove single quote FROM one record
UPDATE #Temp SET USERNAME = REPLACE(USERNAME, '''', '') WHERE USERNAME = 'AJ''ones'

-- remove single quote FROM all records
UPDATE #Temp SET USERNAME = REPLACE(USERNAME, '''', '') WHERE USERNAME LIKE '%''%'
GO

-- SELECT DATA TO CHECK
SELECT * FROM #Temp
GO

-- DROP TEMP TABLE
DROP TABLE #Temp
GO

SQL Snippets: Series Index

Microsoft SQL ServerThis post is the series index for the series on SQL Snippets.

This series will build up through time as I post snippets of SQL Script; I’ll also go back and update some of the previously posted SQL snippets to bring them together into one index.

SQL Snippets
Manage Data Containing an Apostrophe
Create and Use Database Role to Restrict Access
SELECT INTO

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 an IT Professional (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)