Displaying Sorted Hierarchies (SQL Spackle) (2024-08-02)

SQLServerCentral Newsletter for August 2, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Displaying Sorted Hierarchies (SQL Spackle)]( - [Redgate Summit: The Database Landscape - New York]( - [Making Temporal Databases Work. Part 2: Computing Aggregates Across Temporal Versions]( - [From the SQL Server Central Blogs - My personal experience dockerizing a Rust Application: Deploy to AWS ECR using Github Actions]( - [From the SQL Server Central Blogs - Friday Flyway Tips–Comparison Defaults]( - [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Question of the Day - [More bad date functions?]( The Voice of the DBA  Responding to a Disaster [Ryan had a planes, trains, and automobiles situation]( a few weeks ago. On the same day, I didn't. A delay for a few hours, but an easy trip home. Lots of other people didn't have a smooth trip, and I had a few friends who spent an extra night somewhere or had flights canceled and decided not to take a trip. The [Crowdstrike outage]( hit the entire world, causing problems everywhere. Just before that happened, [Azure had an outage in the central region](. If you were affected by these, you have my sympathies. If your IT job included responding to these, you get all the virtual hugs from me ([and Brent](. I know what it's like when someone calls you to handle an outage, and I know it can turn your life upside down. Hopefully, it hasn't been too stressful to you. In Brent's post, he talks about reviewing your DR plans and presenting your findings to your boss. Good advice, and worth reviewing yearly as most of our environments will experience some amount of change. If you follow this advice, make sure you present this in a logical, dispassionate way. I see plenty of technology professionals who get upset when DR isn't given enough priority. That doesn't help the situation. Channel your [inner Spock]( and present your results, allowing someone else to decide what to do and accepting their priorities. You can advocate for change, but keep in mind that there is often no shortage of work and limited time. If someone else decides you can accept the risk of poor plans, document that and move on. Plenty of us will experience a disaster at some point. I've had more than a few occur at various positions, and I know many colleagues who have gone through a wide variety of failures. Even for companies that have extensive DR plans, there will be challenges when a disaster occurs. We're certainly not at the point where any AIs can handle reading our DR document and implementing fixes. Instead, those of us responding will have a guide, maybe a great guide, for how to respond, but we will need to adapt our plans to the current situation. Having the mindset that our plans might not be perfect ([as Mike says]( will help you deal with any challenges that arise. Aside from the technical situation, there are also mental challenges in a disaster. You will feel pressure from your employer, but also pressure at home. Your partner might not understand the extra hours. If you miss commitments made to your family, they will be disappointed, and I hope you are as well. You might have expected your schedule to include fun events, and you likely didn't expect to get less sleep. Your diet might suffer in times of crisis and long hours. All of these situations can be managed, but it helps to think about them when you're calm and make plans. Warn friends and family. Think about how to limit unhealthy foods or situations, set expectations with yourself that help you manage stress, and most of all, treat yourself with kindness. Many of us want to help and support our co-workers but ensure that you (and them) get breaks. Imagine you're in a Crowdstrike situation that lasts for multiple days. Talk about how that will look, even with just a few people and you'll be better prepared when a disaster event occurs. Hopefully, it won't be a large-scale event, but better to prepare for that and experience a minor situation than the reverse. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Displaying Sorted Hierarchies (SQL Spackle)]( Jeff Moden from SQLServerCentral.com Learn how you can query a hierarchy of data and also return the results in an ordered fashion. A handy T-SQL skill that you will use over and over again. [External Article]( [Redgate Summit: The Database Landscape - New York]( Additional Articles from Redgate Join Redgate at the last Redgate Summit event of the year! This series of events is hosted across the globe for data professionals who want to improve their skills and knowledge about Database DevOps, learn about topics surrounding the Cloud, AI, and working across multiple databases. [External Article]( [Making Temporal Databases Work. Part 2: Computing Aggregates Across Temporal Versions]( Additional Articles from SimpleTalk In this part 2 we discuss what kind of aggregates can be obtained from a temporal database and how to express these aggregations in the SQL language. [Blog Post]( From the SQL Server Central Blogs - [My personal experience dockerizing a Rust Application: Deploy to AWS ECR using Github Actions]( Adetokunbo Ige from Adetokunbo Ige I have heard about many interesting things about programming in Rust and I decided to use Rust for one of my applications. I enjoyed the programming experience in Rust... [Blog Post]( From the SQL Server Central Blogs - [Friday Flyway Tips–Comparison Defaults]( Steve Jones - SSC Editor from The Voice of the DBA One of the little details that I find matter more and more in enterprises is understanding why a tool behaves a certain way. OSS/home-grown ones often have limited docs,... [Practical Database Auditing for Microsoft SQL Server and Azure SQL]( [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Site Owners from SQLServerCentral Know how to track changes and key events in your SQL Server databases in support of application troubleshooting, regulatory compliance, and governance. This book shows how to use key features in SQL Server ,such as SQL Server Audit and Extended Events, to track schema changes, permission changes, and changes to your data. You’ll even learn how to track queries run against specific tables in a database. ss   Question of the Day Today's question (by Steve Jones - SSC Editor):  More bad date functions? What is wrong with this code? (don't copy it and run it, guess) DECLARE @p NVARCHAR(10) = 'year' EXEC ('SELECT DATENAME(' + @p + ', GETDATE())') Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Managing Backup History Options in MSDB What are my options for managing the amount of data in the backup and restore tables in MSDB? Answer: by date only Explanation: The sp_delete_backuphistory procedure only allows you to manage history based on dates. Ref: sp_delete_backuphistory - [ [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [Dont want to restart Tempdb not able to shrink datafiles in ag primary]( - HI All, Dont want to restart Tempdb not able to shrink datafiles in always on server primary is there any alternative way we can reduce the tempdb files and shrink the space from them . can we run this below command to clear the space from tempdb data files without restart and effect always on […] SQL Server 2016 - Development and T-SQL [How to get a distinct value from my data set]( - Hi, I need help with writing a script that will allow me to pull a distinct value from column A, depending on the value in column B. Here is an example of the data set: Column A | Column B 5000     |  1 5000     |  1 5001 […] [How To Approach Adding A primary Key To An Existing Table]( - In our vendor created/managed DB we have a table that is like an audit table the vendor uses for tracking down issues. It's something you can turn on or off via the application that uses the DB. The table has NO Primary Key. It does have a date stamp like column so we've used it […] [Deadlocks with UPDATE statements using serializable transaction isolation level]( - We are seeing frequent deadlocks occurring due to a particular stored procedure that is using the SERIALIZABLE transaction isolation level. The stored procedure is essentially trying to ensure that the same reference number (concatenated from multiple fields) is never returned more than once. CREATE PROCEDURE dbo.sp_GenerateNextNumber ( @SequenceKey nvarchar(10), @ReferenceNumber nvarchar(25) = NULL OUTPUT ) […] Development - SQL Server 2014 [View works for me ...but doesn't return results for a user in SSMS but no errors]( - Hi I have this view to check if a job is running:  SELECT job.NAME ,job.job_id ,job.originating_server ,activity.run_requested_date ,DATEDIFF(SECOND, activity.run_requested_date, GETDATE()) AS Elapsed FROM msdb.dbo.sysjobs_view job JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id JOIN ( SELECT MAX(agent_start_date) AS max_agent_start_date FROM msdb.dbo.syssessions ) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date WHERE […] SQL Server 2019 - Development [where is my commit size in my pkg]( - hi, while trying to give our operations guys as much info as possible about a separate ssis issue /post we have with losing connections locally and/or run times of 60 x normal when vpn'd, i looked for my commit size under vs 2022 with ssis data tool 16.0.5397.1.  i thought it was supposed to show […] [error in both ssis and ssms - something about losing connections - new error]( - hi for about 4 or 5 days now, i've been seeing various connections (to our dw server) issues in ssis (excel to sql) under vs 2022 AND SSMS. the ssis error is shown below. in ssms it looks like this ...  The connection is broken and recovery is not possible. The connection is marked […] SQL Azure - Administration [How to Migrate SQL Logins Between Two Azure SQL Instances Without sp_helprevlogi]( - Hello everyone, I am currently working on migrating SQL logins between two Azure SQL instances. Unfortunately, I am facing several limitations and would appreciate your help in finding an effective solution. Using sp_helprevlogin: This stored procedure is not supported in Azure SQL. Using dbatools: The dbatools option is not viable in my current environment. HASHED […] General [AWS RDS MSSQL]( - Since AWS RDS MSSQL does not support logon trigger, is there any alternative way to implement same in RDS MSSQL. In existing logic, I created a logon trigger which allows only some selected group to logon through windows authentication, also restrict some applications from accessing mssql. I want to implement the same in AWS RDS […] Anything that is NOT about SQL! [Microsoft Dataverse]( - So having seen the term dataverse thrown around I pictured it as some sort of marketing buzzword. Recently I've been looking at the MS Power Platform and this term pops up all over the place. So I googled it. Wikipedia talked of an open source product written in Java and I wondered if I may […] SQL Server 2022 - Administration [Error fetching data via Linked Server]( - Hi, SS ver: SS 2017, SE; Oracle: 19.23 SE We have a nightly job that connects from our Sql server env to an Oracle database via Linked server that we have created for this purpose. This job had been running fine for the past several years - it pulls data from multiple tables. However, the […] [Use Polybase with ODBC to create external table]( - I'm trying to use the installed Polybase service on an SQL 2019 server to create an external table by using and ODBC DSN. The connection of the DSN is to a fairly exotic BBj server that hosts 3 databases. Somehow I just do not seem to get the proper syntax for creating the external table. […] SQL Server 2022 - Development [Dark mode, other color schemes]( - All, if you are like me and do not care for the built-in color schemes, try www.sqlshades.com. I just downloaded the EXE and instead of me taking an hour or two to find the right colors and without able to change the docked windows colors, I installed it, restarted SSMS and already love the dark […] [Issues adding and updating a column]( - Hi all  I'm hoping someone will able to say "you're an idiot because....." on this one.  We download a database but we have to add a column to a table and then update it. The code to add/update is as follows: IF NOT EXISTS ( SELECT * FROM UK_Health_Dimensions_New.INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_SCHEMA= 'ODS' […] [how can vs see SSIS under my regular user id but not my admin?]( - Hi, we run vs 2022.  I'm stumped how when i run VS as admin i cant see ssis after hitting create new project unless i want to import ssis or tabular.  but under my regular id i can see new ssis, import ssis, import tabular and new ssrs after hitting crate new project.  its been […]   [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -