SSMS – Linked Server – Column Metadata

Background

Had a good meeting this morning while we try to figure out how to better support our Developers.

One of the issues they brought up was an inability to view the datatype of linked Server tables.

Pictorial

Here is the deepest granularity when we connect to our Linked Server.

Image

ssms.columns.20190114.0425PM.PNG

Explanation

  1. We see the following
    • Server
    • Databases
    • Objects
      • Tables
      • Views

View Columns

Script

Outline

Here are avenues we can explore to view column metadata :-

  1. sp_columns_ex
  2. Openquery/sys
    • sys.all_columns
  3. Openquery/informational_schema
    • informational_schema.columns

Choices

exp_columns_ex

Syntax

exec sp_columns_ex
		  @table_server
		, @table_catalog
		, @table_schema
		, @table_name   

Sample

declare @linkedServer   sysname
declare @linkedDatabase sysname
declare @linkedSchema   sysname
declare @linkedTable    sysname

set @linkedServer= 'AWS-JobBuilder'
set @linkedDatabase = 'acs_ap'
set @linkedSchema = 'dbo'
set @linkedTable = 'ap_user'

exec sp_columns_ex
		  @table_server  = @linkedServer
		, @table_catalog = @linkedDatabase
		, @table_schema  = @linkedSchema
		, @table_name    = @linkedTable

Output

sp_tables_ex.2019014.0443pm

openquery/sys.*

Syntax

select top 10 *

from   openquery
        (
              [AWS-JobBuilder]

            , '
                    select
                              [server] = serverproperty(''servername'')
                            , [database] = db_name()
                            , [schema] = tblSS.name
                            , [object] = tblSAO.name
                            , [column] = tblSAC.name
                            , [type]   = tblST.[name]
                            , tblST.max_length
                            , tblST.is_nullable

                    from   sys.schemas tblSS

                    inner join sys.all_objects tblSAO

                            on tblSS.schema_id = tblSAO.schema_id

                    inner join sys.all_columns tblSAC

                        on tblSAO.object_id = tblSAC.object_id

                    inner join sys.types tblST

                        on  tblSAC.system_type_id = tblST.system_type_id
                        and tblSAC.user_type_id = tblST.user_type_id

              '
        )

Output

openquery.sys.all.2019014.0452pm

openquery/information_schema.columns

Syntax
select top 10 *

from   openquery
        (
              [AWS-JobBuilder]

            , '
                select top 100 

                          [server] = serverproperty(''servername'')

                        , [database] = tblSIC.[TABLE_CATALOG]

                        , [schema] = tblSIC.[TABLE_SCHEMA]

                        , [object] = tblSIC.[TABLE_NAME]

                        , [column] = tblSIC.[COLUMN_NAME]

                        , [position] = tblSIC.[ORDINAL_POSITION]

                        , [dataType] = tblSIC.[DATA_TYPE]

                        , [charMaxLength] = tblSIC.[CHARACTER_MAXIMUM_LENGTH]

                        , [charOctetLength] = tblSIC.[CHARACTER_OCTET_LENGTH]

                from   information_schema.columns tblSIC

              '
        )			   	

Output

openquery.openquery.informational_columns.2019014.0515PM.PNG

Summary

Unfortunately, SQL Server Management Studio ( SSMS ) v17.x does not let us view columns on Linked Servers.

To gather column level metadata, one has to write code.

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.