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

Rogue Software Changes

This editorial was originally published on 16 Nov, 2015. It is being republished as Steve is out of the office at a customer today.

Could a group of software developers make changes that fundamentally alter the way a software system should work without management being aware?

That's the question being asked of VW right now. Most people are skeptical, but I ran across a piece that wants to lend credence to the idea that a few software engineers acted with few people being aware. They did this, not because they wanted to defraud everyone, but they wanted a solve a problem that they couldn't do in other ways. They also didn't see the alteration of test results as much of an issue because they thought the tests were too stringent.

I'm not sure I believe that's what happened. Certainly there is some disagreement from various parties, but with my experience in software projects, management always wants to know how things are proceeding, with more and more questions whenever the applications don't work as expected. When problems are solved, natural human curiosity leads more managers to ask for details, even when they don't understand. In this case, I can't imagine lots of VW management weren't aware that software was being used to pass tests. Many people report to many others, and everyone would have wanted to know how VW solved this engineering problem.

The stakes for organizations will continue to rise in a global economy, and software will play increasing roles in many companies. Will we see more and more pressure to manipulate our world with software, even in criminal ways? I suspect so, and I sympathize with those that might face the loss of employment for not complying with the requirements they're given.

Ultimately I think transparency of software is the best way to bring about better software that complies with regulations and rules. Transparency also ensures that copyrights aren't violated (since violators code is available), and we can determine if security is being built into systems. Perhaps best of all, developers can all learn from each other, seeing exactly what works and doesn't in each system.

I doubt we'll get there, but transparency would be a nice place to be.

Steve Jones - SSC Editor

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

Redgate Database Devops
 
  Featured Contents

Oops, I deleted that data

MVDBA from SQLServerCentral

We all have these days as a DBA,  hopefully they are few and far between for most of us. This morning I walked into the office and found a stack of emails saying that a developer had responded to a support ticket and modified the values in one of our tables and got it wrong. OK, […]

Planning for SAP HANA Deployments in Public Cloud Environments

Additional Articles from Database Journal

Learn about the factors that should be taken into account if you are considering hosting SAP HANA in a public cloud.

Recreating Databases from Scratch with SQL Change Automation

Additional Articles from Redgate

Phil Factor starts with the basics how to rebuild a set of development database from scratch, using SQL Change Automation, and then demonstrates how to check for any active sessions before rebuilding, import test data using BCP, and secure passwords if connecting to the target with SQL Server credentials.

From the SQL Server Central Blogs - Switch activity in Azure Data Factory: Container with many IFs

Rayis Imayev from Data Adventures

(2019-October-16) Developing conditional logic of your Azure Data Factory control flow has been simplified with introducing of the Switch activity - https://docs.microsoft.com/en-us/azure/data-factory/control-flow-switch-activity. Official documentation resource states, this new data factory activity "provides...

From the SQL Server Central Blogs - The FAST number_rows Query Hint

SQLEspresso from SQLEspresso

Query hints are always about tradeoffs—typically you are giving up something, such as flexibility, to achieve more consistency, or very specific performance characteristic. One example of this migt when...

 

  Question of the Day

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

 

The Partial Computed Column

I want to add this column to my table. What happens when I run this?
ALTER TABLE dbo.Activities
ADD ProdOneCount AS CASE
                        WHEN ProductID = 1 THEN
                            CallCount
                    END;
GO

Think you know the answer? Click here, and find out if you are right.

 

 

  Yesterday's Question of the Day (by Thomas Franz)

Database containment and c

Lets start with a simple, new database with Latin1_General_CI_AS_KS as collation:

