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

Microsoft SQL ServerThis 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”

Microsoft Dynamics GP January Hotfix Released

Microsoft Dynamics GPI don’t usually post about releases, except for major version releases. However, the January Hotfix release caught my eye with a few of the included features in the 2016 R2 one.

As well as having the US Payroll updates there are updates to the following which would be of benefit to clients in the UK:

  • VAT Daybook

    • VAT Daybook Summary reports may not show correct data in boxes 1, 6 and 7 if you voided a transaction.
    • VAT detail report may show incorrect figures if you have 2 or more tax codes on a transaction.
  • General Ledger

    • The Excel copy and paste function may not calculate the credit/debit column correctly when using an allocation account.
  • Fixed Asset Management

    • Tax values may not update properly if a user marks to create multiple fixed assets in the Fixed Asset Purchase Order additional information window.
  • Bank Reconciliation

    • You may experience performance issues with the Checkbook Register Inquiry window after you install GP 2016 R2.
  • Project Accounting

    • Ability to change line distributions for time and material projects in revenue recognition.
    • Fee Accounts not available in line distributions button.
    • Purchase Receiving’s not reflecting WIP account edits from Purchase Order for non-inventory items.
  • System Manager

    • Fixed issues with Uncollated printing.

The hotfixes for Dynamics GP 2013 R2 and 2015 R2 only contain US Payroll fixes.

The post from the Dynamics GP Support and Services blog contains the download links.

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

Microsoft SQL ServerThis 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”

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

Microsoft SQL ServerThis 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”

How To Install Microsoft SQL Server 2016: Series Index

Microsoft SQL ServerA 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.

SQL Script To Log Into Each Company

Microsoft Dynamics GPI have a few of clients who have a large number of companies. This generally isn’t a problem as Microsoft Dynamics GP supports this very well.

However, after doing an upgrade of a system which has the VAT Daybook module installed, you need to log into each and every database using the sa account.

Doesn’t sound so bad on the surface, but when you have two hundred companies, this soon adds up to a substantial length of time.

I did a little looking around a while ago for ways to automate this process and found a post by Aaron Berquist from 2011 where he had done exactly this.

However, when we tried his script we got a few errors, so I made a few changes and improvements; my script automatically adds OK button clicks for Test, Historic and Fabrikam sample company databases.

The Test and Historic databases are identified by having or at the end of their names; this is what Dynamics GP uses to display the message.

I removed the temporary table from Aaron’s script as well; when my version of the script is run, output the results to text (make sure you have increased the query results length).

You can copy and paste the output into a Notepad document which can be saved with a .mac extension.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
Based on idea and code from http://www.aaronberquist.com/2011/07/use-dynamic-sql-to-generate-a-dynamics-gp-login-macro/
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	'# DEXVERSION=16.00.0033.000 2 2
	  CommandExec dictionary ''default''  form ''Command_System'' command ''Switch Company'' 
		NewActiveWin dictionary ''default''  form ''Switch Company'' window ''Switch Company'' 
		  ClickHit field ''(L) Company Names'' item ' + CONVERT(VARCHAR(3), ROW_NUMBER() OVER (ORDER BY CMPANYID)) + '  # ' + LTRIM(RTRIM(CMPNYNAM)) + ' 
		  MoveTo field ''OK Button'' 
		  ClickHit field ''OK Button''
		NewActiveWin dictionary ''default''  form sheLL window sheLL 
		NewActiveWin dictionary ''default''  form sheLL window sheLL ' +
		CASE WHEN RTRIM(CMPNYNAM) LIKE '%<TEST>' THEN
			'
			  # This company is set up for testing only. Do not use this company when processing live data.
			NewActiveWin dictionary ''default''  form ''SY_Error_Message'' window ''SY_Error_Message'' 
			  ClickHit field ''OK Button'' 
			NewActiveWin dictionary ''default''  form sheLL window sheLL '
		WHEN RTRIM(CMPNYNAM) LIKE '%<HISTORICAL>' THEN
			'
			  # This company is used for storing historical data only. Do not use this company when processing current-year data.
			NewActiveWin dictionary ''default''  form ''SY_Error_Message'' window ''SY_Error_Message'' 
			  ClickHit field ''OK Button'' 
			NewActiveWin dictionary ''default''  form sheLL window sheLL '
		WHEN CMPANYID = -1 THEN
			'
			  # You have chosen to use the sample company, which provides data that you can use to practice procedures or learn more about the product. When you use this sample company, the date is automatically set to April 12, 2017.
			NewActiveWin dictionary ''default''  form DiaLog window DiaLog 
			  ClickHit field OK 
			NewActiveWin dictionary ''default''  form sheLL window sheLL '
		ELSE
			''
		END
FROM
	DYNAMICS..SY01500
