Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Daily Coping Tip

Spend a few minutes looking at a piece of art and think about what you enjoy about it

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

My Incredible Journey With Your Help

At Techorama, I saw a keynote from Derek Martin of Microsoft. The talk was called Pain, Grief, Perseverance, and Technology, and David talks about his growth in life from a young man to a Principal Program Manager at Microsoft, husband, and father of six or seven. All the while dealing with a mental illness and learning to ask for help and to manage his busy life.

It's a great talk, though one I only lightly relate to. My life has been mostly easy and wonderful. Not always, but mostly. My problems are often minor ones. As I write this, I had surgery this morning to repair a painful ankle I've been dealing with for a few years. Things went smoothly and I expect rehab to be the same.

My challenge with this issue? Scheduling it when I had a hole in my schedule to get cut and then a follow up. As it is, I arrived home from Brussels yesterday with a little stress about the COVID test and airline delays in getting back. I now fly to Germany in 3 days and then come back to see the doctor. I know, life is hard for me. My wife "gets" to come along and help carry my bags.

Did I mention my life is amazing?

I've had a lot of success in my career. I'm not an expert on tuning like Grant or Erik. I don't know all the ins and outs of Power BI like Patrick or the deep Azure tricks that Denny and Joey constantly amaze me with. I don't make the great presentations that Brent does or the business success of Stacia or the impressive credentials of Dr. Holt. I've learned from all these people and more. They've been there to give me help, whether with a technical problem or guidance in some area of life.

I work hard. I've worked many weeks in my career and at my career beyond the standard 40, or even the common tech 50. I grind away at times, but I've also learned to say no and find balance. My wife and Andy remind me that babies aren't dying because I take time off and that I need to take time off. Tjay provides the inspiration to do that more. I've learned to do that as I age, balancing a busy work schedule with my coaching hobby. I've been lucky enough to work with some wonderful young ladies the last six years and travel with them to various cities to watch them compete, laugh, cheer, cry, and work as hard as I've ever worked.

My kids are healthy and happy. I'm proud of their journeys through life as two of them work on careers and one grinds through university. They are strong and dependable. They find a better balance between work and life, but don't shy away from work and are accountable and responsible. This week, they're taking over the cooking for me and horse chores for my wife as we travel in Europe.

Again, life is wonderful here.

My Mom praises my work ethic in getting through college, finding jobs that grew my career because of my hard work, the effort I made to build SQL Server Central with Andy and Brian and sell it to Redgate. All true, but that's not the whole story. I had luck in people giving me opportunities, supporting me, even helping with the workload at times. I had lots of opportunities, and I took advantage of them. I also had plenty of help.

I helped build something that Redgate and many of you have appreciated and used over the years. SQL Server Central, SQL Saturday, the Networking Dinner at the Pass Summit, and a few other things. However, you all helped me with those things. Many of you took a chance on posting a question or coming to the event. Plenty others of you participated with answers, articles, presentations, volunteer efforts, and more. You have helped me in my journey and I appreciate your efforts alongside my own.

We've been a team, and I thank you for being there alongside me.

I'm on an amazing journey in life, and I'm trying to enjoy more of it even as I keep busy at living it. My "problems" are first world ones, minor bumps on one a road I could never have imagined traveling when I was younger. I work hard, but I know many of you are there to help me often. Sometimes you help me when I don't ask or think I need assistance.

You can find an amazing life for you. Work at life, work at your attitude, work at your balance, work at your skills, and work at asking for help. I, and many others, are also here to help you. We don't achieve anything on our own, but there is always someone else willing to provide an assist.

My life is perfect, thanks to us.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
 Featured Contents
SQLServerCentral Article

Fixing "The Distributor has not been installed correctly" Error with AWS

Dinesh Karunarathna from SQLServerCentral

When migrating a database from SQL Server on premises to AWS, you might receive this error. Learn how to get rid of it.

External Article

SQL Database Overview

Additional Articles from MSSQLTips.com

Learn about what a SQL database is, what is stored in a database and how to interact with a database.

External Article

SQL Monitor supports Amazon RDS Monitoring

Additional Articles from Redgate

