Transact SQL – PARSENAME

Background

SQL Server’s parsename function allows us to tokenize a string.

Objectname

It is particularly useful when reviewing object names.

ParseName

Sample

SQL


set nocount on
go

set XACT_ABORT on
go

declare @tblObject TABLE
(
	  [id] int not null
		identity(1,1)

	, [objectName] nvarchar(512)
)

insert into @tblObject
(
	[objectname]
)
select '[dbo].[person]'

union all

select '[tempdb].[dbo].[person]'

union all

select '[hrdb].[tempdb].[dbo].[person]'

union all

select '[aws].[hrdb].[tempdb].[dbo].[person]'

select
		  tblO.objectName

		, [object]
			 = parsename(tblO.[objectName], 1)

		, [schema]
			= parsename(tblO.[objectName], 2)

		, [database]
			= parsename(tblO.[objectName], 3)

		, [server]
			= parsename(tblO.[objectName], 4)

		, [dataCenter]
			= parsename(tblO.[objectName], 5)

from   @tblObject tblO

Output

parseName.objectName.20181108.1258PM

Explanation

  1. Works well
    • Things are good till we get to a situation with more than 4 tokens
  2. fails
    • Null is returns for all tokens once the string contains more than 4 tokens

STRING_SPLIT

Sample

SQL

set nocount on
go

set XACT_ABORT on
go

declare @tblObject TABLE
(
      [id] int not null
        identity(1,1)

    , [objectName] nvarchar(512)
)

insert into @tblObject
(
    [objectname]
)
select '[dbo].[person]'

union all

select '[tempdb].[dbo].[person]'

union all

select '[hrdb].[tempdb].[dbo].[person]'

union all

select '[aws].[hrdb].[tempdb].[dbo].[person]'

select
          tblO.objectName

        , [row]
            = ROW_NUMBER() OVER
                (

                    partition by
                        tblO.objectName

                    ORDER BY
                            (
                                select
                                    null
                            ) asc

                ) 

        , tblOSS.[value]

from   @tblObject tblO

CROSS APPLY STRING_SPLIT(tblO.objectName, '.')  tblOSS

order by
          tblO.[id] asc
        , [row] asc

Output

spiltString.objectName.20181108.0115PM

Explanation

  1. Works well
  2. Issues
    • String_Split
      • Rendering
        • Results are displayed in vertical order; whereas most people will likely prefer it in Horizontally
      • Functionality
        • The position of each token in the original stream is lost

XQuery

Sample

Scenario 1

Objective

Form XML Node

SQL

 set nocount on
go

set XACT_ABORT on
go

declare @valueSeparator     varchar(80)

declare @xmlRootNodeBegin   varchar(80)
declare @xmlRootNodeEnd     varchar(80)

declare @xmlNodeBegin   varchar(80)
declare @xmlNodeEnd     varchar(80)

set @valueSeparator = '.'

set @xmlRootNodeBegin = '';
set @xmlRootNodeEnd = '';

set @xmlNodeBegin = '';
set @xmlNodeEnd = '';

declare @tblObject TABLE
(
      [id] int not null
        identity(1,1)

    , [objectName] nvarchar(512)
)

insert into @tblObject
(
    [objectname]
)
select '[dbo].[person]'

union all

select '[tempdb].[dbo].[person]'

union all

select '[hrdb].[tempdb].[dbo].[person]'

union all

select '[aws].[hrdb].[tempdb].[dbo].[person]'

; with cteXQuery
as
(
    select
              tblO.[id]

            , tblO.objectName

            , [xmlRow] =
                cast
                (
                    (

                          @xmlRootNodeBegin

                        + replace

                        (
                              objectName
                            , @valueSeparator
                            , @xmlNodeEnd + @xmlNodeBegin
                        )

                        + @xmlRootNodeEnd
                    )
                    as xml
                ) 

    from   @tblObject tblO

)
select *

from   cteXQuery cteXQ

order by

        cteXQ.[id]

Output

objectName.XQuery.01.20181108.0202PM

Scenario 2

