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

Rock and Roll

Today we have a guest editorial from Grant Fritchey as Steve is away on his sabbatical.

There are days where working in IT feels like being a colossus astride the planet. Then, there are most of the rest of the days where you just feel like Sisyphus. “I just got that gol-durned rock up there!!!”

I was recently talking with a group of IT folks about data compliance and the changing compliance landscape. For example, you know that the California Consumer Privacy Act went into effect on January 1 with enforcement starting June 1. If not, now you do. The part that was making me crazy, and frankly I had little to offer the people I was talking with is the core of the compliance issue: It’s a business problem, not a technical one.

Oh, sure, there are tons of technical implementation details that will ultimately land on our shoulders as IT folks. However, the definitions have to come from our legal and business teams. The impetus to get to work on this stuff also has to come from the business. What’s worse, the urgency must come from the business, and I’m not convinced that businesses see compliance as anything urgent, despite the growing GDPR fines and the expansion of GDPR-style laws all around the world.

How on earth can we in IT move the rock to the top of the compliance hill without the engagement of the business?

We can’t. So, then, the question becomes, how do we engage the business? I know people who have pointed out the fines issued so far, only to be rejected. They’ve also showed how high the potential fines could be. Still, the business frequently doesn’t seem interested. Yet, it’s clear that this is going to be a problem and that the problem entails a partnership between the people in IT and the rest of the organization.

I put it to you. How do you engage an organization that is both ignorant of the issues and, frankly, uninterested? Is it that the business-side of things has knowledge we lack? That could certainly be true. Conversely, are they just desperately sticking their heads in the sands, hoping the storm passes them by? That may also be true. Regardless of the facts, it sure feels like we’ve rolled this rock up the hill a time or three.

Grant Fritchey

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

Redgate University
 
 Featured Contents

Analysis Services : OLEDB Error while processing in SSAS

aveek22 from SQLServerCentral

Problem This is one of the errors that I frequently encounter while deploying or processing SSAS OLAP cubes on the client's computers or on a remote server. Recently, one of my team members also faced the same issue and so I thought to write a resolution for the issue. The error is something like this: […]

How to Create an Ubuntu PowerShell Development Environment – Part 1

Additional Articles and Robert Cain from SimpleTalk

Since SQL Server now runs in Linux, more database professionals will begin to learn this operating system. In this article, Robert Cain demonstrates how to set up an Ubuntu virtual machine for learning.

Changing SET options in a Procedure or Trigger leads to Recompilation (PE012)

Additional Articles and Phil Factor from Redgate

Phil Factor delves into SQL Prompt's performance rule, PE012, which will advise you if it detects the use of the SET statements within a stored procedure or trigger, which might cause unnecessary recompilations, though the issue extends to other types of batches.

From the SQL Server Central Blogs - Actual Difference Between EXISTS(SELECT 1 …), EXISTS(SELECT * …), and EXISTS(SELECT column …)

Solomon Rutzky from SQL Quantum Leap

Is EXISTS(SELECT 1 ...) better than EXISTS(SELECT * ...)? Who would know better than SQL Server?… Continue reading Actual Difference Between EXISTS(SELECT 1 …), EXISTS(SELECT * …), and EXISTS(SELECT...

From the SQL Server Central Blogs - CRM Data Source Connection Error

SQLRNNR from SQL RNNR

This article takes us to the edge with a couple of CRM related errors after changing the service account to a more secure Managed Service Account. Despite the CRM...

 

 Question of the Day

Today's question (by Kathi Kellenberger):

 

Calculate the Days Between First and Last Orders

Your SQL Server 2017 database has a Sales table that contains CustomerID, OrderID, and OrderDate columns. You would like to return a list of CustomerIDs along with the difference in days between the very first order the customer placed and the most recent order. Which query will give you that answer? Query #1 
SELECT CustomerID, 
   DATEDIFF(DAY,LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderID),
   OrderDate) AS OrderSpanDays
FROM Sales;
Query #2 
SELECT CustomerID, 
   DATEDIFF(DAY,OrderDate, LEAD(OrderDate) OVER(PARTITION BY CustomerID ORDER BY 
   OrderID)) AS OrderSpanDays
FROM Sales;
Query #3 
SELECT DISTINCT CustomerID, 
   DATEDIFF(DAY,FIRST_VALUE(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderID
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
   LAST_VALUE(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderID 
   ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)) AS OrderSpanDays
FROM Sales;
Query #4 
SELECT DISTINCT CustomerID, 
   DATEDIFF(DAY,FIRST_VALUE(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderID),
   LAST_VALUE(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderID)) AS OrderSpanDays
FROM Sales;

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)

SQLCMD OS Commands

I am writing a SQLCMD script in SSMS with SQLCMD mode. I want to execute an operating system command to perform an action, like make a directory. How do I format this command in my script?

Answer: Precede the command with !!