ORDER BY
	CMPNYNAM

Upgrading to Microsoft Dynamics GP 2016 R2: Adding Missing Security Roles

Microsoft Dynamics GPWith Microsoft Dynamics GP 2016 R2 now being available, there are some new security roles and tasks which need to be added.

As they have for the last few versions, Microsoft have made available some SQL Scripts to insert these roles and tasks.

The Dynamics GP 2016 R2 ones can be downloaded from directly from Microsoft or I have a post containing links to all of the versions I can find, which has been updated for Dynamics GP 2016, which is here.

The scripts tend to be cumulative, so if you upgrade from Microsoft Dynamics GP 2013 SP2 to 2016 R2, you only need to run the final script.

Not sure why I thought the scripts were cumulative, but they are not. All of the scripts need to be run in sequence for your upgrade path.

As always, when running scripts against Dynamics GP databases, ensure you have a good backup before starting.

Hyper-V Error Creating Virtual Machines

Hyper-VA while ago I was having problems with Hyper-V whenever I tried to start a VM after my laptop had been to sleep or after it was first started up.

That problem went away recently after I upgraded to Windows 10 by rebuilding the laptop. However, it seems the problem has migrated to whenever I try to create a new virtual machine:

The server encountered an error while creatng

New Virtual Machine Wizard

The server encountered an error while creating {server name}.

The operation failed.

An unexpected error occurred: Logon failure: the user has not been granted the requested logon type at this computer. (0x80070569).

The operation failed.

The Hyper-V Virtual Machine Management server encountered an under unexpected error: Logon failure: the user has not been granted the requested logon type at this computer. (0x80070569).

This is a lot less disruptive than the originally problem as I started VMs a lot more often than I create them. And fortunately, the fix is the same as last time, so the batch file I created can still be used.

Run the batch file and the VM can be created; and even better is that I can keep the New Virtual Machine Wizard open with all of the settings configured when I run the batch file. So if I forget to run the batch file before starting to create the VM, I don’t lose any work.

Analysis Cubes for Microsoft Dynamics GP Configuration Error

Microsoft Dynamics GPAs part of my hands On with Microsoft Dynamics GP 2016 R2 series I installed and configured the Analysis Cubes for Microsoft Dynamics GP.

This is the first time I have done this (I tend to hand reporting to other members of my team) and to do it I had to install a couple of extra SQL Server components; namely SQL Server Analysis Services (SSAS) and SQL Server Integration Services (SSIS).

This all happened quite smoothly, but when I got to the Analysis Cubes configuration step I encountered a Fatal Error:

Fatal Error - Error encountered --> details written to 'C:\Users\iang\AppData\Local\temp\2\ACConfigurationWizard.log'

Fatal Error

Error encountered --> details written to 'C:\Users\iang\AppData\Local\temp\2\ACConfigurationWizard.log'

I navigated to the specified log file and had a look:

Click to show/hide error message

***Fatal Error Encountered***
System.Exception: The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in %SQL_PRODUCT_SHORT_NAME% Books Online.

Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Named Pipes Provider: Could not open a connection to SQL Server [2].  ---> System.Data.OleDb.OleDbException: Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Named Pipes Provider: Could not open a connection to SQL Server [2]. 
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at Microsoft.SqlServer.Dts.Server.StorageSqlServer.Connect()
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Dts.Server.StorageSqlServer.Connect()
   at Microsoft.SqlServer.Dts.Server.StorageSqlServer.FolderExists()
   at Microsoft.SqlServer.Dts.Server.Storage.FolderExists(String folderPath)
   at Microsoft.SqlServer.Dts.Runtime.Application.FolderExistsOnDtsServer(String sFolderName, String sServerName)
   at Microsoft.Dynamics.GP.AnalysisCubes.WarehouseDB.CreatePackageFolder()
   at Microsoft.Dynamics.GP.AnalysisCubes.WarehouseDB.CreateDrivers(String companyID, String currency, String exchangetable)
   at Microsoft.Dynamics.GP.AnalysisCubes.fMain.ProcessCompanyMasterPackages()
   at Microsoft.Dynamics.GP.AnalysisCubes.fMain.InstallAnalysisCubesProduct()

Continue reading “Analysis Cubes for Microsoft Dynamics GP Configuration Error”

Hands On With Microsoft Dynamics GP 2016 R2: Intermission

Microsoft Dynamics GPThe bulk of my Hands On with Microsoft Dynamics GP 2016 R2 posts are now completed and posted, so time for a brief intermission before I finish the series with a look at the new features.

These were introduced my Microsoft in November and I did a Feature of the Day series at the time, but I can now be hands on with them.

However, before I get to them, I have a few other posts to do.

Click to show/hide the Hands On With Microsoft Dynamics GP 2016 R2 Series Index