Objective

use XQuery to split XML Node

SQL
set nocount on
go

set XACT_ABORT on
go

declare @valueSeparator     varchar(80)

declare @xmlRootNodeBegin   varchar(80)
declare @xmlRootNodeEnd     varchar(80)

declare @xmlNodeBegin   varchar(80)
declare @xmlNodeEnd     varchar(80)

--set @valueSeparator = '\'
set @valueSeparator = '.'

set @xmlRootNodeBegin = '';
set @xmlRootNodeEnd = '';

set @xmlNodeBegin = '';
set @xmlNodeEnd = '';

declare @tblObject TABLE
(
      [id] int not null
        identity(1,1)

    , [objectName] nvarchar(512)
)

insert into @tblObject
(
    [objectname]
)
select '[dbo].[person]'

union all

select '[tempdb].[dbo].[person]'

union all

select '[hrdb].[tempdb].[dbo].[person]'

union all

select '[aws].[hrdb].[tempdb].[dbo].[person]'

; with cteXQuery
as
(
    select
              tblO.[id]

            , tblO.objectName

            , [xmlRow] =
                cast
                (
                    (

                          @xmlRootNodeBegin

                        + replace

                        (
                              objectName
                            , @valueSeparator
                            , @xmlNodeEnd + @xmlNodeBegin
                        )

                        + @xmlRootNodeEnd
                    )
                    as xml
                ) 

    from   @tblObject tblO

)
, cteXQueryNode
as
(

    select
              [id]

            , cteXQ.objectName

            , cteXQ.xmlRow

            , [rowNumber]
                = ROW_NUMBER()
                    OVER
                    (
                        PARTITION BY
                              [objectName]

                        ORDER BY
                              [objectName] 

                    )

            , [xmlNode]
                = n.value('.','varchar(4000)') 

    from   cteXQuery cteXQ

    cross apply [cteXQ].xmlRow.nodes('/Root/Node') m(n)

)
select *

from   cteXQueryNode

order by
          [id]

Output

objectName.XQuery.02.20181108.0209PM

Scenario 2

Objective

use XQuery to split XML Node and also pivot columns

SQL

set nocount on
go

set XACT_ABORT on
go

declare @valueSeparator     varchar(80)

declare @xmlRootNodeBegin   varchar(80)
declare @xmlRootNodeEnd     varchar(80)

declare @xmlNodeBegin   varchar(80)
declare @xmlNodeEnd     varchar(80)

set @valueSeparator = '.'

set @xmlRootNodeBegin = '';
set @xmlRootNodeEnd = '';

set @xmlNodeBegin = '';
set @xmlNodeEnd = '';

declare @tblObject TABLE
(
      [id] int not null
        identity(1,1)

    , [objectName] nvarchar(512)
)

insert into @tblObject
(
    [objectname]
)
select '[dbo].[person]'

union all

select '[tempdb].[dbo].[person]'

union all

select '[hrdb].[tempdb].[dbo].[person]'

union all

select '[aws].[hrdb].[tempdb].[dbo].[person]'

; with cteXQuery
as
(
    select 

              tblO.[id]

            , tblO.objectName

            , [xmlRow] =
                cast
                (
                    (

                          @xmlRootNodeBegin

                        + replace

                        (
                              objectName
                            , @valueSeparator
                            , @xmlNodeEnd + @xmlNodeBegin
                        )

                        + @xmlRootNodeEnd
                    )
                    as xml
                ) 

    from   @tblObject tblO

)
, cteXQueryNode
as
(

    select
              [id]

            , cteXQ.objectName

            --, cteXQ.xmlRow

            , [rowNumber]
                = ROW_NUMBER()
                    OVER
                    (
                        PARTITION BY
                              [objectName]

                        ORDER BY
                              [objectName] 

                    )

            , [xmlNode]
                = n.value('.','varchar(4000)') 

    from   cteXQuery cteXQ

    cross apply [cteXQ].xmlRow.nodes('/Root/Node') m(n)

)
select
          [id]
        , tblP.objectName
        , [element1] = [1]
        , [element2] = [2]
        , [element3] = [3]
        , [element4] = [4]
        , [element5] = [5] 