Explanation: Two exclamation points (!!) are used to designate OS commands. These commands are passed to cmd.exe. Ref: Edit SQLCMD Scripts with Query Editor - https://docs.microsoft.com/en-us/sql/ssms/scripting/edit-sqlcmd-scripts-with-query-editor?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 - Administration
Space issue on a table - All,   I am working on implementing table partitioning on a existing table. The table has clustered columnstore index. I have to drop the index and recreate it to use the partition scheme The table size before dropping the index is 200 GB and after dropping the index the table size is 4 TB Do […]
SQL Server 2017 - Development
Insert New Rows in sql based on difference between 2 dates - Hi,   I want to create a new records based on the difference between Start and End dates when StartDate is not NULL . The New value created can be first of every month . I need this field for reporting purpose to get a count of employees my month .   CREATE TABLE #MonthsYearBetwenDates […]
View runs slower - I have the following query SELECT DateADD(day,9-DATEPART(dw,cdata .call_datetime ),cast(cdata .call_datetime as date) ) as effective_date , A.EmployeeID AS employee_id , COUNT(cdata .Extension) AS metric_value --, ISNULL(MS.metric_id,'') AS metric_id FROM ( SELECT EmployeeID FROM ad.it_activedirectorydata WHERE [Enabled] = 'True' ) A INNER JOIN [vw_exampl] cdata ON A.EmployeeID =cdata .employeeID INNNER JOIN tbl2 ON A.employyeid=tbl2.employeeid GROUP BY […]
SQL Server 2016 - Development and T-SQL
Update parent row from child row that has partial data. - On the same table I have table rows that are broken from the import and went to the next row. I need to bring it back to the parent row. For example ID 1  Col3  supposed to have the value 'GHI' but the 'I' went to the 2nd row or ID 2 Col1 cell. Same […]
Parallel Load thousands csv Files - Hi, what is the fastest way to load thousands of csv files with the same structure into an OnPrem SQL DB (staging) - day by day? A parallelization would be ideal. These files have the customer number in the FileName (SSIS, Bulk Insert, Stored Proc., Data Factory, partitioning ...): Thanks Regards Nicole
Administration - SQL Server 2014
AWS instance storage - We are planning to use one of AWS instance for sql server and wanted to know if the instance storage drives can be encrypted for storing PCI and PII data. Coudn't find info on AWS documents. https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/InstanceStorage.html  
SQL 2012 - General
Replication causing log bloating - Hello, We've had a replication process in place for several years.  It seems like every couple of years, there would seem to be some sort of hiccup where our transaction log would start to bloat like crazy.  When I queried the sys.databases table to look at the log_reuse_wait_desc, "REPLICATION" would be the value.  So we […]
How to import Excel with XML - Hi, I have an Excel sheet that I have to import into SQL Server. It just has 3 columns, with one that looks like XML to me. Now I will admit I am not an expert with XML. The columns are something like this: Note: The column with  Acct Info below has what I believe […]
SQL Server 2012 - T-SQL
Recursive CTE - Batches - Hi Folks I'm trying to group rows into batch and adding batch to query to fetch data from system. I know there is lot of questions why it is required. I'm trying fetch data from Oracle SQL developer.  The issue is last row of batch is repeating in next batch. I added query for better […]
XQuery - Use column value to display correct node - Hi All First time with XQuery and despite trying to read as many guides as I can, I'm hitting a bit of a mental block. We have a form design application that stores templates and details of the questions that relate to each template in relational tables. The attributes for the template and questions are […]
Fastest way to load a CSV file in SQL Server Table - What is the fastest way to load a file in SQL Server Table ? I have large CSV Files ( 3-4 GB ) which I load through BULK INSERT. I find this slow compared to BCP. I am unable to load it using BCP as the data types are not supported ( INT,VARCHAR, etc) Please […]
SQL Server 2019 - Administration
OLE DB 0x80004005 connection error trying to run SQL HA Maintenance Plans - Hi, I'm getting a connection error when I try to run even the most basic plan, fails with scheduled or manual runs. The user is both a local-admin on all 3 nodes, and a SQL SA member. All engine processes and agent processes run-as that same AD account too. Everything works great (failovers etc), just […]
COHESITY for Backup/Restore? - Any one have any experience with using Cohesity as the backup/restore platform instead of doing native SQL backups? With TSM, we always did a SQL backup, and then swept that up to tape. In our new backup system based on Cohesity, my backup admins want to backup up the database and log files directly from […]
SSDT
VS 2019 - Schema Compare - Ignore Fill Factor Not Working - Got a strange one with VS2019 and a DB Schema Compare. When comparing two tables on ServerA to ServerB, it is throwing up a difference for the fill factor being 80/90 which is right. However in my options I have "Ignore Fill Factor" enabled so I shouldn't be seeing these differences. As we testing different […]
Integration Services
SSIS compoenents - For SSIS to pickup any dll componenet where do we need to deploy?  some dlls should not go to GAC. in that case what is default localtion for SSIS to pick up?
 

 

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

 

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