USE [master]
go
CREATE DATABASE [test_cont]
CONTAINMENT = NONE
ON PRIMARY 
( NAME = N'test_cont', FILENAME = N'c:temptest_cont.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON 
( NAME = N'test_cont_log', FILENAME = N'c:temptest_cont_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [test_cont] COLLATE Latin1_General_CI_AS_KS

When we run the following query, we see, that the collation of the name column in the view sys.objects is Latin1_General_CI_AS_KS (as expected) and Latin1_General_CI_AS_KS_WS for type / type_desc.

USE test_cont
GO
SELECT c.name, c.collation_name
FROM sys.all_objects AS ao
INNER JOIN sys.all_columns AS c
ON c.object_id = ao.object_id -- will be -385 per default for sys.objects
WHERE ao.name = 'objects'
AND ao.schema_id = SCHEMA_ID('sys')
AND c.collation_name IS NOT NULL;
GO

Now we change the database to partially containment (allowing us to create user, that exists only in this database but not as login on the server level):

-- don't do it on production without exactly knowing, what you do!!!
EXEC sys.sp_configure @configname = 'contained database authentication', @configvalue = 1; 
RECONFIGURE

ALTER DATABASE [test_cont] SET CONTAINMENT = NONE WITH NO_WAIT

Question:

What happens, when we execute this code?

USE test_cont
GO
SELECT c.name, c.collation_name
FROM sys.all_objects AS ao
INNER JOIN sys.all_columns AS c
ON c.object_id = ao.object_id -- will be -385 per default for sys.objects
WHERE ao.name = 'objects'
AND ao.schema_id = SCHEMA_ID('sys')
AND c.collation_name IS NOT NULL;
GO

PS: tested on SQL 2016 SP2 Enterprise

Answer: sys.objects.name has now the collation Latin1_General_100_CI_AS_KS_WS_SC

Explanation: I couldn't find a official documentation regarding this behavior, but putting a database in the partially containment mode changes the collation of several system tables (sys.schemas is another one). Changing back the containment to NONE again "restores" the default database collation. We stumbled over this, when one of our devs created a UNION ALL query over sys.objects in different databases including one partially contained database (caused an error, since the collations in the different SELECTs was not compatible). For more informations regarding Containment, you can read this article.  

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
Managing Mirroring failover in a WSFC cluster - Hi all, hope you can help. I'm not a DBA so have been googling as I go, so forgive my idiocy: I've got a SQL server standard instance with a number of databases. The setup is as follows: Servers 1 and 2 are in a SQL cluster with shared storage. Server 3 is a standalone […]
SQL profiler - Is SQL profile being deprecated and extended events should be used instead?   Also, has anyone heard that there is more of a concern around Statistics rather than fragmentation ?
Extended Events- how to log to an SQL table? - I want to enable an Extended events session. However, I don't want it to go to a file on the HDD, I 'd really like to send it all to an SQL table so I can do grouping, analysis, reporting, etc... How can I get that data into an SQL table for longer term storage […]
SQL Server 2017 - Development
Most frequently used text in column - I would like to update a field (yearclass) for each row with the most common text in payclass for each grower, block, section and year combination. In the case where there are equal amounts of payclass, weight should be used to determine yearclass. Highest sum of weight wins. I have tried many queries of which […]
Insert into OPENDATASOURCE with Microsoft.ACE.OLEDB.16.0 provider - failed - Hi all, we export data into Excel file from SQL Server 2017 with a simple command (a part of a stored procedure): The command looks like: INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.16.0', 'Excel8.0;HDR=YES;IMEX=1;Database=\\some_folder\excel1.xls')...Sheet1     SELECT * FROM table1 It works fine. But only if just one export is processing at the same time. If more exports are […]
Removing a double space in the middle of a name - Suppose I had a list of names and one of the names was Kathy  Harrison.  Notice the double space after the first name.  In Excel there is a function called TRIM which could remove extraneous spaces.  I am trying to find something like that in SQL.  As far as I know, in SQL, TRIM is […]
SQL Server 2016 - Administration
SQL Server security after deleting AD group - I currently have a scenario where an AD group that was defined as a SQL login with particular permissions got deleted from AD but not from SQL. So: domaina\groupb - no longer exists as an AD group but still exists as a SQL login It seems that members of the deleted group still have SQL […]
Very large t-log with backups - I have a database that's 125GB, but the t-log is 190GB.  I do full backups each day and t-log backups every 15 minutes.  I've verified the log backups have been successful for the past week.  Generally when I see a large log like this it is because backups are failing.  The log isn't growing out […]
SQL Server 2016 - Development and T-SQL
Exporting very large Data to an XML file from SQL Server - I have a query that creates an xml file but I'm getting errors because the XML is too large. I've already set the Results To Grid Property XML Data to unlimited but I'm still getting the error.
Administration - SQL Server 2014
Always on - huge set of inserts and updates - Hi everyone, I need some advice please on how best to do something. We have a database which is 1TB in size, its part of a SQL 2014 Always On AG (synchronise sync). We have to do a very large data change, initial estimates are from testing it will take around 15 hours. Optimisation has […]
Development - SQL Server 2014
NOOB question with Case statement - hi here i am again with a noob question again: i'm trying to do this: case  when columnA <> 0 then columnB = 1 else columnB end but it return error. i used case statment before but im the same column, is it possible to use in multiple column with the argument based on one […]
SQL 2012 - General
service pack Question - Hi, I updated our SQL ser5ver from Sp2 to SP 4 today, and it says it was a success. However when I run say: I get Sp 2 So did I get the update or not if not what can I do? Thank you SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
SQL Server 2012 - T-SQL
join with GEOMETRY data type - I want to perform something like....   SELECT * FROM a JOIN b ON a.geometryField  = b.geometryField OR SELECT * FROM a JOIN b ON a.geometryField.  STEquals(b.geometryField ) OR SELECT geometryField FROM a WHERE geometryField  IN (SELECT geometryField FROM b )   You get the gist.  None of the above work.  I know I need […]
Need help query for current row and previous row - Hye, I've table and data as following, CREATE TABLE [dbo].[WeightStory]( [Idx] [int] IDENTITY(1,1) NOT NULL, [WeightDte] [datetime] NULL, [WhatWeight] [decimal](18, 3) NULL, CONSTRAINT [PK_WeightStory] PRIMARY KEY CLUSTERED ( [Idx] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[WeightStory] […]
Reporting Services
Failed Executions - Curious what everyone else is doing... I've always had a daily report showing me report subscriptions that failed.  I've had a task to better monitor all report interactions (subscriptions, cache refresh and interactive) sitting in my queue, but I put it off because of other higher priority tasks.  Right now I'm in the process of […]
 

 

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

 

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