from   cteXQueryNode

pivot
(
    max
    (
        [xmlNode]
    )

    for [rowNumber]

    in
            (
                 [1]
               , [2]
               , [3]
               , [4]
               , [5]

            )

) tblP

order by
        [id]
Output

objectName.XQuery.03.20181108.0213PM.PNG

Transact SQL and the Null Character

Background

Transact SQL is a very high level language and 99.99% of the time one does not really have to pay attention.

This morning for me was not one of those times that I did not have to be attentive.

Lineage

A quick following up to our last post :-

SQL Server – Transact SQL – Get Network Info
Link

In that post we discussed a couple of options for discovering network metadata per SQL Server Instances.

Query

Original Query

Here is our original query

SQL


select *

from sys.dm_server_registry tblDSR

where tblDSR.value_name = 'TcpDynamicPorts'

Output

sysDOTdm_server_registry__20180607_0918PM

Revised Query – Filter Out Null and Empty Records

In our revised query we will filter out null and empty records

SQL

select *

from sys.dm_server_registry tblDSR

where tblDSR.value_name = 'TcpDynamicPorts'

and tblDSR.value_data is not null

and tblDSR.value_data != ''

Output

sysDOTdm_server_registry__20180607_0918PM

Explanation

Our filter clauses “is Not null” and != ” did not help.

Revised Query – Filter Out Null Character

In our second attempt, we filter out records that simply have the NULL CHARACTER as there whole value

SQL

select *

from sys.dm_server_registry tblDSR

where tblDSR.value_name = 'TcpDynamicPorts'

and tblDSR.value_data is not null

and tblDSR.value_data != ''

and tblDSR.value_data != char(0)

Output

sysDOTdm_server_registry__20180607_0925AM

Explanation

Our filter clause is != char(0) and did discard the unwanted records.

SQL Server – Transact SQL – Get Network Info

Background

Wanted to review the IP Addresses for some of our SQL Server Instances that are hosted on Cloud Servers.

Outline

  1. CONNECTIONPROPERTY
  2. sys.dm_exec_connections
  3. sys.dm_tcp_listener_states

Code

CONNECTIONPROPERTY

SQL

Sample

SELECT 

          [netTransport]
            = CONNECTIONPROPERTY('net_transport')

        , [authScheme]
            = CONNECTIONPROPERTY('auth_scheme')

        , [ipAddress]
            = CONNECTIONPROPERTY('local_net_address')

        , [iPort]
            = CONNECTIONPROPERTY('local_tcp_port')

Output

connectionProperty_20181107_0732AM

sys.dm_exec_connections

SQL

Sample

SELECT
              [machineName]
                = SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 

            , [sqlInstance]
                = SERVERPROPERTY('servername') 

            , [instance]
                = SERVERPROPERTY('InstanceName')

            , [ipAddress]
                = tblDMEC.[LOCAL_NET_ADDRESS]

            , [numberofConnections]
                = count(*)

 FROM sys.dm_exec_connections tblDMEC

 where
        (

            ( tblDMEC.LOCAL_NET_ADDRESS is not null )

        )

 group by

         tblDMEC.LOCAL_NET_ADDRESS

Output

sys.dm_exec_connections.20181107.0738AM.PNG

sys.dm_tcp_listener_states

SQL

Sample

SELECT * 

FROM   sys.dm_tcp_listener_states tblTLS

Output

sys.dm_tcp_listener_states.20181107.0811AM.PNG

Db/2 – Locking – DBeaver

Background

Let us see how to stimulate a database blocking situation via DBeaver.

Steps

Outline

  1. Query 1
    • Start a new query window
    • Set commit mode to Manual Commit ( primary )
      • Manual or Auto-Commit
        • Ensure that Mode is set for Manual Commit
      • Issue Database Statement
        • Make sure that statement is not committed/rollback via “Explicit” reference
  2. Query 2
    • Start a new query window ( secondary )
    • Set commit mode to Manual Commit
      • Manual or Auto-Commit
        • Ensure that Mode is set for Manual Commit
      • Issue Database Statement
        • Make sure that statement is not committed/rollback via “Explicit” reference
  3. Review Transaction Log
    • List uncommitted queries

 

