How To Install Microsoft SQL Server 2016: Installing SQL Server Management Studio

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThis is a short series of posts on how to install Microsoft SQL Server 2016; the series index can be found here.

In previous versions of Microsoft SQL Server, SQL Server Management Studio (SSMS) was always part of the standard install, but it seems this has changed with SQL Server 2016.

Instead SSMS is now available as a separate download. I’d recommend downloading the GA rather than any release candidate which might be available:

Download SQL Server Management Studio (SSMS)

Continue reading → How To Install Microsoft SQL Server 2016: Installing SQL Server Management Studio

● Categories: Microsoft, SQL Server, SQL Server 2016, SSMS ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

How To Install Microsoft SQL Server 2016: SSIS Configuration For Named Instances

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThis is a short series of posts on how to install Microsoft SQL Server 2016; the series index can be found here.

In the last post, I installed SSIS, but if you are using a named Instance of SQL Server, there is a configuration step required.

To make the change, there is a file called MsDtsSrvr.ini which, for SQL Server 2016, is located in C:\Program Files\Microsoft SQL Server\130\DTS\Binn. The ServerName needs to be changed to include the full SQL Server Instance Name (as highlighted below):

MsDtsSrvr.ini file in Notepad

Once the full SQL Server Instance Name was added and the file saved, SSIS is ready to use.

Click to show/hide the How To Install Microsoft SQL Server 2016 Series Index

● Categories: Microsoft, SQL Server, SQL Server 2016, SSIS 2016 ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

How To Install Microsoft SQL Server 2016: Installing SQL Server Integration Services

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThis is a short series of posts on how to install Microsoft SQL Server 2016; the series index can be found here.

When I started the installation of the Analysis Cubes for Microsoft Dynamics GP as part of my ‘Hands On with GP 2016 R2 series, I knew I needed SQL Server Analysis Services (clue was in the name), but I didn’t initially realise that I was going to need SQL Server Integration Services (SSIS) although in retrospect it should have been obvious too.

The reason SSIS is needed, is that the Analysis Cubes in SSAS are populated by integration jobs from SSIS (scheduled using SQL Server Agent).

So I had to come back and install SSIS. You do this by launching the SQL Server setup utility and, under Installation click on New SQL Server stand-alone installation or add features to an existing installation:

SQL Server Installation Center

Continue reading → How To Install Microsoft SQL Server 2016: Installing SQL Server Integration Services

● Categories: Microsoft, SQL Server, SQL Server 2016, SSIS 2016 ● Tags: , , , , , , , , , , ,  ● Permalink ● Shortlink ●

How To Install Microsoft SQL Server 2016: Installing SQL Server Analysis Services

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThis is a short series of posts on how to install Microsoft SQL Server 2016; the series index can be found here.

I typically don’t install SQL Server Analysis Services (SSAS), but decided to this time as I was installing all of the Microsoft Dynamics GP additional products for my Hands On with Microsoft Dynamics GP 2016 R2 series.

To install SSAS, launch the SQL Server setup utility and click on Installation and then on New SQL Server stand-alone installation or add features to an existing installation:

SQL Server Installation Center

Continue reading → How To Install Microsoft SQL Server 2016: Installing SQL Server Analysis Services

● Categories: Dynamics, GP, Microsoft, SQL Server, SQL Server 2016, SSAS 2016 ● Tags: , , , , , , , , , , ,  ● Permalink ● Shortlink ●

How To Install Microsoft SQL Server 2016: Configuring SQL Server Reporting Services

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThis is a short series of posts on how to install Microsoft SQL Server 2016; the series index can be found here.

In the last post, I installed the SQL Server Database Engine and the Reporting Services, but I did not configure Reporting Services. I mentioned that I have had problems before when doing this, so always do it separately.

To configure SQL Server Reporting Services (SSRS), launch Reporting Services Configuration Manager from the Windows Start menu.

Select the Report Server Instance to connect to and click Connect:

Reporting Services Configuration Connection

Continue reading → How To Install Microsoft SQL Server 2016: Configuring SQL Server Reporting Services

● Categories: Dynamics, GP, Microsoft, SQL Server, SQL Server 2016, SSRS 2016 ● Tags: , , , , , , , , , , ,  ● Permalink ● Shortlink ●

How To Install Microsoft SQL Server 2016: Installing SQL Server Database Engine

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThis is a short series of posts on how to install Microsoft SQL Server 2016; the series index can be found here.