SQL Monitor works with your SQL Server data, wherever that data exists, from local instances to AWS RDS and AWS virtual machines. You can keep an eye on your estate, regardless of where the data is.

Blog Post

From the SQL Server Central Blogs - Power BI: Calculating Network Days in M Custom Columns

DataOnWheels from DataOnWheels

Knowing the days between events is a fairly common reporting request because a lot of reporting is created to track SLA’s (service level agreement) and other KPI’s (key performance...

Blog Post

From the SQL Server Central Blogs - Seeding an Availability Group Replica from Snapshot

aen from Anthony Nocentino Blog

Background If you’ve been using Availability Groups, you’re familiar with the replica seeding (sometimes called initializing, preparing or data sychronization) process. Seeding is a size of operation-based data, copying...

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

The First LEAD

I have a query that uses LAG to calculate a sales difference. There is a part of the code that is like this:
LAG(saletotal) OVER (PARTITION BY saledate ORDER BY saledate) - SUM(saletotal) OVER (PARTITION BY saledate ORDER BY saledate) AS hrchange
However, the first row shows a null for this query. What should I do to make this a zero when the LAG value doesn't exist?

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)

The Long Hash

What are the output lengths for these HASHBYTES queries on SQL Server 2019?

DECLARE @i VARCHAR(4001) = REPLICATE('a', 2001)
, @j VARCHAR(4001) = REPLICATE('a', 1001)
, @k VARCHAR(4001) = REPLICATE('a', 4001);

SELECT
len(HASHBYTES('SHA2_512', @i))
, len(HASHBYTES('SHA2_512', @i + @j))
, len(HASHBYTES('SHA2_512', @i + @j + @k))

Answer: All are 64 bytes

Explanation: The output on SQL Server 2019 is dependent on the algorithm chosen, not the input. For AES_512, 64 bytes are output. Ref: HASHBYTES - https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15

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 - Development
How to get correct Part Feature Number when Part Exist ? - I work on sql server 2017 I have table #partsfeature already exist as below   create table #partsfeature ( PartId int, FeatureName varchar(300), FeatureValue varchar(300), PartFeatureNumber int ) insert into #partsfeature(PartId,FeatureName,FeatureValue,PartFeatureNumber) values (1211,'AC','5V',1), (2421,'grail','51V',2), (6211,'compress','33v',3)   my issue Done For Part id 3900 it take wrong Part Feature Number 7 and Correct Must be 2 Because Feature name and Feature Value […]
How to make select Dense Rank over max of TechnologyId Based on FeatureString Fo - I work on sql server 2017 i have table have dense rank over FeatureString and column store rank increment is technology id as below create table #partsfeature ( PartId int, FeatureName varchar(300), FeatureValue varchar(300), FeatureString varchar(300), TechnologyId int ) insert into #partsfeature(PartId,FeatureName,FeatureValue,FeatureString,TechnologyId) values (1211,'AC','5V','AC(5V)Boil(10v)Temp(5V)',1), (1211,'Boil','10v','AC(5V)Boil(10v)Temp(5V)',1), (1211,'Temp','5V','AC(5V)Boil(10v)Temp(5V)',1), (2421,'grail','51V','Alc(5V)Coil(9V)grail(51V)',2), (2421,'Coil','9V','Alc(5V)Coil(9V)grail(51V)',2), (2421,'Alc','5V','Alc(5V)Coil(9V)grail(51V)',2), (6211,'compress','33v','compress(33v)heat(90v)push(80v)',3), (6211,'heat','90v','compress(33v)heat(90v)push(80v)',3), (6211,'push','80v','compress(33v)heat(90v)push(80v)',3)   Now max […]
SQL Server 2016 - Administration
Restore stalls around 97% done with PREEMPTIVE_OS_WRITEFILEGATHER - Hello experts, I'm having trouble getting around this issue. I am trying to restore a database and every time (via T-SQL or GUI), it stalls out at around 97%. I check sp_WhoIsActive and keep seeing this wait type: PREEMPTIVE_OS_WRITEFILEGATHER I tried the solution here but to no avail: Cannot bring the Windows Server Failover Clustering […]
SQL 2012 - General
help needed. MS SQL Server 2012 - Hi, I've just got a call (6pm on a Friday!) from a customer who has had his line-of-business application provider sell him a solution which required SQL Server 2012 Standard, yet the supplier (joyfully) installed the evaluation version of SQL Server 2012 Enterprise. This has now ticked over and expired. My problem is that (a) […]
SQL Server 2019 - Administration
old backup files - Is there any industry standard term/phrase in the DBA community for old backups of SQL databases that have seemingly been left and forgotten about, that could pose a compliance risk around GDPR, or any other data retention element of the data regulations you are subjected to you in your roles.  I was thinking it could […]
Ola Hallengren and GCP - Hi, Looking to move to GCP IAAS VMs running SQL Servers. Do the Hallengren script work backing up directly from a Google Cloud Platform hosted SQL instance to a GCP Cloud Bucket? I know it works within Azure just cannot find anything that confirms it does in GCP. If it does anyone have insight into […]
SQL Server 2019 - Development
Table level increament backup - Hi Expert, How i can take table backup and restore again in sql server when more data is available at the time of import create table table2 (col1 date, col2 char) insert into table2 values('2022-02-02',22) then took backup at the time of restore found 2 more incremental records in table2 insert into table2 values('2022-02-03',22), ('2022-02-04',22) […]
aggregate by a given period - Hello! Please, consider the following code DECLARE @SNH TABLE ( cntDT DATETIME, cntQ varchar(10), cntL int ) INSERT INTO @SNH (cntDT, cntQ, cntL) VALUES ('2001-04-04 10:00:00', 'Queue01', 3), ('2001-04-05 10:03:00', 'Queue01', 1), ('2001-04-05 10:06:00', 'Queue01', 1), ('2001-04-05 10:07:00', 'Queue01', 1), ('2001-04-05 10:11:00', 'Queue01', 1), ('2001-04-05 10:12:00', 'Queue01', 10) I'd like to have the table @SNH […]
Amazon AWS and other cloud vendors
Pragmatically update an AWS Lambda VPC info -   I'm looking for an example code that uses boto3 that can update an existing AWS Lambda function's VPC info. I need to write a code so that every time the lambda function is redeployed, the python script will be triggered and it will update the lambda function that just got deployed. I don't want […]
General Cloud Computing Questions
Cloud hosting suggestions? - Hello again,   I've been running my SQL Database locally through my computer but am looking to move it to the cloud. I've been looking at Google Cloud services, AWS, Azure, and Elephant SQL hosting, but was hoping for some input from some of you more experienced people on what products you've found work best. […]
Reporting Services
SSRS report rendering to PDF is failing. - PDF rendering stopped working for no apparent reason. No changes were made. Users exported PDFs on Thur, but stopped working on Friday. Fails in Edge and Chrome. Doesn't matter what report could be 20 pages or a single record with 3 columns. Error from event log: Microsoft.ReportingServices.ReportProcessing.UnhandledReportRenderingException: An error occurred during rendering of the report. […]
How to see username of the user running the report in Profiler? - I'm trying to configure shared data source of SSRS, on SQL Server 2012, so I can see in Profiler and Activity Monitor who is actually running the report using this data source. I was able to achieve it by specifying Windows Authentication in the shared dataset: when the user runs a report using this datasource, […]
SSRS 2016
Page Navigation Buttons Do Not Work on Second + time loading reports - I am perplexed by an SSRS issue I haven't seen before. I've been a report developer for over 10 years, and work for a company that recently upgraded their reporting server from 2016 to 2019. Some of the older reports that return more than one page worth of data have the page navigation buttons disabled. […]
General
SQL drop -   DROP TABLE IF EXISTS OARD, and a correlated subquery with a grouping that would give the total of adding all the null and blank votes, but I'm a bit lost. Thanks for the help!
PostgreSQL
newbie db design question - hello! really noob question here. i have this experimental database design: create table products ( product_id serial primary key, description text, supplier_id????) ; create table supplier ( supplier_id serial primary key, description text) ; the products table should be linked to the supplier table via "supplier_id" column. i can't find out what would be the […]
 

 

RSS FeedTwitter

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

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -