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

The Database Giveth and Taketh Away

How many of you have ever run a database query with the wrong parameters and produced a report that you sent to fulfill a request? How quickly did you send another note to ask the report be discarded as a new one was coming? Probably a few of you. I know I have been in that situation during my career. If not a bad query, perhaps some of you incorrectly gathered data from other sources or made the mistake of killing the wrong session or updating the wrong data. Database errors occur, and often we have to decide how to pick of the pieces from a mistake.
 
Recently there was a fairly high profile database query error with Southwest Airlines. Apparently a mass email went out to let customers know they had been awarded the valuable “Companion Pass” from the company. Many customers with a heavy travel schedule aim for this to allow a companion to travel for free with them. After receiving the email, many contacted the company as they didn’t expect the award. As someone that travels a lot, I know most of us do know exactly when we achieve some status and when we’re short of the level.
 
Southwest admitted this was a “database error” and gave away a few coupons to customers, but this an embarrassment as well as an annoyance for customers. Most probably got the notice and didn’t feel bad when it was taken away, but a few people were probably close enough to try to change plans and book another person, only to find they weren’t getting a reward. I know, this is a first world problem, but still somewhat embarrassing for Southwest IT.
 
I doubt this was a database error, unless a DBA updated some rows incorrectly. That’s certainly possible as an UPDATE with the wrong (or missing) WHERE clause is a common occurrence. My guess is that this was likely either a bug in some software that posted mileage completed or it was a poorly written query used to send out emails to customers. In any case, this likely wasn’t a big issue, but it is something that has occurred before. Sometimes with worse consequences.
 
There will always be data entry errors, and certainly query mistakes in what we run. I don’t know of how we’ll eliminate more of these mistakes, other than to create more alerting that looks for anomalies and let’s someone know to double check that the values are correct. Actually, to me this is one area where AI is useful. It can detect things that look wrong, as it does with spell check or next word suggestions, and then let a user decide to keep or change the data. That might be something we would appreciate in SSMS. It could offer to correct my “selcet” to “select” while also letting me know that setting a price to $150 might be wrong when all other prices are $1500 or great.

Steve Jones - SSC Editor

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

 
Redgate SQL Prompt
  Featured Contents

Using Backup Encryption with an External Certificate

Steve Jones - SSC Editor from SQLServerCentral

Learn how to load an external certificate into SQL Server to encrypt your backups.

Tracking the number of active sessions on a database using SQL Monitor

Additional Articles from Redgate

Phil Factor creates a simple custom metric to track the number of sessions that have recently done a read or write on a database. Having established a baseline for the metric, you’ll be able to spot, and investigate the cause of, any wild deviations from normal behavior.

Reporting Services Basics: Overview and Installation

Additional Articles from SimpleTalk

SQL Server Reporting Services has changed quite a bit since it was introduced in 2004. Despite new analytic services like Power BI, SSRS is still a popular tool for paginated reports. In this article, Kathi Kellenberger explains the architecture of SSRS and walks you through installing an SSRS development environment on your workstation or laptop.

SQL in the City Summits – UK, US & Down Under

Press Release from Redgate

Redgate are inviting senior data professionals to attend one of the upcoming SQL in the City Summit events taking place in April, May and June. If you’re interested in learning how your business can benefit from implementing Compliant Database DevOps this event is for you. Find out who’s presenting and register for a Summit near you today. Register now

From the SQL Server Central Blogs - Window Functions vs GROUP BYs

Bert Wagner from SQLServerCentral

There are many options available for improving the performance of a query: indexes, statistics, configuration settings, etc… However, not all environments allow you to use those features (eg. vendor…

From the SQL Server Central Blogs - Database Fundamentals #21: Using the JOIN Operator, OUTER JOIN

Grant Fritchey from SQLServerCentral

The OUTER JOIN returns one complete set of data and then the matching values from the other set. The syntax is basically the same as INNER JOIN but you…

 

  Question of the Day

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

 

Creating a DDL Trigger

What is the DDL For creating a DDL Trigger (a trigger that fires on CREATE TABLE or a similar DDL statement)?

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)

