SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Indexing Computed Column

Featured Script

List Extended Database properties for all databases

The Voice of the DBA

It's Natural to Avoid Problems

The hardest part of transforming to a lean, efficient process is the culture change necessary to get a team to work together. It doesn't matter if the team builds software or vehicles, the change to working in a new way, into disclosing problems, admitting issues, working without blame to fix them, these are very, very difficult changes to make.

Dr. Edward Deming and his work with the Japanese have been the source of many ideas on how to improve a manufacturing process and increase quality. The ideas and principles are incorporated into The Goal, a novel that inspired The Phoenix Project. I recommend both those books for people interested in DevOps because building software is very similar to the manufacturing process used for other goods.

Toyota has been one of the leaders of quality manufacturing and the Toyota Production System has been used as a model and adapted by many industries, including software. One of the ideas used in manufacturing is the ability of anyone to highlight potential problems. In car plants, this has been implemented with an andon cord, a way to stop production and have others help diagnose and solve a problem early in the process.

Some have thought this is a culture issue where the Japanese might be more willing to highlight problems and work as a team, something that many Westerners have struggled to do. At the lean blog, they talk about how this isn't the case. In fact, the Japanese, in general, seek harmony and might be less likely to raise potential issues. In fact, I think most workers are hesitant to raise potential issues, for a variety of reasons. Often anything that slows down work is frowned upon by management, which often leads to issues lingering on, or substandard products being produced. This also happens in software.

I might argue that changing culture for developers is hard, but even harder for managers. We often don't train managers well, and certainly we don't often have good systems for coordinating work for create and knowledge workers. The systems that help with manufacturing give us a base, but they don't apply quite the same way when your "machinery" is another human. Culture change for DevOps must include management and that means less oversight and interruptions, blameless reviews, and embracing mistakes and small failures. Those are often very hard for managers but necessary if you want to improve your software.

Steve Jones - SSC Editor

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

Featured Contents

Adding a dynamic ColumnId to a DAX Calculated Table

Mohamed I. from SQLServerCentral

Learn how a calculated value can be added to a table with the missing key values filled in.

SQL Change Automation 4 now available with SSMS extension

Press Release from Redgate

The Development component of SQL Change Automation now integrates directly into SQL Server Management Studio as well as Visual Studio. This allows teams to collaborate seamlessly during development, regardless of their preferred IDE. Other new features include new data compare options for PowerShell and static code analysis.

How to Create a Pareto Chart in Power BI Desktop

Additional Articles from MSSQLCity.com

In this tip we look at how to create a Pareto Chart which applies the Pareto principle aka the 80/20 rule using Power BI Desktop.

PowerShell Alert API and Server-level Permissions Overview in SQL Monitor

Additional Articles from Redgate

SQL Monitor 9.1 offers a new PowerShell Alert API to control and fine tune your alert configuration, across a group of servers, and a new Server Permissions Overview table, which summarizes who has access to a server via a “super-privileged” server-level role. Here Redgate's Rebecca Woof dleves into the details.

From the SQL Server Central Blogs - Using the SSIS Term Extraction for Data Exploration

Tim Mitchell from Tim Mitchell

Data exploration is an essential piece of any new ETL (extraction-transformation-load) process. Knowing the structure, type, and even the semantics of data sources can help the ETL developer or...

From the SQL Server Central Blogs - Simple Graph Queries

Diligentdba 46159 from Mala's Data Blog

In this post we saw how to create some graph tables with data. In this I will explore simple queries off of this data and how they compare with...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Indexing Computed Column
I have a column with names. I make a non persisted computed column with the length of the field. with this code: ALTER TABLE dbo.Customer ADD CustomerNameLen AS LEN(CustomerName) I want to index the length of the field. If I create this index CREATE INDEX Customer_NameLength ON dbo.Customer (CustomerNameLen); Will this work?
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)

Default Error Logs

When I install SQL Server 2017, how many error logs are kept by default?

Answer: 6

Explanation: There are six error logs kept by default. The current one and 5 previous ones. Ref: Configure SQL Server Error Logs - https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/scm-services-configure-sql-server-error-logs?view=sql-server-2017

Discuss this question and answer on the forums

Featured Script

List Extended Database properties for all databases

Ivan Makariev from SQLServerCentral

I am using database properties to add some useful information for each database. Tired of exploring each database from the menu in SSMS to look for specific information, I decided to make a script listing the extended properties for all my databases. I am pleased to offer this to the community

CREATE PROC ListExtendedDBproperties
as
CREATE TABLE #PropList
(
class_desc varchar(50),
DBname varchar(100),
PropertyName varchar(100),
value sql_variant
)
DECLARE @dbname varchar(50)
DECLARE @cmd nvarchar(1000)
DECLARE c1 CURSOR FORWARD_ONLY FOR
SELECT name as DBname FROM sys.databases
OPEN c1
FETCH c1 INTO @dbname
WHILE (@@fetch_status = 0)
BEGIN
SET @cmd = 'INSERT INTO #PropList
(class_desc, DBname, PropertyName, value)
SELECT class_desc, ''' + @dbname + ''' as DBname,
name as PropertyName,
value
FROM [' + @dbname + '].sys.extended_properties
WHERE class = 0 '
EXEC (@cmd)
FETCH c1 INTO @dbname
END
CLOSE c1
DEALLOCATE c1
SELECT * FROM #PropList ORDER BY DBname

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

Why did the SPN's disappear? - Ran into a very puzzling issue this morning: a 2017 SE server suddenly lost all its SPN's, and I can't figure out why or how. Background: The service account was changed on 7/18, and the SPN's under the old account were deleted and new SPN's under the new account created. All was well. On 9/27, […]

Guide on MySQL server - I am working on a small company and still new to production of a mobile application. I am currently building a mobile application using .net web api and I have decided to use MySQL server as my database and may i ask is it correct if I just install MySQL server on my windowns laptop? […]

SQL Server 2017 - Development

Recursive CTE/Bill of Materials question -- how to get only lowest level? - I'm trying to sort out someone's Bill of Materials process that's happening half in Access VBA and half in SQL Server, so if I sound confused, well, I am. I'm trying to return a "shopping list" of all the components necessary to build everything in the work order. (If you use the AdventureWorks2017 database as […]

SQL Server 2016 - Administration

Ola Hallengren backup failing with syntax error - Hi experts, Just curious if anyone has a quick answer to this. If not, I will email Ola Hallengren about it. I set up Ola Hallengren's Database Maintenance Solution Version date: 2019-06-14 00:05:34. I tested out the scheduled full user database backup. It failed with this error: Executed as user: ... . Incorrect syntax near […]

Point in Time Restore from only Full Backup - We take a Daily Full Backup, a Differential Backup every 4 hours, and Log backups every minute. We retain Full and Diff Backups for 1 week, and Log backups for 24 hours. I understand that to do a point in time restore for today, I'd restore the Last Full Backup, the latest Diff Backup, then […]

High Avilabilty Secondary Server Error: 976, Severity: 14, State: 1. - Error Occurred in Secondary Server But both Primary and Secondary Server Data Synchronized No Data Loss The target database, 'DBName', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and […]

SQL Server 2016 - Development and T-SQL

SQL Server 2016 timeout weird error - Dear All, I have a win form c# 4.5 app which connects to SQL server 2016 DB, most of the functions work correctly. For one of the functions, we make a simple 'select col1,col2 from table' query (datareader) we get below error Inner exception:: System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out. The same works fine […]

Time difference based on values within other columns - Can you help? I have a table like below. ID SrNum Status Time 1 1 Begin 9/29/2019 9:00:05.656 AM 1 2 Request Info […]

Administration - SQL Server 2014

ssisdb in alwayson - Good morning all, We have implemented the following architecture: Server A: SQL 2014 Entreprise + Integration Services Server B: SQL 2014 Entreprise+ Integration Services We created an AlwaysOn availability group (Basics) using both servers as replicas, and protecting the SSISDB database. The problem we are having is that during a flip-flop, we have to reopen […]

RHS crash - We had an outage where RHS windows cluster service got restarted because the file share witness was not accessible. We are trying to replicate it on test environment but failed to do so. Environment: windows server 2012 R2 standard SQL 2014 Appreciate if anyone can guide me in the right direction on how to replicate […]

Development - SQL Server 2014

Sql script Transpose Multiple Column into Rows - Hi i've the current situation DtCont | DtCont | DtCont | --------------------------------------------------------- "20180229"|"20180330"|"20180428" and i need the follow result |DtCont | ------------------------------------------ |"20180229"| |"20180330"| |"20180428"| With Unpivot is not possible,only solutuon i find is create a sequente of select with union select a.PM_DataContabileBKI as r0 […]

SQL Server 2008 - General

Adding a formula to Computed Column Specification formula in SQL Server 2008 - I am trying to add the following query to the formula property to calculate minutes to hours. (CONVERT(char(5), DATEADD(MINUTE, convert(int,[sun_total])), '19000101'), 108) as MinuteToHour) If i run it using a Select statement t it works fine but when I add it to the formula box without the SELECT it errors. The sun_total = 150 […]

Integration Services

SQLServerCentral.com Announcements

Status Update 30 Sept 2019 - SPAM Reporting - It's been awhile since I posted, mostly because we transitioned out of migration mode to ongoing support mode. There is still some work being done, but it's prioritized around other work on other sites. There are two good changes made this past week. HTTP/2 is now being used, which should speed up some of the […]

SQL Server 2005 Integration Services

Determine user SSIS package run as - I have an SSIS package that is failing. The SSIS package was built in Visual Studio 2005, and is run from a SQL Agent Job on a server running SQL Server Management Studio 2005. The job uses a FileWatcher task to monitor an FTP site (accessed via a network share) and looks for xml files […]

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.