SQL Server – v2017 – Error – “VS Shell installation has failed with exit code 1638”.

Background

Back installing SQL Server v2017 Developer Edition.

Earlier Post

Our preceding attempt is here:

SQL Server – v2017 – Install – “Oracle JRE 7 Update 51 (64-bit) is required for Polybase”
Link

 

Error

Got another error.

Error Image

VCRuntimeExitCode1638_2018109_1048AM.PNG

Error Text


TITLE: Microsoft SQL Server 2017 Setup
------------------------------

The following error has occurred:

VS Shell installation has failed with exit code 1638.

For help, click: https://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=14.0.1000.169&EvtType=0x5B39C8B9%25401434%25403

Trouble Shooting

Current Status of Install

Install_VCRuntime140_CPU32_Action

The error pasted above came up during the installation of VCRuntime140/32 bit.

This is based on the progress message.

 

What is Install_VCRuntime140_CPU32_Action?

Glad you asked.

VCRuntime140_CPU32
  1. VCRuntime
    • Visual C++ Runtime
  2. 140
    • ??? ( Visual C++ 2010 )
  3. CPU32
    • 32-bit

Version Number

Let us determine the Product Number for the identified runtime [ 140 ]

Product Version Number Internal Version Number Date Released
Visual C++ 2017 14.1 March 7, 2017
Visual C++ 2015 14.0 July 20, 2015
Visual C++ 2013 12.0 October 17, 2013
Visual C++ 2012 11.0 August 15, 2012
Visual C++ 2010 10.0 April 12, 2010
Visual C++ 2008 9.0 November 2007
Visual C++ 2005 8.0 November 2005

Version Number

It appears that for Version Number 140, our internal Version Number is 14.0.

And, that places us at Visual C++ 2015.

Remediation

Uninstall Visual C++ 2015

Outline

  1. Access Programs & Features
  2. Locate Visual C++ 2015
  3. Uninstall programs

Screen Shots

 

Microsoft Visual C++ 2015 Redistributable (x64 ) – 14.0.24215

Selected

programAndFeatures_VC2015_20181009_1051AM.PNG

Modify Setup
programAndFeatures_VC2015_x64_20181009_1052AM.PNG
Uninstall Successful
programAndFeatures_VC2015_x64_Successful_20181009_1052AM.PNG

 

Microsoft Visual C++ 2015 Redistributable (x86 ) – 14.0.24215

Selected

programAndFeatures_VC2015_x86_Selected_20181009_1053AM.PNG

Uninstall
programAndFeatures_VC2015_x86_Starting_20181009_1054AM.PNG
Uninstall Completed

programAndFeatures_VC2015_x86_Completed_20181009_1055AM.PNG

Summary

Once Visual Studio 2015, 32 and 64-bit, was uninstalled the installation of SQL Server 2017 continued un-prompted.

References

  1. Stack Exchange
    • DBA Stack Exchange
      • Help installing SQL Server 2017 – VS Shell installation has failed with exit code 1638
        Link
  2. Microsoft
    • CSS SQL Server Engineers
      • Guillaume Fourrat [MSFT]
        • Installation of SQL Server 2017 failing with ‘VS Shell installation has failed with exit code 1638’
          Link

 

SQL Server – v2017 – Install – “Oracle JRE 7 Update 51 (64-bit) is required for Polybase”

Background

Installing SQL Server v2017 and ran into an error that I have seen before.

Error

Error Image

OracleJRE7Update51_x64.2018109.1111AM.PNG

Error Textual

Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase

Tackled Before

History

Already tackled before here:

SQL Server (v2016) – Installation – Blocked – “Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase”
here

History

The fix applied was to install Java JRE downloaded from here:

https://www.oracle.com/technetwork/java/javase/downloads/index.html
Link

But, unfortunately even though install applied no help this time.

Trouble Shooting

Let us dig a bit deeper.

SysInternals

Process Monitor

Image

sysInternals.processMonitor.20181009.1010AM.PNG

Explanation

  1. Identified component used by SQL Server Setup
    • Component is ScenarioEngine
  2. ScenarioEngine
    • Log
      • C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\20181009_092126\Detail.txt

Tail

Tail For Win32

Download

Downloaded Tail For Win32 from here:

Tail for Win32
Link

Usage

Launched “Tail For Win32” and loaded the identified SQL Server Log file ( Detail.txt )

Results
Image
TailforWin32_Usage_20181009_1151AM
Textual

