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

HIPPO vs. Data

One trend for many organizations is to be data-driven. This means using data to make decisions at all levels, or at least support those decisions. This was popularized by many companies, and there is research to back up the claims that those organizations who do this have more success, more revenue, more profitability, etc. Much like DevOps, however, it seems many organizations don't want to actually invest in making data-driven decisions.

Instead, many opt to make decisions by HIPPO. If you've never seen this acronym, it stands for highest paid person's opinion. Many managers will defer to whoever is above them in the org chart, often someone that is paid more money than them. Even when there is data to make a different decision, the HIPPO is still used.

It's not just management. I sometimes see technical people do this. In that case, it might be the loudest or most senior person rather than the highest paid, but the sentiment is the same. Rather than using a lot of information, someone makes a decision based on what they want/see/need/desire. I've seen this often with tooling, where one person like Jenkins (or hates it), and the organization changes from  (or to) Octopus Deploy or Bamboo for no real reason. Lately, it seems some people are advocating moving from Azure DevOps to GitHub Actions because, well, I don't know why. There's not a good rational reason.

The goal of collecting and using data is to do so intelligently and with purpose. And not just for big decisions, but for everyday actions. By senior managers and frontline people, to help guide a more efficient and effective company. This involves a lot of technical work, but also cultural change. We have to position data as a strategic and tactical asset that everyone shares and uses as the basis to make the entire organization better.

Of course, these are similar to the concepts of DevOps. Let's build software better. In practice, many attempts succeed initially but fail later as more and more groups fight individual incentives that promote one group's goals over another's. When silos aren't broken down, we fall back into the same patterns.

In this case, listening to (or lobbying) the HIPPO.

Steve Jones - SSC Editor

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

 
 Featured Contents
SQLServerCentral Article

Using Power BI to Analyze My Travels

Steve Jones - SSC Editor from SQLServerCentral

As a data professional, I have some fun with data in my life. I like numbers, and I like tracking things. I've written posts about my year in numbers, and recently noted the states I've visited. Like many of you, I've sometimes used this data to practice a skill, maybe learn to ETL or query […]

External Article

Sample Use Case for Using SQLCMD Utility Batch Files

Additional Articles from MSSQLTips.com

In this article, we look at a sample use case of how you can create SQLCMD batch files that can be interactive and reused in different SQL Server environments.

PostgreSQL 101: The journey towards PostgreSQL - tips and lessons from Grant Fritchey

Additional Articles from Redgate

Want to hear someone else’s experience of learning PostgreSQL from scratch? Then look no further.

Grant Fritchey, Microsoft Data Platform MVP, started working with SQL Server in 1995 but more recently has needed to support and understand PostgreSQL. In this webinar, he will go on a deeper dive into the PostgreSQL database and will share top tips from his own learning journey.

Join us on July 25th

Blog Post

From the SQL Server Central Blogs - Demonstrating How I Use Voice Access

DataOnWheels from DataOnWheels

As part of my effort to inform people on how to use tools with their disabilities and continue working, I created a series of videos that demonstrate how I...

Blog Post

From the SQL Server Central Blogs - Three more chapters of my data architecture book are available!

James Serra from James Serra's Blog

As I have mentioned in a prior blog post, I have been writing a data architecture book, which I started last November. The title of the book is “Deciphering...

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 Carlo Romagnano):

 

More OUTER APPLY

What does the last query? 
declare @t table(i int) 
insert into @t
SELECT * FROM (VALUES
('1')
,('2')
,('3')
,(NULL)
) AS V([i])


SELECT t.i
      ,MSG.msg
  FROM @t t
OUTER APPLY
(
    SELECT msg = CASE t.i
                    WHEN 1 THEN 'ONE'
                    WHEN 2 THEN 'TWO'
                    WHEN 3 THEN 'THREE'
                    ELSE CAST(1/0 AS VARCHAR(10))
                 END
) MSG

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)

Roles Across Schemas

I ran this code in a new database:

CREATE TABLE dbo.mytable (myid INT)
GO
CREATE TABLE webapi.states (stateid INT NOT NULL  CONSTRAINT statespk PRIMARY KEY, statecode VARCHAR(2), statename VARCHAR(20))
GO
INSERT webapi.states (stateid, statecode, statename) VALUES (1, 'AK', 'Alaska')
GO
CREATE LOGIN apiuser WITH PASSWORD = 'Demo1234'
GO
CREATE USER apiuser FOR LOGIN apiuser WITH DEFAULT_SCHEMA=webapi
GO
ALTER ROLE db_datareader ADD MEMBER apiuser
GO

Next, I log in as the apiuser and change to this new database. I run this:

SELECT TOP 10 
s.stateid, 
s.statecode, 
s.statename
FROM webapi.states AS s;
GO
SELECT myid FROM mytable;
GO

What happens?

Answer: Both SELECTs succeed

Explanation: The user is set with a default schema, though when querying objects that are not schema qualified, the dbo schema is checked. With the membership in db_datareader, all objects across all schemas are included. Ref: db_datareader - https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver16#fixed-database-roles

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
Log Shipping Jobs stop working automatically - Dear Friends, On my Log Shipping configuration , sometimes , more frequently actually, the Backup,Copy and Restore Jobs stop working automatically.. I have to check on them and run manually...and some times the restore has errors which force me to reconfigure it again..Any comments / advises on this will be highly helpful..Thank you.
Trace Flag 3444 - I just discovered trace flag 3444 set on a SQL 2017 srver, and I can't find reference to it. Has anyone come across this trace flag and what is it used for? Thanks MC
SQL Server 2016 - Administration
Need to read ErrorLog to get the END TIME of last restore? - is there a stored proc somewhere for sql server 2016 that can return latest restore database duration in seconds and in minutes for each database on the server? I cannot find a ready code, and the GPT cannot help. it is hard to believe though that there is no working code somewhere. I looked and […]
SQL Server 2016 - Development and T-SQL
WITH RECURSIVE gives error - WITH RECURSIVE factorial(F,n) AS ( SELECT 1 F, 3 n UNION ALL SELECT F*n F, n-1 n from factorial where n>1 ) SELECT F from factorial where n=1   Error Msg 102, Level 15, State 1, Line 26 Incorrect syntax near 'factorial'. Completion time: 2023-07-07T14:16:59.5379702-04:00 If i remove RECURSIVE option it works fine.
SQL Server 2019 - Administration
[SSIS Server Maintenance Job] failed - Executed as user: ##MS_SSISServerCleanupJobLogin##. The SELECT permission was denied on the object 'availability_databases_cluster', database 'mssqlsystemresource', schema 'sys'. [SQLSTATE 42000] (Error 229). any help with this will be appreciated!
SQL Server 2019 - Development
List Month End Dates Between Two Dates - Hi everyone I am working on a query where I need to list all business month end dates between two dates.  The two dates would define the start and end periods but these two dates are dynamically created: DECLARE @START_DATE DATE = DATEADD(YEAR,-1,GETDATE()) DECLARE @END_DATE DATE = GETDATE() I am not sure how to generate […]
sql Convert datype int to string - hallo all, let me ask i have a query convert like this : INSERT INTO STG_RECOVERY (MODEL, VALID_ON, NOREK, KOL_AWAL, KOL_AKHIR, BD_AWAL, BD_AKHIR, BD_KL_AWAL, BD_KL_AKHIR, AMOUNT, RECOVERY, RECOVERY_PCT) SELECT 3, A.validon VALID_ON, A.Acct, A.BIKOLE KOL_AWAL, B.BIKOLE KOL_AKHIR, A.BAKI_DEBET BD_AWAL, B.BAKI_DEBET BD_AKHIR, NULL BD_KL_AWAL, NULL BD_KL_AKHIR, C.BD_KL AMOUNT, A.BAKI_DEBET - B.BAKI_DEBET RECOVERY, ((A.BAKI_DEBET - B.BAKI_DEBET) / […]
getting next date - Is there a way to show the next date that is greater but not have them duplicate like the highlighted below? I am using sql server and doing a select from the columns below where the service date is greater than da discharge date.     This is what I want it to show. It […]
Remove duplicate data - Hi Below is the create table and insert script. I am attaching current output and expected output. USE master GO CREATE TABLE [dbo].[test_07052023]( [EMAILS SENT] [float] NULL, [mmClientID] [float] NULL, [Report Run Date] [nvarchar](4000) NULL, [Issue Name] [varchar](4) NOT NULL, [Issue Year] [int] NOT NULL, [Product] [varchar](17) NOT NULL, [Rate Card Name] [varchar](9) NOT NULL, […]
Integration Services
deploy ssis package to MSDB db in SSIS server using azure pipeline - Has anyone tried deploying ssis to MSDB db in SSIS server using Azure pipeline?  I am used to do the file system deployment using Azure pipeline but not to MSDB db. Din't find any good article on this on the internet.
Anything that is NOT about SQL!
Need recommendations on replacing a NAS device for home use. - In my home office (SQL Server DBA, now a 13-year retiree) I have two NAS devices, a D-Link DNS-343 (4x2TB) and a WD EX4100 (4x10TB). I had to take the WD NAS off my surge protectors due to repeated 'power supply failure' notices, and it has worked since with no problem. My Win 10 machine […]
Administration
Transaction log BU fails in Maintenance Plan fails when there is no Full BU - I have 3 maintenance plans for full backups, differential and transaction log. The job for log backups fails in when there is no full backup. Which is logical of course. But is there a way to prevent this error from happening? I would like an option that it will not try to backup the log […]
SQL Server 2022 - Administration
RAISERROR Serverity 16 Showing in SQL Agent Log - We are evaluating SQL Server 2022 and have upgraded an SQL Server Fail Over Cluster and AG with with CU 4. In order not to run the jobs created by Reporting services on the passive node, we have added a step to check to all SQL Agent Jobs that does a RAISERROR to exit out […]
SQL Server 2022 - Development
SQL Server AOAG Add 3 IP's to LIstener with Powershell Script Not working - Team, Why does the below not work? Using parameters for listener name and ip addresses. When i hard code the IP addresses in there it works even with the listener as a parameter . I assume it has to do with the quotes. Any assistance is greatly appreciated. See full section of code not including […]
Issue with sql server stored procedure--``i need to update multiple columns from - use dB DROP PROCEDURE [dbo].[pre_update] GO SET QUOTED_IDENTIFIER ON CREATE TYPE GetTrackingNo AS TABLE (TrackingNo Int ); go CREATE PROCEDURE pre_update @TVP GetTrackingNO READONLY AS Begin SET NOCOUNT ON; DECLARE @trackingTVP AS GetTrackingtNO ,@col1 VARCHAR (MAX) ,@col2 VARCHAR(100) ,@col3 VARCHAR(MAX) your text insert into @TrackingTVP (TrackingNO) (select distinct TrackingNO from sub where date between '2023-01-01' […]
 

 

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

 

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