dput()

I have this dataframe in R:

> HR.hitters
   rank          players  Hr yrs.played
1     1      Barry Bonds 762         22
2     2       Hank Aaron 755         23
3     3        Babe Ruth 714         22
4     4   Alex Rodriguez 696         22
5     5      Willie Mays 660         22
6     6 Ken Griffey, Jr. 630         17
7     7    Albert Pujols 633         22
8     8        Jim Thome 612         22
9     9       Sammy Sosa 609         18
10   10   Frank Robinson 586         21

What does this code do?

> dput(HR.hitters)

Answer: This deparses the object to the client

Explanation: This function, dput() is designed to deparse an object and write a text representation to the screen or a file. In this case, the output is similar to this:

structure(list(rank = 1:10, players = structure(c(4L, 6L, 3L, 
2L, 10L, 8L, 1L, 7L, 9L, 5L), .Label = c("Albert Pujols", "Alex Rodriguez", 
"Babe Ruth", "Barry Bonds", "Frank Robinson", "Hank Aaron", "Jim Thome", 
"Ken Griffey, Jr.", "Sammy Sosa", "Willie Mays"), class = "factor"), 
    Hr = c(762, 755, 714, 696, 660, 630, 633, 612, 609, 586), 
    yrs.played = c(22, 23, 22, 22, 22, 17, 22, 22, 18, 21)), row.names = c(NA, 
-10L), class = "data.frame")

  Ref: dput - https://stat.ethz.ch/R-manual/R-devel/library/base/html/dput.html

Discuss this question and answer on the forums

 

Featured Script

Add working days but avoid holidays

steve 14359 from SQLServerCentral

A function to add or subtract working days taking into account weekends and using a table of non-working days.

CREATE FUNCTION [dbo].[fns_AddWorkingDays]
(
@StartDate datetime,
@N INT
)
RETURNS datetime
AS
BEGIN
-- This ensures that however the server is configured for dates
-- the function will know the DATEPART(DW values for Saturday
-- and Sunday
declare @SaturdayDW int
declare @SundayDW int
set @SaturdayDW = DATEPART(DW,CONVERT(datetime,'2019 January 5')) -- A Saturday
set @SundayDW = DATEPART(DW,CONVERT(datetime,'2019 January 6')) -- A Sunday
-----------------------------------------------------------------
-- If @N is zero then reduce the date by 1
-- and try adding one day
if @N=0
begin
set @N=1
set @StartDate=DATEADD(DAY,-1,@StartDate)
end
----------------------------------------------------------------
-- If @N >=0 then increment dates while counting
-- If @N =0 set @increment = 1 else set @increment = -1
-- Work out what to do if the loop encounters
-- a Saturday or Sunday - it depends on the
-- direction of travel.
declare @saturdayadjustment int
declare @sundayadjustment int
if @n>=0 set @saturdayAdjustment = 2 else set @saturdayAdjustment = -1
if @n>=0 set @sundayAdjustment = 1 else set @sundayAdjustment = -2
----------------------------------------------------------------
declare @CountDays int
set @CountDays=0
declare @LoopDate datetime
set @LoopDate = @StartDate
while @CountDays<ABS(@N)
begin
set @LoopDate=DATEADD(DAY,@increment,@LoopDate)
if DATEPART(DW,@LoopDate)= @SaturdayDW
begin
set @LoopDate=DATEADD(DAY,@saturdayAdjustment,@LoopDate)
end
if DATEPART(DW,@LoopDate)= @SundayDW
begin
set @LoopDate=DATEADD(DAY,@SundayAdjustment,@LoopDate)
end
if exists(select HolidayID from tblHoliday where HolidayDate=@LoopDate)
begin
set @LoopDate=DATEADD(DAY,@increment,@LoopDate)
end
set @CountDays=@CountDays+1
end
return @LoopDate

END

More »

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.


Unable to Successfully Connect Using SQL Server Alias - I’ve tried on four different SQL instances on three different computers to configure a SQL Server alias and then connect to that SQL instance using SSMS on the machine that the alias was configured on. Each time I get the standard error message from SQL Server when it can’t find the SQL instance name being […]
Is there a way to walk "up" from current XML node -
AG Setup - I was thinking to configure Always on to 2 different secondary, one in DR Async  and one in same site with Sync mode and then configure replication from secondary replica. I was thinking this would not be a best option when there is a fail-over  the replication redirection would be question? However, checking to see […]
Using Cursor in DELETE statement - I have a need to use a SQL Cursor in order to delete data from a table, one row at a time (to prevent issues with a trigger associated with the table being deleted from).  I wrote the following SQL, and while it is not producing any errors, it is running very long and eventually […]
Reports - Run time of reporting query is less when running directly against the SQL Server Vs Reporting tool. So running reports from third party reporting tool takes more time. Have you came across this problem. Any advise?
Connection to Integration Service Failed - I am using a trial of SQL Server 2017.  SSIS worked. Untill i change Log On As to Local Service. Please help?
Parse In/Out Times into one or more Shift Codes - Once again, I come to rely on this helpful community. For a while now, I have been struggling with a problem that (even if it is more or less solved with application procedural code) would greatly benefit if it could be solved at the database level. Explaining the business case is a bit difficult but […]
Incrementally loading a very large Dimension table – Performance problems - Scenario:  I have a very large type-1 dimension table (50+ million rows and 15+ GB with page compression) in a SQL Server 2016 SP1 64 bit Enterprise Edition database loaded from an SAP data source using Theobald Xtractor.  I am required to perform both INSERTs and UPDATEs on this dimension daily which I do by […]
Status Update 24 Apr 2019 - We had a few days off for the Easter holiday and I got busy with other work yesterday, but a little progress. First, we still are short a developer as there is a delay in bringing on another resource and a couple internal resources got pulled for some higher internal priority stuff. The nature of […]
SQL VIEW works on its own but not when combined - Hi there, I have around 12 views and that works perfect if I execute them individually but when I combine them using UNION ALL it takes long time to execute and results in a time out error. Each view is bit complicated and brings back huge volume of data and they are grouped Please throw […]
Master Data Management (MDM) Tools by Microsoft - I have different data sources, applications having similar customer data. I want to sync them across them so that we do not have to find one customer in specific datasource/application and place all customers profile under one master database. How do Microsoft provide solution for this? any MDM DB tool y Microsoft available? Please share […]
AOAG endpoint won't connect - Hi, i’m not able to change the status of my hadr_endpoint do “connected”. It always show the status “disconnected”. I droped the endpoint, changed the endpoint owner, and changed the connect permission to the user, who starts the sql servives. But nothing change. Had someone any ideas, what to do? I won’t setup the AOAG […]
Splitting a multi-dimensional Array in SQL Server 2012 - Hello, I have data from an Orders table that has 4 columns, in which 2 contain data in a multi-dimensional array (ClassAttendees and ClassTickets). I am having trouble splitting the array in SQL server 2012. I would like to create a child table from the array that referenced the original OrderId, and held Class Attendee […]
Troubleshooting with. wait stats (last 2 weeks included) - I’m troubleshooting a slow SQL Server. Our server team is adamant it doesn’t need more memory or CPU. I’ve been working through the wait stats, but can’t figure out where to hunt next. Here is a link to an Excel sheet with stats, and below is a description of the tabs: https://www.dropbox.com/s/mg83ru9zv4rc9o6/waitstats_db.xlsx?dl=0 WaitStats – I’ve […]
grant user read only whole server - Hello — I should know this…  but is there any way to grant a user global read-only access? We replicate our production server using log shipping, and we use the replicated copy for reporting purposes.  We have a few users who we would like to give read-only access to this copy.  However, the databases are […]
 

 

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

 

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