13) 2018-10-09 10:13:06 Slp: Init rule target object: Microsoft.SqlServer.Configuration.Polybase.Polybase_IsMinJavaVersionInstalledFacet
(13) 2018-10-09 10:13:06 SQLPolyBase: Could not find registry setting HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion.
(13) 2018-10-09 10:13:06 SQLPolyBase: Minimum version expected: 1.7.51. Java not found.
(13) 2018-10-09 10:13:06 SQLPolyBase: Rule 'Polybase_IsMinJavaVersionInstalled' detection result: IsMinJavaVersionInstalled= False
(13) 2018-10-09 10:13:06 Slp: Evaluating rule        : Polybase_IsMinJavaVersionInstalled
(13) 2018-10-09 10:13:06 Slp: Rule running on machine:
(13) 2018-10-09 10:13:06 Slp: Rule evaluation done   : Failed
(13) 2018-10-09 10:13:06 Slp: Rule evaluation message: This computer does not have the Oracle Java SE Runtime Environment Version 7 Update 51 (64-bit) or higher installed. The Oracle Java SE Runtime Environment is software provided by a third party. Microsoft grants you no rights for such third-party software. You are responsible for and must separately locate, read and accept applicable third-party license terms. To continue, download the Oracle SE Java Runtime Environment from https://go.microsoft.com/fwlink/?LinkId=526030.
(13) 2018-10-09 10:13:06 Slp: Send result to channel : RulesEngineNotificationChannel

Textual
  1. QLPolyBase: Could not find registry setting HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion.
    (13) 2018-10-09 10:13:06 SQLPolyBase: Minimum version expected: 1.7.51. Java not found.

Registry

regedit

Images

Regedit – Image – Before
Image

registry_20181009_1038AM.PNG

Textual
  1. Computer\HKEY_LOCAL_MACHINE\Software\JavaSoft
    • Java Plug-in
    • Java Update
    • Java Web Start
    • Java Web Start Caps
    • JRE
Explanation
  1. HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion.
    • Java Runtime Environment
      • Missing

Remediation

Outline

Please download Java JRE 8 and install it.

Download

Please download artifacts from here :-

Java SE Runtime Environment 8 Downloads
https://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.html
Link

Artifacts

Image

Java SE Runtime Environment 8u181

download_JavaSERuntimeEnvironment_20181009.1221PM.PNG

download_20181009.1026AM.PNG
Explanation
  1. Chose to download Windows X64

 

Install

Images

Destination Folder

download_DestinationFolder_20181009.1031AM.PNG

Installing
Installing -01

install_20181009.1032AM.PNG

java Setup – Complete

install_JavaSetupComplete_20181009.1033AM.PNG

Change in License Terms

download_JavaSERuntimeEnvironment_Roadmap_20181009.1030AM.PNG

Registry

Using regedit, please review the Java’s registry structure.

regedit

Images
Regedit – Image – After
Image

registry_java_jre_1Dot8_20181009_1243PM.PNG

Explanation
  1. HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion is now present

 

Summary

It appears that Oracle has changed the folder structure for Java in the Windows Registry.

Specifically what used to be

HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion.

is now

HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\JRE\CurrentVersion.

Please be sure to have Java JRE v1.8 ( Java 8) installed for SQL Server v2016\v2017 Polybase.

BitLocker – Configuration – Error – “Unable to find the Reporting Services instance name”

Background

Recently we ran into an “ha ha” moment installing Microsoft BitLocker.

 

BitLocker Administration and Monitoring

Configuring Reports

Here is the “Configuring Reports” window.

ConfigureReports_20180720_1008AM

Error

Error Image

unableToFindTheReportingServicesInstanceNameMSSQLServer

Error Text

Unable to find the Reporting Services instance name <server-name>\MSSQLServer

Trouble Shooting

Reporting Services Configuration Tool ( RSConfigTool )

Launched SQL Server Reporting Services Configuration Tool ( RSConfigTool).

RSConfig_ReportServerStatus_20180720_1033AM.png

Ensured that the service is running and noted the Instance ID.

The Instance ID is SSRS.

Remediation

BitLocker Administration and Monitoring

Configuring Reports

For the instance name, please note the instance name recorded earlier.

That instance name is SSRS.

Configuration SSRSDB.20180720_1041AM

 

 

SQL Server – Network – Get TCP Ports

Background

Get network ports that the current SQL Server Instance is using.

Outline

There are some pathways to getting the SQL Server Network Ports:

  1. sys.dm_server_registry
    • SQL Server Version
      • v2008

Code

sys.dm_server_registry

SQL


select 

          tblDSR.registry_key
        , tblDSR.value_name
        , tblDSR.value_data
        , [userOrAdmin]
            = case
                when (tblDSR.registry_key like '%AdminConnection%') then 'Admin'
                else 'User'
              end

from   sys.dm_server_registry tblDSR

/*
	Database Engine - MSSQLServer
*/
where
       (

            (
                ( tblDSR.registry_key like '%\MSSQLServer\%' )
			)
        )

/*
	Filter in TCP Ports
*/
and
       (

            (
                tblDSR.value_name in
                    (
                          'TcpDynamicPorts'
                        , 'TcpPort'
                    )
            )

        )