In this post, I am going to step through the installation of the Microsoft SQL Server 2016 Database Engine. As most of my work is with Microsoft Dynamics GP, there will be a little focus on the installation required specifically for Dynamics GP, but the basic install is the same regardless of whether it is for Dynamics GP or not.

Launch the SQL Server setup utility (setup.exe), select Installation and click on New SQL Server stand-alone installation or add features to an existing installation:

SQL Server Installation Center

Continue reading → How To Install Microsoft SQL Server 2016: Installing SQL Server Database Engine

● Categories: Dynamics, GP, Microsoft, SQL Server, SQL Server 2016, SSRS 2016 ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

How To Install Microsoft SQL Server 2016: Series Index

● Ian Grieve ●  ● 13 Comments   ● 

Microsoft Dynamics GPA new version of SQL Server came out last year, and I’ve been creating some new demo machines for use at work. It has become habit to screenshot everything I install or configure, so I decided I might as well post a small series on how to install Microsoft SQL Server 2016 and it’s component parts.

The posts will be made over the coming days and show in the series index, below:

How To Install Microsoft SQL Server 2016
Installing SQL Server Database Engine
Configuring SQL Server Reporting Services
Installing SQL Server Analysis Services
Installing SQL Server Integration Services
SSIS Configuration For Named Instances
Installing SQL Server Management Studio

The installation of SQL Server has been broken down into different posts, as I don’t install all components all of the time, but only install the ones which are required at the time.

● Categories: Microsoft, SQL Server, SQL Server 2016, SSAS 2016, SSIS 2016, SSRS 2016 ● Tags: , , , , , , , , , ,  ● Permalink ● Shortlink ●

Workflow 2.0 Tasks Not Escalating

● Ian Grieve ●  ● 2 Comments   ● 

Microsoft Dynamics GPI probably spent longer pondering over this one than I should have done.

We created a new Payables Transaction approval workflow for a client last week and during UAT they reported that tasks were not escalating when they went overdue.

I verified the workflow and did some testing and was able to confirm that tasks were not escalating correctly.

This was not something I had seen before so I spent a little time doing other things to divert my mind before the realisation struck that a scheduled process has to be checking for tasks to escalate. So off to SQL Server Agent I went.

Or at least tried to. The client does not have SQL Server Agent installed; rather they use an alternate third party tool (I don’t know why).

We passed details of the jobs through to them which are usually created in SQL Server Agent (Scan For Overdue Workflow Tasks For All Companies and Scan For Invalid AD Users and Expired Delegations For All Companies) so that they could create them in the third party tool.

The additional thought which occurred is that Dynamics GP is supported on Microsoft SQL Server Express which does not contain SQL Server Agent, so users on this SQL Server platform cannot use escalations in Workflow 2.0 (unless they use a third party equivalent of SQL Server Agent).

● Categories: Dynamics, GP, Microsoft, Workflow ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

Change SQL Query Results Length

● Ian Grieve ●  ● 5 Comments   ● 

Microsoft Dynamics GPWhen 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.

● Categories: Microsoft, SQL Server ● Tags: , , ,  ● Permalink ● Shortlink ●

Find SQL Trigger In All Databases

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPIn the last post, I posted a SQL script which could be used to find a column in the database. This post has a SQL script which can be used to locate triggers in all databases.

Change the highlighted section to the name of the trigger you’re looking for and run the script.

This is not something I am going to do manually, so I wrote a script which would find all tables containing the specified column (change the highlighted section):

/*
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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
DECLARE @command nvarchar(max)
DECLARE @Trigger VARCHAR(50) = 'utr_'

SELECT @command = 'IF EXISTS ( SELECT 1 FROM sys.databases WHERE name = ''?'')   
					BEGIN
						USE [?];
						DECLARE @stmt nvarchar(max) DECLARE @n char(1) SET @n = char(10)
						SELECT DB_NAME() AS ''DB_NAME'',t.name,t.is_disabled FROM sys.triggers AS t 
						INNER JOIN sys.objects AS o ON o.object_id = t.object_id WHERE o.name LIKE ''' + @Trigger + '%''
					END'

EXEC sp_MSforeachdb @command
● Categories: Microsoft, SQL Server ● Tags: , , , ,  ● Permalink ● Shortlink ●