Transact SQL – STRING_AGG – Error – “Incorrect syntax near ‘within'”

Background Working on a script, but noticed an error when I tried to sort the data using the String_Agg within group clause. Code Let us reproduce using a sample data set. SQL Sample -01 SQL Output Sample -02 Outline If one reviews our output, one will notice that the cities are listed based on the … Continue reading Transact SQL – STRING_AGG – Error – “Incorrect syntax near ‘within'”

Transact SQL :- XQuery – Union – Basic Study

Background Wanted to hopefully do a better job showing how to use XPath or clause. Basket Query Get Apples ( /item/type => apple ) SQL Output Get Pears ( /item/type => pear ) SQL Output Get Apples or Pears ( /item/type => apple or pear ) => Use XPath Union Outline Use XPath Union Each … Continue reading Transact SQL :- XQuery – Union – Basic Study

SQL Server – v 2019 – CTP ( CTP 3.2 ) 

Background Returned to playing around with SQL Server 2019 Community Technology Preview ( CTP ) this week. Issues Ran into issues such as :- Client SQL Server Management Studio ( SSMS ) Engine SQL Server Engine Error :- Msg 155, Level 15, State 1, Line 61 .... is not a recognized... option   Client SSMS … Continue reading SQL Server – v 2019 – CTP ( CTP 3.2 ) 

Transact SQL – Error – “Msg 14607 – Procedure msdb.dbo.sysmail_verify_principal_sp – principal name is not valid”

Background Needing to deploy email notification across a couple of SQL Instances. I am not into clicking through screen shots and so I am going to prepare a Transact SQL Script. Error Ran into an error Image Textual Trouble Shooting The error message states the principal is no good. Command The command that failed is … Continue reading Transact SQL – Error – “Msg 14607 – Procedure msdb.dbo.sysmail_verify_principal_sp – principal name is not valid”

Transact SQL – XQuery – Ancestor

Background Playing around with XML, but dug in a quick sand. XML Here is the XML Image Code Code Code Find Nodes whose element matches specific value SQL Output Pass X PATH :- Child::bar/Attribute SQL Output Find Descendants SQL Output Find Ascendants SQL Output Output - Textual Output - Image Source Code Control Git Hub … Continue reading Transact SQL – XQuery – Ancestor

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 Output Output - AWS Here is the result when we issue command against an ASW/RDS MS SQL Server Instance. … Continue reading AWS/RDS – SQL Server – Error – “The EXECUTE permission was denied on the object ‘agent_datetime’, database ‘msdb’, schema ‘dbo’ “

Transact SQL – Non-ANSI Joins

Background Trying to see which SQL modules uses legacy Non-ANSI SQL Joins. Discovery There are a couple of pathways we can use to find sql code that relies on Non-Ansi SQL Joins. The mediums we will use are :- Code Execute Code Dynamic Management Views System Catalog Views sys.sql_Modules SQL Server Profiler Code Code Execution … Continue reading Transact SQL – Non-ANSI Joins