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

 

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.

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

sp_describe_undeclared_parameters and temp tables

Background

As a very quick follow-up to our last post, let us untie sp_describe_undeclared_parameters from Tableau.

SQL

Temporary Table

Code


use [tempdb]
go

set nocount on
go

set xact_abort on
go

declare @sql nvarchar(4000);

declare @CHAR_TAB char(1)

set @CHAR_TAB = char(9);

begin tran

    create table #sam
    (
        [id] bigint not null
            identity(1,1)
    )

    set @sql = N'select * from #sam '
                + ' where [id] = @P1'

    begin try

            print 'running sql '
            print '------------'
            print @sql

            print ''; print '';

            exec sp_describe_undeclared_parameters
                    @tsql = @sql           

        end try
        begin catch

           print 'Exception'
           print '========='

           print @CHAR_TAB
                    + 'ERROR_NUMBER() :- '
                    + cast
                       (ERROR_NUMBER()
                            as varchar(10)
                       )

           print @CHAR_TAB
                    + 'ERROR_MESSAGE() :- '
                    + ERROR_MESSAGE()

            print ''; print '';

        end catch

while (@@TRANCOUNT > 0)
begin

    rollback tran;

end

Output

Output – Image

temporaryTable.output.20190621.0910AM.PNG

Output – Textual


running sql
------------
select * from #sam  where [id] = @P1

Exception
=========
	ERROR_NUMBER() :- 208
	ERROR_MESSAGE() :- Invalid object name '#sam'.

Global Temporary Table

Code


use [tempdb]
go

set nocount on
go

set xact_abort on
go

declare @sql nvarchar(4000);

declare @CHAR_TAB char(1)

set @CHAR_TAB = char(9);

begin tran

    if object_id('##sam') is not null
    begin

        drop table ##sam

    end

    create table ##sam
    (
        [id] bigint not null
            identity(1,1)
    )

    set @sql = N'select * from ##sam '
                + ' where [id] = @P1'

    begin try

            print 'running sql '
            print '------------'
            print @sql

            print ''; print '';

            exec sp_describe_undeclared_parameters
                    @tsql = @sql           

        end try
        begin catch

           print 'Exception'
           print '========='

           print @CHAR_TAB
                    + 'ERROR_NUMBER() :- '
                    + cast
                       (ERROR_NUMBER()
                            as varchar(10)
                       )

           print @CHAR_TAB
                    + 'ERROR_MESSAGE() :- '
                    + ERROR_MESSAGE()

            print ''; print '';

        end catch

        if object_id('##sam') is not null
        begin

            drop table ##sam

        end

while (@@TRANCOUNT > 0)
begin

    rollback tran;

end

Output

Output – Image

temporaryTable.output.20190621.0910AM.PNG

Output – Textual


running sql
------------
select * from ##sam  where [id] = @P1

Output – Grid

globalTemporaryTable.output.20190621.0923AM

Source Code Control

GitHub

sp_describe_undeclared_parameters/TempTables
Link

Summary

It is provable that sp_describe_undeclared_parameters does not play well with temporary tables.

Yet, it does OK with global temporary tables.

 

Tableau – Error – “Invalid object name ‘#Tableau_8_2_Connect_CheckCreateTempTableCap’.”

Background

Playing around with Tableau and noticed an error recorded on our Microsoft SQL Server Database.

Error

Error Text


 Invalid object name '#Tableau_8_2_Connect_CheckCreateTempTableCap'.

Troubleshooting

SQL

Here is a modified version of the offending code :-


CREATE TABLE [#Tableau_8_2_Connect_CheckCreateTempTableCap] 

([COL] INTEGER

)

SELECT *

FROM [#Tableau_8_2_Connect_CheckCreateTempTableCap]

exec sp_describe_undeclared_parameters
N'INSERT INTO [#Tableau_8_2_Connect_CheckCreateTempTableCap] ([COL])
VALUES (@P1)'

Remediation

Outline

The issue is that sp_describe_undeclared_parameters does not work well with temporary tables.

Things work well when an actual table is used.

Please keep in that the pound sign # in the table name, [#Tableau_8_2_Connect_CheckCreateTempTableCap], means that the table is a temporarily table and it is created in temporary database, tempdb.

SQL


if object_id('[Tableau_8_2_Connect_CheckCreateTempTableCap]') is not null
begin

    drop table [Tableau_8_2_Connect_CheckCreateTempTableCap]

end

CREATE TABLE [Tableau_8_2_Connect_CheckCreateTempTableCap] (
	    [COL] INTEGER
)

SELECT *

FROM [Tableau_8_2_Connect_CheckCreateTempTableCap]

exec sp_describe_undeclared_parameters
          N'INSERT INTO [Tableau_8_2_Connect_CheckCreateTempTableCap] ([COL])
            VALUES (@P1)'

if object_id('[Tableau_8_2_Connect_CheckCreateTempTableCap]') is not null
begin

    drop table [Tableau_8_2_Connect_CheckCreateTempTableCap]

end

Output

parameters

SQL Server, SSMS, and Unicode characters

Background

Here I am fraught with insomnia.

Tried to review statistics on a table, but getting an error.

Error

Cannot find a table or object with the name

Text

Msg 2501, Level 16, State 45, Line 3
Cannot find a table or object with the name “[dbo].[M?_TEST]”. Check the system catalog.

Trouble Shooting

Review Tables

Outline

Let us Identify tables that have Unicode characters in their names.

The steps we took is to look the contents of the sys.tables view.

We compare the contents of sys.tables.name with cast(sys.tables.name as varchar(255)).

SQL


select
            [src]
                =  'sys.tables'

         , tblST.[name]

         , [containsUnicodeChar]
                = case
                        when
                            (
                                tblST.[name]
                                    != cast(tblST.[name] as varchar(255))
                            ) then 1
                        else 0
                   end

from   sys.tables tblST

order by
           [containsUnicodeChar] desc
         , tblST.[name] asc

Output

metadata.20190619.0457PM

Review Primary Key Constraints

Outline

Let us Identify primary key constraints that have Unicode characters in their names.

SQL

select
          [src]
            = 'sys.key_constraints'

        ,  [constraint]
            = tblSKC.[name]

        , [parent]
            = object_schema_name(tblSKC.[parent_object_id])
                + '.'
                + object_name(tblSKC.[parent_object_id])

        , [containsUnicodeChar]
                = case
                        when
                            (
                                tblSKC.[name]
                                    != cast(tblSKC.[name] as varchar(255))
                            ) then 1
                        else 0
                   end

from   sys.key_constraints tblSKC

order by
           [containsUnicodeChar] desc

         , [parent]

         , [name]

Output

metadata.constraints.primaryKeys.20190619.0500PM.PNG

 

Summary

Occasionally, Unicode characters can sneak into SQL object definitions and data.

SSMS does not always clearly indicate Unicode characters.

Please take care.