Announcing the first Microsoft Fabric Community Conference! (2024-02-05)

SQLServerCentral Newsletter for February 5, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Announcing the first Microsoft Fabric Community Conference!]( - [Fixing the Synapse Error "Available workspace capacity exceeded error (Livy session)"]( - [Resolving the MERGE statement attempted to UPDATE or DELETE the same row more than once error]( - [From the SQL Server Central Blogs - SQL Server's Buffer Cache: How to Calculate Dirty Page Sizes]( - [From the SQL Server Central Blogs - Digging up undiscovered savings]( - [Big Data Analytics with Spark: A Practitioner's Guide to Using Spark for Large Scale Data Analysis]( Question of the Day - [Checking Permissions]( The Voice of the DBA  Serious Software Glitches Recently [Robert Sterbal]( pointed out a podcast to me. [This link is for Apple Podcasts]( but it's for the Journal, which is on other platforms (I listened on Spotify). It's the story of a computer glitch in UK post office software, which resulted in quite a few local postmasters being criminally prosecuted, many convicted, and even a few committing suicide. It's a sad story, and it's complex, but there are some technology-related elements. First, the overall story is Fujitsu sold the UK a point-of-sale system for post offices. There was a computer glitch here, which incorrectly calculated lots of totals and showed postmasters owing more money than they should. They were upset, called support, got nowhere and many were liable for paying money they didn't owe. The UK postal management hid information about the widespread nature of the problem, while prosecuting many local postmasters. Fujitsu support didn't disclose to callers how others were experiencing this same issue. This also coincided with a (an unrelated) law that changed saying computer systems were presumed correct and anyone accused of a crime had to prove the computer was wrong. Without a doubt, there are human failings here with support people, management, a vendor, and likely others. I don't want to minimize those, and I do think quite a few people involved, especially management, should face charges. However, since this is a database-related site, I wanted to focus on the code quality here. I don't know the exact nature of the calculation issue, but there is clearly a bug somewhere in the system. Do we, as technologists, think we're better developers or database people than those at Fujitsu? Would we not produce calculation bugs that might be hidden in aggregations? I have to say that I see this stuff all the time and not just in development. I run into these bugs in production, and I think this is often because we don't embrace enough testing. I see this in all sorts of systems, with developers of many different experiences. While application developers have gotten very good at unit testing, that same habit hasn't gotten as widely deployed among database developers. What's more, I often find that people writing aggregation queries for reports often use lots of live data, and they don't write tests or even perform calculations to ensure complex formulas are correct. If you've ever done complex aggregations in SQL or DAX, you might find there can be strange effects from filters, from NULLs, and even from the way a window or range of rows is processed. It's easy to say that a report on 1,000 rows of data out of 100,000 is roughly correct with some total, when you haven't actually verified that calculation manually. I certainly think Fujitsu deserves a lot of blame in this case. Ultimately, they are the source of issues. Those that covered up the problems, both at the UK government organization and at Fujitsu should be prosecuted and held liable, but the programmers and testers are also at fault. They didn't do a good job testing their software, and worse, didn't do the job of tracking down the bugs, finding issues, and correcting them. I hope those issues are fixed now, but they weren't addressed promptly as this situation took place across years. I often work with companies trying to build software better, but I find it hard to get them to test database software. I know the testing frameworks are immature, the tooling is poor, and honestly, too few of us have a good test data management process in place. However, we can start to learn to add unit tests to our code. At the very least, we ought to write some repeatable, automated test when a bug is reported. Clearly, in that situation, we (as a team) didn't write good code if a bug was found. Either because of tech skills or we didn't get the specification correct. In either case, we need to improve and automated tests to ensure we don't make this mistake again are a way to start getting better. Much of the software I've worked on isn't directly related to affecting human lives. That's probably true for most of you unless you write software that controls some sort of vehicle movement or medical device that dispenses care or drugs. My son works on rocket flight software, and he takes that seriously since people will be riding those, but for most of us, the work we do isn't critical to anyone living or dying. However, this story shows that we might still affect human lives. We ought to take that responsibility seriously and ensure we are doing the best job we can to produce quality software. Having some testing (and good test data), is a way to double-check ourselves and our team. It's worked well to raise the quality level of mobile software dramatically. We database people ought to learn from that and adopt better testing. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Announcing the first Microsoft Fabric Community Conference!]( Steve Jones - SSC Editor from SQLServerCentral Read a message from Bob Ward about the first Microsoft Fabric conference taking place in Las Vegas this March. [SQLServerCentral Article]( [Fixing the Synapse Error "Available workspace capacity exceeded error (Livy session)"]( diponkar.paul from SQLServerCentral This article will help you to resolve the Livy session error in an Azure Synapse notebook. [External Article]( [Resolving the MERGE statement attempted to UPDATE or DELETE the same row more than once error]( Additional Articles from MSSQLTips.com I'm receiving the following error when attempting to run a T-SQL MERGE statement: "Msg 8672, Level 16, State 1, Line 123. The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. [Blog Post]( From the SQL Server Central Blogs - [SQL Server's Buffer Cache: How to Calculate Dirty Page Sizes]( SQLPals from Mission: SQL Homeostasis SQL Server's Buffer Cache: How to Calculate Dirty Page Sizes As we know, the SQL Server buffer cache is an area or region inside the server's memory where SQL Server... [Blog Post]( From the SQL Server Central Blogs - [Digging up undiscovered savings]( kleegeek from Technobabble by Klee from @kleegeek You might be able to shave off thousands – or more – in your monthly cloud bills for your critical SQL Servers, all while maintaining or even improving performance.... [Big Data Analytics cover]( [Big Data Analytics with Spark: A Practitioner's Guide to Using Spark for Large Scale Data Analysis]( Site Owners from SQLServerCentral Big Data Analytics with Spark is a step-by-step guide for learning Spark, which is an open-source fast and general-purpose cluster computing framework for large-scale data analysis. You will learn how to use Spark for different types of big data analytics projects, including batch, interactive, graph, and stream data analysis as well as machine learning. In addition, this book will help you become a much sought-after Spark expert.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Checking Permissions I want to check what permissions I have on a SQL Server 2022 instance and database. Can I do this with fn_my_permissions and get server and database permissions? SELECT * FROM sys.fn_my_permissions (NULL, NULL) AS fmp; 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) Getting Permissions If I run this, do I get permissions on the individual objects in the database on SQL Server 2019? SELECT * FROM sys.fn_my_permissions (NULL, 'database'); Answer: No Explanation: The correct answer is no. This query gives you database level permissions, on the database itself, such as CREATE, ALTER, BACKUP, general permissions on object classes. To get individual permissions to objects, you need to specify objects. Ref: sys.fn_my_permissions - [ [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 2016 - Administration [install sp3+]( - I have a SQL Server on version 13.0.5622.0 and need to get it up to date security patch wise. It appears I need to install sp3 and then the hotfix 13.0.7000.253 Azure feature sp3 Then the next three Azure security features 13.0.7016.1 13.0.7024.30 13.0.7029.3 Can someone confirm? [SQL DB 2 TB]( - I have a SQL database of 2 TB running on production. There is a need to refresh this database on Q environment on a daily basis. Cannot implement SQL to SQL replication coz InfoSec has an issue doing so (from prod to non prod)...Backup/restore takes incredibly long time. Any thoughts/suggestion/advice? SQL Server 2016 - Development and T-SQL [dont seem to be able to deploy an ssis project from vs 2022 to sql server 2019]( - Hi, from vs 2022 when i choose the deployment wizard for my ssis project i see this.  I am not sure what the path is supposed to be but i entered the folder name you see in the 2nd image after i added that folder to the catalog from ssms. When i deploy i […] [Script to Evaluate Datatype Usage or choice]( - Hey All, I'm trying to find a script that could be run against all tables to evaluate the datatype of each column. The idea would be to get feedback on whether performance could be improved if the column was switched to different datatype. Also, it could find situations in which a large datatype is being […] SQL Server 2019 - Administration [SPNS & Webserver connections.]( - Morning all, Appreciate a pointer on this! I have a web app, connecting via IIS on a webserver to SQL. I'm having authentication issues that I presume are SPN related so will start here. I have kerberos connections in my SQL server already. It's only the webApp I'm having issues with. When setting the SPN […] [SSMS]( - Hi I got a prompt saying that a new version of ssms was available so I downloaded it and installed it. The old version is still installed. I assumed that once I downloaded the new version then the old one would go away but it didn't. Is this expected? Can I remove the previous version […] SQL Server 2019 - Development [How to substract value from its own column in same tabl through condition basis]( - I have a table where i want to subtract the value from same column value through condition basis.. My table is like this i have group the table through item_id through the query SELECT Item_id, sum(qty) FROM [#TempSOR_mb] group by Item_id and get the result as per below : i want to subtract subitem_id 203 […] SQL Azure - Administration [Grant execute to all table types at once]( - In our database we have a lot of table types. I'm looking for a way to grant EXECUTE on all tables types at once. I'd also be interested in granting it to all table types in one particular schema. So far, everything I can find gives the syntax for granting execute to one table type […] Reporting Services [Connect SSRS with Microsoft fabric lakehouse]( - how to connect SSRS with Microsoft fabric lakehouse? Reporting Services 2008 Development [Teams meeting Required to explain a list that has a table embedded in it.]( - I was told to produce a report broken down into customers with the name and address at top of the letter and a list of invoices and each break down of items listed in the invoice. Looked like a list with a table embedded and the invoice number staggered with line items and cost below. […] Integration Services [Get rid off master key]( - Hello everyone, I'm currently working with Visual Studio 2022 and SQL Server 2019 Enterprise Edition. I am encountering an issue while attempting to deploy SSIS packages to the Integration Catalog, specifically an error related to creating a master key or opening in a new session. I'm seeking advice on how to resolve this problem. None […] [dtexecui.exe not found]( - Hi All, I have VS 2022 installed along with SQL server data tools enabled. I also installed SQL Server integration service project 2022 downloaded and installed. Sql server 2019 enterprise version is already installed. I could not find dtexecui.exe utility tool any where in the machine. What am I missing ? When I opened SQL […] SQL Server 2022 - Administration [DNS resolution for DB listener]( - We have a SQL Server Always On Availability Group (AG) configuration with the following components: 1. Nodes: Node1: ZZZDB1.abc.def.gh (IP: 11.89.12.83) Node2: ZZZDB2.abc.def.gh (IP: 11.89.12.46) 2. Failover Cluster: Failover Cluster Name: ZZZDBCLST IP Addresses: 11.89.12.53 11.89.12.87 3. Always On Database Listener: Listener Name: ZZZDBAG IP Addresses: 11.89.12.54 11.89.12.98 In multi-subnet configuration, where each node and […] SQL Server 2022 - Development [Performance optimization by excluding JOIN with tables only with one record]( - We are adding condition that check of the table variable contains one or less records and at this case switching JOIN to adding conditon to WHERE clause, for example: IF EXISTS(SELECT 1 FROM @TableTypePlayersTypes) BEGIN SELECT @RowCount = COUNT(*) FROM @TableTypePlayersTypes IF @RowCount <=1 BEGIN UPDATE PlayersTypes SET LastModifiedDateTime = @dt WHERE PlayersRestrictionID = (SELECT […] [How to replace a specific part of string contains value in side whole string]( - Good Morning, CREATE TABLE MyTable (ID int, DATA NVARCHAR(MAX)) INSERT INTO MyTable values (1, 'ID:12345, Name: Chris , Tiger, Sal:3000, City: NYC') INSERT INTO MyTable values (2, 'ID:23456, Name: Scott Lowski, Sal:4000, City: BOSTON') INSERT INTO MyTable values (3, 'ID:34567, Name: Mat, Linker Tiger, Sal:1000, City: LONDON') INSERT INTO MyTable values (4, 'ID:45678, Name: Tiger, […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -