Transact SQL – XQuery – Ancestor

Background

Playing around with XML, but dug in a quick sand.

XML

Here is the XML

Image

foo.xml

Code

Code

<root>                                                                                                             
  <foo id="0">                                                                                                       
	<foo id="1">                                                                                                   
	  <bar attr="xxx" />                                                                                         
	</foo>                                                                                                         
	<foo id="2">                                                                                                   
	  <bar attr="sam" />                                                                                         
	</foo>                                                                                                         
	<foo id="3">                                                                                                   
	  <tar>                                                                                                      
		<bar attr="samuel" />                                                                                     
	  </tar>                                                                                                     
	</foo>                                                                                                         
  </foo>                                                                                                             
</root>

Code

Find Nodes whose element matches specific value

SQL


declare @id int

set @id = 3

SELECT
         [id] = item.value
                    (
                          '@id'
                        , 'INT'
                    ) 

FROM @foo.nodes('//foo') t (item)

WHERE item.value('@id', 'INT') = @id

Output

XPath.attribute.value.is.3

Pass X PATH :- Child::bar/Attribute

SQL


-- child//bar/@attr=sam
SELECT
         [element.attribute.sam]
            = t.item.query('.')

FROM @foo.nodes('//*[child::bar[@attr=''sam'']]') t (item)

Output

XPath.attribute.value.is.sam

Find Descendants

SQL


-- descendant
SELECT
         [element.descendant]
            = t.item.query('.')

FROM @foo.nodes('//foo[@id=2]/descendant::*[position()=1]') t (item)

Output

XPath.element.descendant

Find Ascendants

SQL


-- ancestor
SELECT
         [element]
            = t.item.query('.')

FROM @foo.nodes('//bar[@attr=''sam'']/ancestor::*[position()=1]') t (item)

Output

Output – Textual


Msg 9335, Level 16, State 1, Line 61
XQuery [nodes()]: The XQuery syntax 'ancestor' is not supported.

Output – Image

XPath.element.ascendant

Source Code Control

Git Hub

TransactSQLXQuery/ancenstor/
Link

 

Summary

Transact SQL/XQuery does not support the ancestor function.

Referenced Work

  1. Stack Overflow
    • XPath to find nearest ancestor element that contains an element that has an attribute with a certain value
      Link
    • xpath: find a node that has a given attribute whose value contains a string
      Link

 

Installation & Configuring WINS on Windows 2003

Background

To get Linux and Windows computers to be able to use the same name resolution mechanism we can rely on DNS or Microsoft WINS.

While DNS supports global address names, WINS is better suited for mapping Microsoft Windows computer names to their corresponding IP Address.

 

WINS

What is WINS

Wikipedia

Link

Windows Internet Name Service (WINS) is Microsoft’s implementation of NetBIOS Name Service (NBNS), a name server and service for NetBIOS computer names.

WINS offers to NetBIOS names a central mapping of host names to network addresses.

Like the DNS, it is implemented in two parts.

The parts are :-

  1. Server
    • A server service that manages a database of netbios names.
    • It facilitates server to server replication, service requests, and conflicts.
    • A TCP/IP client component supports client’s registration and renewal of names
  2. Client
    • And, respond to clients query requests

Installation

Outline

  1. Windows
    • Launch Configure Your Server
      • Control Panel, Administrative Tools, Configure Your Server
    • Window – “Welcome to the Configure Your Server Wizard”
    • Window – Preliminary Steps
    • Window – Server Role
      • Select “WINS Server”
    • Avail Windows Installation Media
      • In our case Windows Server 2003 Standard Edition

Images

Image – Welcome

Image – Preliminary Steps

Image – Server Role

WINS server is marked not Configured

Image – Configuring Components

Please insert the Compact Disc labeled ‘Windows Server 2003, Standard Edition CD-ROM’ into your CD-ROM

 

Files Needed – Copy files from

Summary of Selections

This Server is now a WINS Server

Configuration

On each Workstation, access Control panel, networking, NIC Configuration and specify WINS Server IP Address.

Samba :- Name Resolution – From Windows, Resolve Linux Host Names

Background

Upon trying to connect to the lone Linux host on our LAB network found it inaccessible.

Later found out that the IP Address received from the DHCP Server has changed.

 

Goal

Need to be able to use name resolution and find the Linux host from a windows box.

 

Network Ports

  1. Netbios Name Service
    • UDP Port 137
  2. Netbios Datagram Service
    • UDP Port 138
  3. SMB/CIFS Network Traffic
    • Windows NT 4.0 and Below
      • TCP Port 139
      • UDP Port 139
    • Windows 2000 and above
      • TCP Port 445

Access Linux Hosts from Windows

Outline

  1. Samba Package
    • Is Samba Package Installed?
    • If not, install Samba Package
  2. Configuration
    • /etc/samba/smb.conf
      • netbios name
  3. Start and Enable Services
    • smb
      • Start & Enable for auto-start
      • Check Status
  4. Firewall
    • Netbios Naming Service
      • UDP port 137
      • TCP port 137
  5. Confirm
    • Linux
      • Confirm listening network ports
    • Windows
      • Confirm windows hosts name resolution

Tasks

Samba Package

Is Samba Installed?

yum
Syntax
sudo yum list installed

Sample
sudo yum list installed | grep samba

Output – Image

source.windows.01.20190709.0847PM

Explanation
  1. Samba Installed

Install Samba

If Samba has not yet been installed, we install it.

yum
Syntax
sudo yum -y install {package}
Sample
sudo yum -y install samba

Samba Configuration

Outline

  1. /etc/samba/smb.conf
    • Domain
    • Host
Editor
Syntax
sudo vi {file}
Sample
sudo vi /etc/samba/smb.conf

Output – Image

smb.conf.02.20190709.0946PM.PNG

Service

Service Start and Enable for auto-start

Syntax
sudo systemctl start {service}
sudo systemctl enable {service}
Sample
sudo systemctl start nmb
sudo systemctl enable nmb

 

Service Status

Syntax
sudo service {service} status -l
Sample
sudo service nmb status -l
Output

service.nmb.status.01.20190709.0913PM.PNG

Explanation
  1. Local Master Browser
    • Our Linux host takes upon the role of a local master browser
    • The role is for a specific subnet based on the Interface’s IP Address

Firewall

Firewall – Open
Syntax
sudo firewall-cmd --zone=public --add-port={port}/{protocol} --permanent

Sample
sudo firewall-cmd --zone=public --add-port=137/udp --permanent
sudo firewall-cmd --zone=public --add-port=137/tdp --permanent

Output – Image

firewall.137.01.20190709.0902PM

Explanation
  1. TCP Port 137
    • Opened
  2. UDP Port 137
    • Opened

Confirmation

Confirm Listening Ports

On Linux Hosts, confirm listening network ports

netstat
Syntax
netstat
Sample
netstat -lu | grep netbios
Output – Image

netstat.listening.01.20190710.0218M

Explanation
  1. The listening ports are listed
    • Network Ports
      • They are udp ports
        • netbios-ns
        • netbios-dgm
    • Services
      • /etc/services
        • Please consult the /etc/services file for corresponding network port numbers for the listed service

 

Confirm Windows Host Name Resolution

Confirm that windows host able to perform name resolution against Linux Hosts

Command Line Tools
Syntax
ping {linux-host-name}
Sample
ping linuxdbhost
Output – Image

ping.windows.02.20190710.0144AM

Explanation
  1. The windows host is able to perform name resolution against the windows host

 

Referenced Work

Vladyslav Baidak
Backend Engineer at Scalified
Devops: Configuring Host Name Resolution
Link
September 24, 2017

References

  1. Samba.org
    • Firewalling Samba
      Link
  2. Centrify
    • Configuring the Samba integration
      • Modifying the Samba smb.conf configuration file
        Link
  3. Stack Exchange
    • superuser
      • What is NetBIOS? Does Windows need its ports 137 and 138 open?
        Link
  4. Rackspace
    • How To
      • Check running services on Linux
        Link
  5. Makandra Cards
    • netstat: show listening ports
      Link
  6. Red Hat
    • Archives
      • Re: What is NMB ?
        Link
  7. OS Technix
    • How To Find The Port Number Of A Service In Linux
      Link

SQL Beautifier

Background

As a Database Administrator get so see a bit of SQL Statements.

Avenues

The SQL gets to the DBA through various avenues, inclusive :-

  1. Developers
    • Submitted to review and execute against Production Database
  2. Monitoring Tools

