Leveraging SQL Transaction Control in Python Applications (2024-10-28)

SQLServerCentral Newsletter for October 28, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Leveraging SQL Transaction Control in Python Applications]( - [Audit SQL Server Extended Stored Procedures Usage]( - [From the SQL Server Central Blogs - Control Flow Restartability in Azure Data Factory]( - [From the SQL Server Central Blogs - Monday Monitor Tips: Knowing Your RPO]( - [The Unicorn Project]( Question of the Day - [AUTO_DROP Statistics]( The Voice of the DBA  Databases for Executives There's an article at Forbes about the [Five Things Business Leaders Should Know about Databases](. Disclosure, it's by my boss, but I think it's still a good read. These are points we've learned from research and work with customers and prospects at Redgate Software. These points come from you, as well as from executives with whom we work, but there are so many people in organizations who don't think about the complexity of data, so it's a good one to pass along. The five things are (if you don't want to read): data is growing, getting more complex, there are multiple database platforms in most estates, teams struggle (duh), and data is a business issue. Most of us know about the fourth one, often because we may feel overloaded with work. We might also feel a lot of stress in trying to keep up with not only the workload but also trying to learn more to support the ever-growing variety of systems it seems our employer wants to put into production. I regularly talk with customers whose developers keep wanting to try out a new, shiny database platform in the cloud (or add new features from their existing platforms). Wait, not try out. They've already deployed some production data there and now want other developers or operations people to work with their system. Often lots of the staff isn't familiar with the platform or feature, even the people who decided to implement it. Is anyone familiar with that situation? The digital transformation and importance of software isn't lost on most executives, but I find far too many that don't place the same importance on the data that powers their software or the database platforms that support all software. Data is important, and ensuring it is available to software, protected, secured, and available is critical. That also means that the process of developing and managing the database portion of your software matters. I think Database DevOps is important, but it's not a panacea to buy a product. I'd love to show you what [Redgate Software can do here]( but the main thing I stress to clients is that you need to get your database development into the modern era and match what software developers do. That means a smooth process, with version control, that ensures you deploy changes in a way that doesn't impact customers. This doesn't mean just go faster. This means bringing along data modeling, good architecture, and performance testing. Those are the same things we've been doing (or should have been doing) for decades, but we need to ensure these are still a part of whatever process we choose and included in any automation we implement. Pass the article along to your management, and be sure they understand that all these points are important. Particularly the fourth point because if your staff isn't supported and trained, the rest of the business will suffer. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Leveraging SQL Transaction Control in Python Applications]( omu from SQLServerCentral Explore the fundamentals of Python's SQL transaction control, demonstrating how to control and enhance database operations for improved data integrity. The best practices and real-world examples for integrating strong transaction management in Python applications are covered in this article. [External Article]( [Audit SQL Server Extended Stored Procedures Usage]( Additional Articles from MSSQLTips.com In this article, we look at how to audit and monitor the use of SQL Server extended stored procedures in your database instance. [Blog Post]( From the SQL Server Central Blogs - [Control Flow Restartability in Azure Data Factory]( Meagan Longoria from Data Savvy I presented at SQL Saturday Pittshburgh this past weekend about populating your data warehouse with a metadata-driven, pattern-based approach. One of the benefits I mentioned is that it’s easy... [Blog Post]( From the SQL Server Central Blogs - [Monday Monitor Tips: Knowing Your RPO]( Steve Jones - SSC Editor from The Voice of the DBA A customer was asking recently about the RPO for their estate, and I showed them a few things from the Estate tab in Redgate Monitor. This post covers a... [The Unicorn Project]( Site Owners from SQLServerCentral In The Unicorn Project, we follow Maxine, a senior lead developer and architect, as she is exiled to the Phoenix Project, to the horror of her friends and colleagues, as punishment for contributing to a payroll outage. She tries to survive in what feels like a heartless and uncaring bureaucracy and to work within a system where no one can get anything done without endless committees, paperwork, and approvals.   Question of the Day Today's question (by Steve Jones - SSC Editor):  AUTO_DROP Statistics In SQL Server 2022, what does this statement do? CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH AUTO_DROP = ON 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) No Renames Which of these can't be renamed with sp_rename? Answer: all of these can be renamed with sp_rename Explanation: All of these can be renamed with sp_rename. Ref: sp_rename - [ [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 - Development and T-SQL [Nested Multi-Line Comments Q]( - Is the below with nested multi-line comments valid T-SQL code? I thought no because it has nested Multi-line comments but when I drop the code into SQL Server to check its I get no errors. Below is a section of the script that has the nested comments. The Closing comment marker in the last row […] SQL Server 2019 - Administration [ODBC Connectivity]( - I am running SQL Server 2019 on my local PC - Windows 10 pro x64. I have an MSAccess application that uses a ODBC DSN to connect to the database. I am trying to connect instances of this application from other PCs running Windows 11. I cannot create a ODBC DSN from those machines to […] [Adding Database to AG After Adding A New Master Key To Database]( - I have a database i need to add to AG. lets name it "TestDB", now TestDB already has a master key and i do not have the password for it, so i add a new database master key to it using the following query - USE [TestDB]; ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = […] [Adding Database to AG After Adding A New Master Key To Database]( - I have a database i need to add to AG. lets name it "TestDB", now TestDB already has a master key and i do not have the password for it, so i add a new database master key to it using the following query - USE [TestDB]; ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = […] SQL Server 2019 - Development [Operand data type varchar is invalid for sum operator]( - I have this view [dbo].[Asset Metrics - Client(Rama -Client-Test)], which makes use of multiple tables/views Below: The only change that was made in the underlying data was [Prod].[IMETA - Global Field Flows and Holdings (Period and client)_model_SAP BW]. The Value column in source contained a non numerical value "X", i removed it and replaced it […] [SSIS Package error when running through SQL Agent job]( - Hi, Can someone please help me resolve below error. To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition (64-bit) of Integration Services or higher. End Error DTExec: The package execution returned DTSER_FAILURE (1). I'm running my package using SQL Server agent Job. But it is throwing error as […] Reporting Services [Upload report fails (SSL/TLS) but "Save As" from Report Builder works]( - When I use the "Upload" button from the SSRS website it fails and I get the following error in the logfile: OData exception occurred: System.Net.WebException: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. However, if I open the same report in Report Builder and use "Save As", it […] General [Why does SSMS always pop up this window every time I get into it?]( - For the last few weeks every time I get into SSMS 20.2 I am presented with this window: I always select the Copy button, which supposedly would copy the top of my user settings file a new version. I would think that would take care of it, but no, the next time I get into […] Strategies and Ideas [Date Type ETL Question]( - I'm not sure that I'm posting this in the right section, so please mods please move if that is the case. Anyway, what are the best practices for ETL jobs that move Date fields? Let's say I'm going from Oracle to SQL Server. Is it best to land the data in SQL server in some […] Integration Services [Converting Non Numeric to Null]( - I have a numeric column called "Value" in a flat file, which contains both numeric data and non-numeric values, such as "X". I'm using the Derived Column Task in SSIS to convert the non-numeric "X" values to NULL. However, I am encountering an error during this process. Could you please assist me in resolving this […] [Converting Non Numeric to Null]( - I have a numeric column called "Value" in a flat file, which contains both numeric data and non-numeric values, such as "X". I'm using the Derived Column Task in SSIS to convert the non-numeric "X" values to NULL. However, I am encountering an error during this process. Could you please assist me in resolving this […] Working with Oracle [Sql server 2005 cpu spiking issue]( - Hi everyone, new to this sub but I was wondering what you guys would do to check on a sql server that goes from 5% cpu utilization to 99% back down to 5% every 4 seconds. I checked if it was a job and the closest one is a simple insert that runs every minute. […] SQL Server 2022 - Administration [Database backup and restore questions.]( - Hello: I’m not one hundred percent sure on database backup and restore and have questions. Scenario ·       A developer accidentally deleted the production database on January 21st at 10 AM. ·       Questions: o  How can I recover the database point of time? o  Can I restore or recover the production database point of time if […] [Possible oddity in msdb.dbo.backupset]( - Hi all  I've written a script to keep track of how long backups are taking, which databases got a full or differential backup, and a few other bits. I'm using msdb.dbo.backupset as my base table but I think I've found something strange. According to that table, we managed to backup a 300GB database in […] SQL Server 2022 - Development [how to solve a conversion failure error]( - Sample query reply appreciated Getting the following error: Conversion failed when converting the varchar value 'VZ34-031' to data type int. This is my query that produced the error: select count(t1.shipqty), t3.description from sales t1 left outer join return t2 on t1.company = t2.company and t1.invoicenum = t2.invoicenum left outer join reason t3 on t2.company = […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -