SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

A Minor Problem

The Voice of the DBA

Daily Coping Tip

Notice when you’re hard on yourself or others and be kind instead

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.

Never let a good crisis go to waste

There have been quite a few jobs in my life where my first day was complete chaos. Some were technical and some were not. I had a first day as a bartender (in more than one job) where someone was sick or the place was busy and I was left on my own to figure things out. Fortunately, I know my way around a keg and a bottle of liquor. I've also started at a couple tech jobs where we had a crisis in a system. I've been able to pitch in and help solve problems, or even get other work done on my own. In all these cases, the boss was impressed, and I often started out my tenure with quite a bit of credibility.

When we look to make changes, it can be hard to convince someone that the change is better for the organization. This might especially be true when working in software development and trying to convince someone to change their process or habit. I see this all the time as clients look to adopt DevOps, with no shortage of naysayers. There are always plenty of people that want to maintain the status quo.

I heard quote from a presentation by Comcast execs on DevOps: "never let a good crisis go to waste." The quote has been attributed to others in history, but I think it's an apt description of how to drive your processes forward when you know there are problems and issues and no one wants to spend time fixing them. Pain often allows an opening for a better solution to be considered.

Too often when there is no problem, no immediate pain, there is a lack of will from management to spend time improving things. Certainly there are some executives that believe in improvements, but far too many focus on adding something new rather than improving the underlying infrastructure of software development. When you see a problem, if you can't make an improvement at the moment, keep an improvement or solution handy. You never know what a crisis might give you the opportunity to pitch a better way forward.

Steve Jones - SSC Editor

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

Featured Contents

Introduction to PostgreSQL Database on Amazon RDS

aveek22 from SQLServerCentral

This article explains how to get started with PostgreSQL on AWS.

More Gems from the SQL Prompt Treasure Chest

Additional Articles from Redgate

Become the captain of your SQL Prompt ship by embarking on a voyage of discovery through the hidden treasures of the tool. You'll uncover features of the unknown that will enable you to write, format, analyze and refactor your SQL effortlessly. Join this webinar on December 16th to find out more.

Normal Forms

Additional Articles from SimpleTalk

To prevent data change anomalies, a database should be normalized. Did you know that there are 10 normal forms? In this article, Joe Celko reviews normalizing databases including commonly used normal forms.

From the SQL Server Central Blogs - Tracking costliest queries

Brahmanand Shukla from SQL Server Carpenter

Being a Database Developer or Administrator, often we work on Performance Optimization of the queries and procedures. It becomes very necessary that we focus on the right queries to...

From the SQL Server Central Blogs - Azure Synapse Analytics is GA!

James Serra from James Serra's Blog

(Note: I will give a demo on Azure Synapse Analytics this Saturday Dec 5th at 1:10pm EST, at the PASS SQL Saturday Atlanta BI (info) (register) (full schedule)) Great...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	A Minor Problem
Don't run this code, but there is a problem in it. There is a dbo.Day2 table with a datavalue column and no other tables in the database. What is the problem? WITH cteData (lowerbound, upperbound, letter, pwd) AS ( SELECT SUBSTRING(datavalue, 1, CHARINDEX('-', datavalue) - 1) as lowerbound , SUBSTRING(datavalue, CHARINDEX('-', datavalue) + 1, CHARINDEX(' ', datavalue) - CHARINDEX('-', datavalue) - 1) as upperbound , SUBSTRING(datavalue, CHARINDEX(' ', datavalue) + 1, 1) , SUBSTRING(datavalue, CHARINDEX(':', datavalue) + 2, 50) FROM dbo.Day2 AS d) , cteSolution (occ, valid) AS ( SELECT LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) AS occ , CASE WHEN LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) >= d.lowerbound AND LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) <= d.upperbound THEN 1 ELSE 0 END AS valid FROM dbo.cteData AS d) SELECT COUNT(*) FROM cteSolution WHERE valid = 1; GO
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)

A Linked Server Alias

In SSMS, how can I add an alias for my linked server that is different than the name of the SQL Server I am trying to access. In other words, I am on SQL01. I want a linked server to SQL02, but I don't want the name of the linked server to be SQL02. Instead I want it to be FinanceServer.

Answer: Enter FinanceServer as the Linked Server name. Then choose the type of server as Other Data Source and then enter the details for SQL02 in the appropriate boxes.

Explanation: If you leave the default server type as SQL Server, the linked server name needs to be the server name. If you choose the Other Data Source option, you can have a different server name. Ref: SQL: Linked Servers: Don’t hard code server names - https://blog.greglow.com/2017/11/06/linked-servers-dont-hard-code-server-names/

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

Chance data Capture with AG - Since CDC uses log reader to read the changes from the Log. If the SQL server is configured for AOAG group, I was thinking to enable the CDC on secondary replica instead of primary replica. Would you see any issues with this setup? I am thinking Change data capture are not supported on secondary databases that […]

TDE- Transparent Data Encryption related question - Hi Experts, Looking for suggestions on TDE encryption. We have a 3TB database in UAT env. Its in 2-node(node-A, node-B) Alwayson AG. SQL Server version is SQL 2017 EE CU22. We turned on TDE encryption node-A. It took more than 12 hours and completed of about 40% encryption. Meanwhile, unexpected failover occured. we brought it […]

SQL Server 2017 - Development

Extract data with variable column names and order - Hi guys, Just looking for any good suggestions on how to tackle this problem. I have a table of attributes which are held in very wide sparsely populated table (at least it's not EAV!). For display purposes in the application there is a separate table that defines which columns are being used, their display order […]

SQL Server 2016 - Development and T-SQL

Help Transforming TSQL to MSSQL - Hello, I am not great with SQL and the statement I wrote is in TSQL and it is working. I would like this same statement to run in MSSQL, can you please restate it correctly or put me on the right track? It is the With statement that is killing me! I think it needs […]

Enabling FileStream in AG participated Database - Hi team, I was asked to enable FileStream for one of the existing varbinary(max) column in existing table. It is already participating in Avalability Group where we have 1 primary & 1 secondary server. Now as part of the change, i am doing the below Enable FileStream at instance level by enabling it via SQL […]

SQL Server 2019 - Administration

Perf hit for Increasing tran log frequency - we are currently doing a 1 hour tran log backup for a system, I'd like to push that up to 1 minute. Is there a performance hit to be concerned about with doing this? I've gotten some pushback regarding performance and doing tran log backups this frequent. My view is that each hit will be […]

tlog back up and normal restore back migration - Hi Expert, why there is less down time in tail log backup as compared to the normal backup and restore in migration Shree

SQL Server Agent Failing After Windows Shutdown - Hi All~ Where I work, we have Azure SQL Server that we are connecting to through SSMS. I have created several SQL Server Agent Jobs, using my own login as a Proxy. These jobs run a VBS Script (using a command line) that uploads data into our db, creates reports, and moves and manipulates files […]

Database Mail - Times out - Hello All, I have a lovely program called "TreeSize" on my SQL Server that can send an email out via SMTP just fine. I decided to try setting up Database Mail so I can get email alerts when Backups complete, etc. I am following these steps: https://www.mssqltips.com/sqlservertip/1100/setting-up-database-mail-for-sql-server/ Again, my TreeSize Application can send mail no […]

SQL Server 2019 - Development

SQL Spatial - find all locations within X distance of a point. - I have a really stupid question, and I'm betting the answer is "Well, what happened when you actually ran the query?", but I'll ask anyway. (Maybe I just need a bigger spatial dataset). Say I have a center point P that's my location. I can do all the "find the distance" stuff just fine using […]

nested while - CREATE Table TABLEA(N varchar(50),Q int,R int) ALTER PROCEDURE [dbo].[SP](@N varchar(100),@Q int=0,@R int=0) AS BEGIN DECLARE @I int = 1 BEGIN WHILE (@I <= @R) BEGIN WHILE (@I <= @Q) BEGIN INSERT INTO [dbo].[TABLEA] SELECT @N,@Q,@R SET @Q = @Q - 1 END SET @R = @R -1 END END END exec [dbo].[SP] 'NameA',10,2 Current TableA […]

SSRS 2016

Execution Log Clean Up - The task that cleans up out of date execution log entries runs at 0200 every morning. This is not an ideal time for us because we're a 24 hour business and the most important times for are actually overnight. There have recently been complaints raised because SSRS is unavailable for a short period at 0200 […]

Integration Services

SIS gets stuck on data flow task loading screen upon opening project - I'd like to start by mentioning that I have searched for this topic, although there are similar solutions out there, I haven't found one specific to my case and others I've tried haven't worked, so I am seeking any help relating to my issue. In SSIS, Anytime I open a project, whether I import it […]

Anything that is NOT about SQL!

Searching for PII Data in Teams and Outlook - I've been performing a a Proof-of-Concept within our business, showing how Redgate's various tools can help the company to keep a track of PII data and let us use databases that have PII data as the basis for QA and Dev environments. One of the conversations with our InfoSec people raised a question that they […]

PostgreSQL

OLE DB provider "PGNP" for linked server "MyLinkedServer" reported an error - Hi, I am trying to migrate SQL Server databases from 2008 to 2019. My old server has a linked server to PostgreSQL (version 8) I've installed the driver and created the linked server: I've installed the driver and created the linked server: EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'MyLinkedServer', @provider=N'PGNP', @datasrc=N'N'MyLinkedServer', @provstr=N'PORT=5432;CNV_SPECIAL_FLTVAL=ON;', @catalog=N'MyDB' ... The linked […]

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.