/*
    Filter out \IP1 thru \IP4
        HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP1
        HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP2
        HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP3
        HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP4
*/
and
       (
            ( tblDSR.registry_key not like '%\IP_')
        )

/*
    Filter out empty data
*/
and
        (

                ( tblDSR.value_data is not null )
            and ( tblDSR.value_data != '' )
            and ( tblDSR.value_data != '0' )
        )

Output

Output – Default Instance

sysDOTdm_server_registry__Default__20180606_0258PM.png

Output – Named Instance

sysDOTdm_server_registry__20180606_0239PM.png

Explanation

  1. Registry Key
    • Please close out your eyes to the AdminConnection; unless you are trying to connect to the Admin Port
    • Most people will be using the regular user default port

References

  1. Microsoft
    • Docs / SQL / Relational databases / System dynamic management views
      • sys.dm_server_registy

Transact SQL – STRING_AGG

Background

In our post on MySQL – Information_schema.statistics we spoke glowingly of the GROUP_CONCAT Function.

I really could not find a way around using it in MySQL.

I ran into some difficulties using it and as with problems one just googles for workarounds.

BTW, the MySQL post is here.

SQL Server

Introduction

It seems that in version 2017, MSFT played catch up.

And, added a similar function.

String_Agg

Code


select
        [object]
            = quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , [indexID]
            = tblSI.[index_id]

        , [index]
            = tblSI.[name]

        , [indexType]
            = tblSI.[type_desc]

        , [columnList]
            = 
                STRING_AGG 
                (
                      concat
                      (
                          tblSC.[name]
                        , ''
                      )	

                    , ', '
                ) 
                WITHIN GROUP 
                (
                    ORDER BY 
                        tblSIC.[key_ordinal]
                )
            

from   sys.schemas tblSS

inner join sys.objects tblSO

        on   tblSS.[schema_id] = tblSO.[schema_id]
 
inner join sys.indexes tblSI

        on   tblSO.[object_id] = tblSI.[object_id]

inner join sys.index_columns tblSIC

        on   tblSI.[object_id] = tblSIC.[object_id]
        and  tblSI.[index_id]  = tblSIC.[index_id]

inner join sys.columns tblSC

        on   tblSIC.[object_id] = tblSC.[object_id]
        and  tblSIC.column_id = tblSC.column_id

/*
    Skip MS Shipped Objects
*/
where tblSO.is_ms_shipped = 0

/*
    Exclude Included Columns
    Only Include actual Key Columns
*/
and   tblSIC.[key_ordinal] > 0

group by

                quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , tblSI.[index_id]

        , tblSI.[name]

        , tblSI.[type_desc]

order by

                quoteName(tblSS.[name])
              + '.'
              + quoteName(tblSO.[name])

        , tblSI.[index_id]

        , tblSI.[name]



Output

 

Crediting

Crediting Dan M for asking the question.

And, Martin Smith for ably.

Simulating group_concat MySQL function in Microsoft SQL Server 2005?
Link

 

References

  1. Microsoft
    • String_Agg
    • sys.index_columns
  2. Stack Overflow
    • Simulating group_concat MySQL function in Microsoft SQL Server 2005?
      Link

SQL Server – Operator – Top – Top Expression (0)

Background

Earlier today I found myself pressing to make sure I had done right by a query.

 

Query Plan

Query Plan – 01

Here is the original query Plan

Image

queryPlan_computeScaler_HashMatch_20180517_1139AM [clipped]

Explanation

  1. I know that I don’t really want a Hash Match
    • Took care of the Hash Match by reducing the query from two tables to a single table
    • There are a few novel ways to do so, and will cover that later

 

Query Plan – 02

Here is the query Plan once we got rid of the secondary table

Image

queryPlan_computeScaler_HashMatch_20180517_1143AM [clipped]

 

Top ?

I was stuck at the Top Operator for a very long time

Explanation

  1. Rowcount
    • Do I have a set rowcount somewhere
    • Is my environment introducing a constraint for maximum number of records to “touch
    • Is my edition of SQL Server throttling performance
  2. Top
    • Do I have a top N clause somewhere

 

Operator – Top – Default

Overview

Here is what our Top Operators looks like when we do not have “set rowcount” set and we do not have an actual TOP Clause.

Image

Explanation

  1. Actual Number of Rows :- 65
  2. Output List :- sysschobjs.id & sysschobjs.nsid
  3. Top Expression :- (0)

 

Operator – Top – “Set rowcount”

Overview

What if we add an actual set rowcount

Image

 

Explanation

  1. Actual Number of Rows :- 2
  2. Output List :- sysschobjs.id & sysschobjs.nsid
  3. Top Expression :- (0)