In quite a few cases, the first thing one has to do is space things out a bit.

 

SQL

Original

Here is our original SQL.


SELECT            dbo.lms_emp_aff_x.aff_netid,            dbo.lms_emp_aff_x.aff_loc,            dbo.lms_emp_aff_x.aff_emp_unique_loc_id,            'A',            @currdate,            ' '        FROM dbo.lms_emp_aff_x        WHERE dbo.lms_emp_aff_x.aff_loc = @loc_passed AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN            (              SELECT location_uid.lu_unique_loc_id              FROM location_uid              WHERE location_uid.lu_loc = @loc_passed AND location_uid.lu_record_type = 'A'           )

SQL Beautifiers

Outline

  1. EverSQL
    • EverSQL Query Formatter
      Link
  2. Free Formatter
  3. Poor SQL
  4. Devart
    • SQL Formatter for SQL Server
      Link
  5. Dan’s Tools
    • sql beautifier
      Link

Choices

EverSQL

Revised

Image

eversql.revised.

Code

SELECT
  dbo.lms_emp_aff_x.aff_netid,
  dbo.lms_emp_aff_x.aff_loc,
  dbo.lms_emp_aff_x.aff_emp_unique_loc_id,
  'A',
  @currdate,
  ' '
FROM
  dbo.lms_emp_aff_x
WHERE
  dbo.lms_emp_aff_x.aff_loc = @loc_passed
  AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN (
    SELECT
      location_uid.lu_unique_loc_id
    FROM
      location_uid
    WHERE
      location_uid.lu_loc = @loc_passed
      AND location_uid.lu_record_type = 'A'
  )

Free Formatter

Revised
Image

freeFormatter.com.20190202.0850PM

Code

SELECT
   dbo.lms_emp_aff_x.aff_netid,
   dbo.lms_emp_aff_x.aff_loc,
   dbo.lms_emp_aff_x.aff_emp_unique_loc_id,
   'A',
   @currdate,
   ' '
FROM
   dbo.lms_emp_aff_x
WHERE
   dbo.lms_emp_aff_x.aff_loc = @loc_passed
   AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN
   (
      SELECT
         location_uid.lu_unique_loc_id
      FROM
         location_uid
      WHERE
         location_uid.lu_loc = @loc_passed
         AND location_uid.lu_record_type = 'A'
   )

Poor SQL

Revised
Image

poorsql.20190131.0808am

Code

SELECT dbo.lms_emp_aff_x.aff_netid
	,dbo.lms_emp_aff_x.aff_loc
	,dbo.lms_emp_aff_x.aff_emp_unique_loc_id
	,'A'
	,@currdate
	,' '
FROM dbo.lms_emp_aff_x
WHERE dbo.lms_emp_aff_x.aff_loc = @loc_passed
	AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN (
		SELECT location_uid.lu_unique_loc_id
		FROM location_uid
		WHERE location_uid.lu_loc = @loc_passed
			AND location_uid.lu_record_type = 'A'
		)

Devart / SQL Formatter for SQL Server

Revised
Image

Code

SELECT
  dbo.lms_emp_aff_x.aff_netid,
  dbo.lms_emp_aff_x.aff_loc,
  dbo.lms_emp_aff_x.aff_emp_unique_loc_id,
  'A',
  @currdate,
  ' '
FROM dbo.lms_emp_aff_x
WHERE dbo.lms_emp_aff_x.aff_loc = @loc_passed
AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN (SELECT
  location_uid.lu_unique_loc_id
FROM location_uid
WHERE location_uid.lu_loc = @loc_passed
AND location_uid.lu_record_type = 'A')

Dan’s Tools – cleancss

Revised
Image

sqlbeautifier.20190131.0818am

Code
SELECT dbo.lms_emp_aff_x.aff_netid, dbo.lms_emp_aff_x.aff_loc, dbo.lms_emp_aff_x.aff_emp_unique_loc_id, 'A', @currdate, ' '
FROM dbo.lms_emp_aff_x WHERE dbo.lms_emp_aff_x.aff_loc = @loc_passed AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN(SELECT location_uid.lu_unique_loc_id FROM location_uid WHERE location_uid.lu_loc = @loc_passed AND location_uid.lu_record_type = 'A')

Summary

 

In our current short list, here are losers and winners.

