Category: SQL Server

As with virtually all headlines containing a question, the short answer is no. From the research I have done, the temp table will always be created with the collation of the SQL Server. If you want to skip to the solution, click here.

Why would you want a different collation? Well, in this case we need a different collation as there is a third party database which a customisation in Microsoft Dynamics GP is using for pricing lookups. The database was originally on a server with the same collation (SQL_Latin1_General_CP1_CI_AS) as the Dynamics GP server (and which the database shares). However, the database has now been moved to a new server with the default UK server collation (Latin1_General_CI_AS).

The problem now is that when data is being inserted or updated into this database, a collation error is produced:

Continue reading “Can I Create a Temp Table With Collation Different to the Server Collation?”

With Microsoft Dynamics GP, there are only two user accounts which can, by default, create new users or assign access to companies; these the the sa (SQL Server System Administrator) and DYNSA (Dynamics GP System Administrator).

The former account should only be used when absolutely necessary (such as when implementing Microsoft Dynamics GP or moving it to a new SQL Server Instance; there are some ISV products which also insist on the sa account when it isn’t strictly necessary from a tecHnical perspective).

The recommended way of maintaining security is to configure a normal user account with the permissions necessary to create and assign users to companies. There are a few steps to go through to assign the relevant security.

Mark Polino did a post a while ago on adding users without using the sa account, but, in this post, Mark assigned the sysadmin role to the user. While this will do the job, and in fewer steps, I prefer to lock down security so users only have the permissions required, which precludes assigning a sysadmin role. The reason for this is both best practice, but also that I have several clients who will not allow the sysadmin role to be assigned to a GP user.

The following steps cover the minimum security required for a user to be able to add new users or assign them access to companies.

Assign the user to all companies in Microsoft Dynamics GP (this is done in the User Access Setup window (Administration area page » Setup » System » User Access):

Continue reading “Create User or Assign Company Access Without Using sa”

I create a fair amount of T-SQL in the form of selects, views and stored procedures. While I try to format my code to make it easy to use, I do encounter code from other people which is not very well formatted; and if you look at the stored procedures or views which ship with Microsoft Dynamics GP, they are generally formatted with all of the code on a single line.

There are a variety of ways of formatting SQL and I’m sure there are addins for SQL Server Management Studio; however, I often work on clients servers and can’t just install anything I want.

Therefore, a web based alternative is favourite and SQLFormat is the best one I have come across so far.

To use, simple paste your code into the big box and click the Format SQL button:

You can even load a file should you wish.

I’m posting it here as a reminder to myself.

Over the last few years as we have grown the ERP Practice at Perfect Image we have taken over a number of clients from other Microsoft Dynamics GP partners, both in the UK and elsewhere. Far too many of them have had Dynamics GP installed with the incorrect collation.

According to the system requirements, Microsoft Dynamics GP is supported with two collations:

• SQL_Latin1_General_CP1_CI_AS (the recommend one as it is case insensitive).
• Latin1_General_BIN

Whenever I install SQL Server, I am careful to ensure that I pick the SQL_Latin1_General_CP1_CI_AS collation which is supported; this is important to do, as a UK language server has Latin1_General_CI_AS as the default and you need to proactively make the change. A US server has the correct collation by default.

If I’m honest, I couldn’t have told you why this was important, but it is something I have always been very careful to do. I couldn’t have told you why this was important, until today that is, when I read a guest post by Craig Verster, Senior Microsoft Dynamics GP Consultant at Microchannel Services, on the Winthrop DC blog.

The post by Craig explains why it is important to use the correct collation. Short version is that while both SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS are 1252 character collations, they handle the Þ character different which can cause incorrect data to be returned in the Reporting Service Reports; there could also be other places where this manifests.

I’d encourage everyone (especially consultants) to read and understand the post so we can stop clients having an incorrect collation installed on the SQL Server used with Microsoft Dynamics GP.

Microsoft Dynamics GP includes a number of views in the database which are used to drive some of the reporting (such as the Refreshable Excel Reports); these reports contain drill down links. I’ve never really done that much work with the views myself, but I was alerted to an issue by a partner organisation who was creating some reports from Dynamics GP integrated with data from their system.

As development was in progress, the partner was working on the clients standalone test system, but when they tested the drill downs, the data was coming from the live system. I did some exploring of the views and found that the drilldowns are coded during deployment to include the server and database:

'Account Index For Drillback' = 'dgpp://DGPB/?Db=GP&Srv=2018SQL1\GP&Cmp=TEST&Prod=0' +dbo.dgppAccountIndex(1,['Account Master'].[ACTINDX] )

This issue will affect the views not only when the databases are copied to a new system, but also when a live company is copied to a test company on the same server.

The solution was to create a script which would alter the views to the new server or database:

/*
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).
*/
DECLARE @ViewDefinition AS NVARCHAR(max)

DECLARE @OldDatabase AS VARCHAR(5) = 'TWO'
DECLARE @NewDatabase AS VARCHAR(5) = DB_NAME()

DECLARE @OldServer AS VARCHAR(50) = '2018SQL1'
DECLARE @NewServer AS VARCHAR(50) = @@ServerName

CREATE TABLE #ViewDefinitions(
	ViewDefinition NVARCHAR(MAX)
	,ROW_ID INT IDENTITY
)

INSERT INTO #ViewDefinitions
	(ViewDefinition)
--VALUES
	(SELECT
		REPLACE(
			REPLACE(['SQL Modules'].definition, 'CREATE VIEW', 'ALTER VIEW')
		,'Srv=' + @OldServer + '&Cmp=' + @OldDatabase,'Srv=' + @NewServer + '&Cmp=' + @NewDatabase)
	FROM
		sys.all_views AS ['All Views']
	JOIN
		sys.sql_modules AS ['SQL Modules']
			ON
				['SQL Modules'].object_id = ['All Views'].object_id
	and
		['SQL Modules'].definition LIKE '%Srv=' + @OldServer + '&Cmp=' + @OldDatabase + '%')

DECLARE
	cursor_Views Cursor
FOR
	SELECT
		ViewDefinition
	FROM
		#ViewDefinitions
	
	Open cursor_Views

	FETCH NEXT FROM
		cursor_Views
	INTO
		@ViewDefinition
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)
			EXEC (@ViewDefinition)
			FETCH NEXT FROM
				cursor_Views
			INTO
				@ViewDefinition
		END
	CLOSE cursor_Views
DEALLOCATE cursor_Views

DROP TABLE #ViewDefinitions
GO

The script uses the @@ServerName and DB_Name functions for the new server and database names; the two highlighted sections are the parameters for the old server and database which need to be amended.

As this script updates the views, make sure you have a good database backup before running.

Updated 24/10/2018 following feedback from David Morinello