I work on a variety of test and demo systems and recently stumbled across an oddity with SQL Server Management Studio (SSMS); when I tried to launch it using the shortcut, the splash screen would display, stay on screen for a few seconds before disappearing with SSMS never starting, but no error message appearing or showing in the logs.
If I tried to start it by typing
ssms in a command prompt I received the following error:
Windows cannot find 'ssms'. Make sure you rtyped the name correctly, and then try again.
I did some exploring and found this article on Stackoverflow which resolved my problem.
Find the Ssms.exe.config file and erase the line
<NgenBind_OptimizeNonGac enabled="1" />:
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 ( ):
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.
In this short series of posts, I am going to be covering the installation of Microsoft SQL Server 2017; the installation process for this version is somewhat different to previous ones, in that SQL Server Management Studio and SQL Server Reporting Services have been separated out from the main installer.
The series index can be found here. I am not a SQL expert and this series of posts is intended only for use on demo or test environment and not production ones.
The next component to be installed, is SQL Server Management Studio (SSMS). This was the first of the components which Microsoft removed from the main installer. However, to install it, we can launch the download page from the Setup utility.
Once launched, click Installation and then select Install SQL Server Management Tools:
Continue reading “How To Install Microsoft SQL Server 2017: Install SQL Server Management Studio”
With a new version of Microsoft Dynamics GP about to be released, it’s time to refresh my test environment; as well as the upcoming release of Microsoft Dynamics GP 2018, Microsoft SQL Server 2017 is also now available.
In this short series of posts, I am going to cover the installation of the SQL Server components needed for an installation of a demo/test Microsoft Dynamics GP 2018 (not including SSAS or SSIS).
I am not a SQL expert and this series of posts is intended only for use on demo or test environments and not production ones.
When you execute a query in Microsoft SQL Server Management Studio, you can chooe to output the result to text, but this is limited to 256 characters which can not always be enough (my next planned post will have an example of this).
The setting for this can be changed in Tools >> Options.
Change the field Maximum number of characters displayed in each column from 256 to 8000:
Click OK to save the change; you will need to click the New Query button for the change to take effect.
I was doing some work with a custom table yesterday and at one point needed to make a change to the table layout via SQL Server Management Studio. However, when I tried, I got the below error message;
Continue reading “SQL Server Management Studio Not Allowing Table Changes That Require A Table To Be Dropped”