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

 

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!

 

dbfiddle

Background

Reading through online SQL discussions and found out about db<>fiddle.

Code

Here is a sample code.


set nocount on;
SET STATISTICS XML OFF;

if object_id('[dbo].[customer_dadeniji]') is not null
begin

    drop table [dbo].[customer_dadeniji]

end

if object_id('[dbo].[customer_dadeniji]') is null
begin

    CREATE TABLE [dbo].[customer_dadeniji]
    (
       [id] [INT] NOT NULL
                  identity(1,1),

       [guid] uniqueidentifier NULL,

        [CHAR](6) NULL,

       [firstName] [VARCHAR](100) NULL
            INDEX [IX_FirstName] NONCLUSTERED,

       [lastName] [VARCHAR](100) NULL
            INDEX [IX_LastName] NONCLUSTERED,

       [address1] [VARCHAR](150) NULL,

       [address2] [VARCHAR](150) NULL,

       [zipCode] [CHAR](5) NULL,

       [countryCode] [CHAR](3) NULL
            INDEX [IX_CountryCode] NONCLUSTERED,

       [lastPurchaseDate] [DATE] NULL,

       [totalPurchasesAmount] [FLOAT] NULL,

       constraint [PK_Customer_dadeniji]
       primary key
       (
          [id]
       )
    );

end

INSERT INTO [dbo].[customer_dadeniji]
(
    [lastname]
  , [guid]
)
SELECT
        tblSO.[name]
      , NEWID()

FROM sys.objects tblSO

INSERT INTO [dbo].[customer_dadeniji]
(
    [lastname]
  , [guid]
)
SELECT
        'smith'
      , CAST(NEWID() AS VARCHAR(50))

SET STATISTICS XML ON
;

select [lastname]

from   [dbo].[customer_dadeniji] 

where  [lastname] = 'smith'

drop table [dbo].[customer_dadeniji];

SET STATISTICS XML OFF
;

db<>Fiddle

URL

dbFiddle is available here.

Database Platform Supported

As of 2019-07-07, here are some of the database platforms supported :-

dbSupport.20190707.0815PM

SQL Server

SQL Server - v2017

Sample Code

Our little sample code is available here.

Image

dbo.customer.20190707.0807PM

Explanation

We see the query and results of issuing the run command.

The query's output includes both the data returned and query plan.

We have the query plan because we invoked "SET STATISTICS XML ON" before issuing our select clause.

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

Win OS – Error – “Certificate for local system with Thumbprint is about to expire or already expired”

Background

Want to quickly deal with a MS Windows Error.

The error is logged in the Event Viewer and it reads :-

Certificate for local system with Thumbprint is about to expire or already expired.

Event Viewer

Windows Logs

Application

List

List Events
Image

autoEnrollment.20190702.0802PM

Tabulate
  1. Log Name :- Application
  2. Source :- CertificateServicesClient-AutoEnrollment
  3. Event ID :- 64

Trouble Shooting

PowerShell

Outline

  1. Issue Directory command against local machine’s certificate repository
    • Specifically check machine, not services nor user
  2. Return Sorted By
    • Display the following fields
      • Subject
      • Thumbprint
      • Expire Date

 

Code


Set-StrictMode -Version Latest

Write-Host

Write-host "Certificates in Local Machine Store"
Write-host "==================================="

dir cert:\LocalMachine\my | Sort-Object NotAfter | Foreach-Object `
	{

		$log = "{0}" -f $_.subject;
		Write-host $log

		$log = "`tThumprint :- {0}" -f $_.thumbprint;
		Write-host $log

		$log = "`tExpiry Date :- {0}" -f $_.NotAfter;
		Write-host $log

		Write-host
		#Write-Host

	}

Output

listCertsMachine.20190702.0829PM.PNG

Expiration

We can see that one of our certificates expired at 5 AM this morning.