Steps

  1. Review Commit Mode ( Manual and Auto-Commit Mode ? )
    • Issue a new query window
    • From the tool bar confirm transaction commit mode
    • Make sure is is set to manual commit
  2. Query Window – 01
    • Create new query window
    • Enter Query
    • Execute Query
    • Make sure query does not contain explicit commit nor rollback clause
  3. Query Window – 02
    • Create new query window
    • Enter Query
    • Execute Query
    • Make sure query does not contain explicit commit nor rollback clause
  4. Review Transaction Log
    • Invoke menu “Database/Transaction Log”
    • Transaction Log
      • The “Transaction Log” window opens
      • Review list of uncommitted transactions

 

Images

Manual/Auto Commit Mode

Auto-Commit Mode

transactionCommitMode.Auto.20181024.1029AM.PNG

Changing Mode

transactionCommitMode.Reviewing.20181024.1030AM.PNGl

Mode Changed to Manual

transactionCommitMode.Manual.20181024.1031AM.PNG

Query 1

Toolbar

toolbar.01.20181024.0953AM.PNG

Query Window – 01

Query Entered into Query Window 01

toolbar.02.setMode.20181024.0957AM.PNG

Query Executed against Query Window 01

toolbar.03.queryExecuted.20181024.0958AM.PNG

Query Window – 02

Query Entered into Query Window 02
toolbar.03.query02.BeforeExecution.20181024.0959AM.PNG
Query Executed against Query Window 02

toolbar.03.query02.AfterExecution.20181024.1000AM.PNG

Transaction Log

Review Transaction Log

transactionLog.02.20181024.1001AM.PNG

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.

Dbeaver / Date Format

Background

Quick follow-up to our last post on String Representation of datetime.

Referenced Post

BTW, that post is here:

Db/2 – LUW – SQL Error [22007]: “The string representation of a datetime value is out of range”
Link

Goal

In this post we will discuss options for displaying date columns in “locale” sensitive ways.

Implementation

Dbeaver

Query

SQL Server

When we issue a query that looks like the one below :-

Sample

select
          tblSD.name as [name]
        , tblSD.create_date as [createTS]
        , cast(tblSD.create_date as date) as [createDate]

from   sys.databases tblSD

Output

sys.databases.20180926.0714AM

Explanation
  1. Columns
    • createDate
      • Values
        • 2003-04-08
      • Representation
        • YYYY-MM-DD
        • ISO

Dbeaver Configuration

Objective

Let us configure our JDBC Connection

Steps

  1. From the left panel, choose Data Source
  2. Right click on Data Source
  3. Select Properties
  4. In Properties Window
  5. In the left window, select “Result Sets” \ “Data Formats”
  6. In the Data Formats window
    • Tick the checkbox to grant us the opportunity to modify
    • Customize per your needs
      • Locale
        1. Language :- en-English
        2. Country :- US – United States
        3. Locale :- en_US
      • Format
        1. Type :- Date
        2. Pattern :- MMM-dd-yyyy

Data Formats

Data Formats – Date
Original

DateFormats.20180926.0720AM.PNG

Revised

DateFormats.20180926.0724AM

Data Formats – Other

Please keep in mind we are able to customize other data types, as well.

Inclusive are Date time types  ( Date, Time, Timestamp ) and Numbers.

DateFormats.OtherOptions.20180926.0828AM.PNG

Query

SQL Server

If we go back and re-issue our query, here is what it will look like:

Output

sys.databases.20180926.0726AM

Explanation

Our dates now look like Month-Day-Year.

References

  1. Dbeaver
    • dbeaver/dbeaver
      • Dates from DB2 on IBM i are displayed as “null” #2948
        Opened By / On Date :- savadgecayet commented on Feb 8
        Link