Operator – Top – “Select TOP N”

Overview

Here is what we see when we add a “Top 1” Clause.

Image

Explanation

  1. Actual Number of Rows :- 1
  2. Output List :- sysschobjs.id & sysschobjs.nsid
  3. Top Expression :- (1)

 

Other Things

Overview

I was stuck and so tried other things; such as

  1. Newer version of SQL Server ; v2017 to be exact
  2. Took out the insert into and performed a simple select

Could not reproduce…

 

Craig Freedman ( MSFT )

Finally goggled on the right terms and read what Craig Freedman has to say.

The particular post that I will be quoting is:

ROWCOUNT Top
Link

  1. TOP Operator
    • If you’ve looked at any insert, update, or delete plans, including those used in some of my posts, you’ve probably noticed that nearly all such plans include a top operator.
  2. SET ROWCOUNT
    •  It is a ROWCOUNT top. It is used to implement SET ROWCOUNT functionality.
  3. Why doesn’t SQL Server add a ROWCOUNT top to select statements?
    • SQL Server implements SET ROWCOUNT for select statements by simply counting and returning the correct number of rows from the root of the plan.  Although this strategy might work for a really trivial update plan such as the one above, it would not work for more complex update plans.  For instance, if we add a unique index to our table, the update plan becomes substantially more complex
  4. Placement
    • By placing the ROWCOUNT top above the table scan, the optimizer can ensure that the server updates exactly the correct number of rows regardless of the complexity of the remainder of the plan.

 

Martin Smith

The good thing about blogging and allowing comments is that people can come back and provide helpful feedback.

Here is one from Martin Smith:

Martin Smith
December 29, 2012 at 8:15 am

In 2012 it looks like this operator is only added to plans run under “SET ROWCOUNT” of other than zero. As far as I can discern it is added in to the set_options used as a plan cache key.

SQL Server Versions

Here are the versions of SQL Server where you will be able to reproduce the Top (0) Operator preceding data effecting operators :-

  1. 2005
  2. 2008-R2

Dedication

Thankfully I have a far better grasp courtesy of two able men, Craig Freedman & Martin Smith.

SQL Server – Querying for OS Version

Background

As SQL Server now runs on Linux it is likely a good idea to be able to determine the underlying OS that a specific instance is sitting on.

 

Choices

Here are some choices for determining a targeted SQL Instance OS Version :-

  1. @@version
  2. sys.dm_os_host_info
    • SQL Server Versions
      • v2017 and above

 

System Function

@@version

Syntax


select @@version

Output

Tabulated
@@version OS/Client OS/Server
 Microsoft SQL Server 2017 (RTM-CU3-GDR) (KB4052987) – 14.0.3015.40 (X64)
Dec 22 2017 16:13:22
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Enterprise 2016 LTSB 10.0 <X64> (Build 14393: )
Windows 10
Microsoft SQL Server 2014 (SP2-CU7) (KB4032541) – 12.0.5556.0 (X64)
Aug 17 2017 12:07:38
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 (Build 14393: ) (Hypervisor)
Windows Server 2016
Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64)
Aug 22 2017 17:04:49
Copyright (C) 2017 Microsoft Corporation
Express Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)
Windows 8.1 Windows Server 2012 R2
Microsoft SQL Server 2014 – 12.0.4100.1 (X64)
Apr 20 2015 17:29:27
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (Hypervisor)
Windows Vista Windows Server 2008

 

Dynamic Management View

sys.dm_os_host_info

Syntax


SELECT 
          host_platform
	, host_distribution
	, host_release
	, host_service_pack_level
	, host_sku
	, os_language_version  

FROM sys.dm_os_host_info

;

Output

 

Summary

If you happen to be running SQL Server v2017, you will have not do to all the parsing involved with getting accurate data from @@version.

 

Addendum

Did a similar post here.

This was back in 2010.  And, that post is titled “MS SQL Server – Read OS Version“.

Windows NT 6.3

Unfortunately “Windows NT 6.3” is returned for the following OSes:

  1. Windows 2016
  2. Windows 2012

Prove

Windows Server 2016

SQL Server

master.dbo.xp_MSver
Image

Explanation
  1. Name :- WindowsVersion
  2. Internal Value :- 364118016
  3. Character Value :- 6.3 (14393)

 

WinOS

systeminfo
Image

Blogging

Blogging is an interesting option.

How much to reveal and how much to slay.

It all depends on what the original intent is.

Arguing for the sake of arguing is pointless.

 

References

  1. Microsoft
    • SQL Server
      • Docs > SQL > Relational databases > System dynamic management views
        • sys.dm_os_host_info ( Transact SQL )
          Link
      • Docs > SQL > T-SQL > Functions
        • @@version ( Transact SQL )
          Link
      • Docs / SQL / Relational databases / System stored procedures