Losers :-

  1. Dan’s Tool
    • Clean CSS
      • Not SQL focus

Winners :-

  1. EverSQL
    • EverSQL Query Formatter
  2. Free Formatter
    • SQL
  3. Poor SQL

dbfiddle – Constraints

Background

A couple of posts ago spoke about db<>Fiddle. In this post we will start highlighting features that are not available due to security and feature set constraints in a shared environment.

Legend

  1. db<>fiddle

Code

    1. Schema
      • Create
        • SQL Fails
          • SQL
            •  create schema [lab] authorization [dbo] 
          • Error
            •  Msg 15151 Level 16 State 1 Line 1 Cannot find the user 'dbo', because it does not exist or you do not have permission. Msg 2759 Level 16 State 0 Line 1 CREATE SCHEMA failed due to previous errors. 
        • SQL Works
          • SQL
            •  create schema [lab] 
    2. Memory Optimized
      • Table
        • Create
            • SQL Fails
              •  CREATE TABLE [lab].[well] ( [userID] [varchar](100) COLLATE Latin1_General_100_BIN2 NOT NULL ) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA ) 
            • Error
               Msg 41337 Level 16 State 100 Line 39 Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container. 
    3. Delimiter/GO
      • SQL fails
        • Code
          •  select [num] =1 go select [number2] =2 
        •  Error
          •  Msg 102 Level 15 State 1 Line 3 Incorrect syntax near 'go'. 
      • SQL Succeeds
        • Code
          •  select [num] =1 ; select [number2] =2 

Summary

Sharing code is not easy. But!

 

SQL Server Data Tools ( SSDT ) – Error – “The Application is not installed”

Background

Wanted to quickly go over an error that is easy to self trigger when working with SQL Server Data Tools ( SSDT ).

 

Triggering Point

A few days ago I was working with SSDT, and I was informed that one of the plugins is running slow and prompted if I want to disable it.

Error

Error Message

Solution Explorer

Here is the visuals from Solution Explorer.

Text

The project is marked “incompatible“.

The project’s packages are not outlined.

Their usual placement is obstructed by a text that reads “The application is not installed“.

Image

solutionExplorer.01.20190704.0637PM.PNG

Textual

The application is not installed.

 

Remediation

Outline

  1. In SQL Server Data Tools ( SSDT )
    • Extensions and Updates
      • Access Menu Options Tools \ “Extensions and Updates”
      • The “Extensions and Updates” Window appears
        • Left Panel
          • From the left panel, access the Installed\All Tab
        • Middle Panel
          • Choose to sort by “Most Recent”
          • Select “Microsoft Integration Services”
          • Click on the “Enable” button
  2. Restart SSDT
  3. Load Project
  4. Access Solution Explorer window
    • Access Project
    • Right click on Project and choose to Reload it

 

Images

Extensions and Updates

Microsoft Integration Services Project

Pre Changes

extensionsAndUpdates.integrationServices.01.20190704.0645AM.PNG

Post Changes

extensionsAndUpdates.integrationServices.02.20190704.0646AM.PNG

Summary

Please keep good notes of changes you make.

And, be ready to go back and revert if need be.

AWS/RDS – SQL Server – Error – “The EXECUTE permission was denied on the object ‘agent_datetime’, database ‘msdb’, schema ‘dbo’ “

Background

Here is an error I have been wanting to talk about for a while here.

Code

msdb.dbo.agent_datetime

Outline

The agent.date_time function accepts two integer values, date and time.
And, returns the corresponding datetime value.

SQL


use [msdb]
go

declare @date int
declare @time int

set @date = 20190701
set @time = 0

select
        [ts]
            = [msdb].[dbo].[agent_datetime]
                (
                      @date
                    , @time
                )

Output

Output – AWS

Here is the result when we issue command against an ASW/RDS MS SQL Server Instance.

Output – AWS – Image

agent_datetime.aws.ouput.20190705.1257pm

Output – AWS- Textual
Msg 229, Level 14, State 5, Line 10
The EXECUTE permission was denied on the object 'agent_datetime', database 'msdb', schema 'dbo'.

Output – Traditional

Here is expected result.

Output – Traditional – Image

agent_datetime.aws.output.good.20190705.0101pm.PNG

Output – Traditional – Textual
2019-07-01 00:00:00.000