Use a Table Variable for Logging Entries that Need to Survive Rollbacks (2024-02-09)

SQLServerCentral Newsletter for February 9, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Use a Table Variable for Logging Entries that Need to Survive Rollbacks]( - [Fun with Powershell]( - [LangChain Prompt Templates with OpenAI LLMs]( - [From the SQL Server Central Blogs - Search for queries with Query Store GUI]( - [From the SQL Server Central Blogs - Moving SQL Prompt History to Another Machine]( - [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Question of the Day - [Error Severity 10]( The Voice of the DBA  Do You Have a Jeff? In the [Phoenix Project]( (worth a read), there is a character called Brent, who is to go-to person for everything in IT. I don't know if this character was modeled after [Brent Ozar]( but I always picture him when I re-read the book, and I suspect he was that person in previous positions. I've been that person as well, and it's both exciting, fulfilling, and very stressful. At [Redgate]( that person has been Robert C, who is my go-to person for many questions. In the DBA world, I think of [Jeff Moden](. He's been a prolific and incredible author over the years on many things SQL-related and is a huge proponent of others learning to write better code and better utilize the database platform more efficiently. I suspect in his company, he is the go-to person for most database-related questions and problems. I also suspect he solves most of them very well and has the influence (or power) to effect change. However, are you Jeff in your organization? Do you have a Jeff? In many of the customers I work with, there is no Jeff. Sometimes there are very smart people, but they cannot effect change. Or they don't know how to go about making change happen. In other companies, there isn't anyone who is an expert on the database or related technologies. Too often, I think people aren't often aware of what expert really is and they just do the best they can without knowing if it's a great job or a poor one. That's my opinion, but it's based on decades of experience (and success) working with lots of others on database code. The good news is that most of you can learn to be Jeff. You can work to improve your skills, both technical and interpersonal (soft), to raise the quality bar at your organization. Maybe you can get more work done and get a huge sense of satisfaction from your job (and a raise). Maybe you just want to get things done more efficiently and get away from work more often. There are many ways to do improve the quality at work, but essentially the idea behind [platform engineering]( is to produce tools that make developers and operations groups more efficient. The goal is to get more work done, easier, reducing the cognitive load on everyone involved with software. Not because they can't do the work, but because we want them focused on their specialty. Whether that's writing C# code for an application, writing zero downtime database changes, or making beautiful reports. The hassles of actually capturing and moving code should be something provided by a platform. A person can do some of this by sharing their knowledge, not just in conversations, but providing templates, models, standards, and other code elements that might help their co-workers become more productive. DevOps is about producing code quicker, reliably delivering that to the customer, and raising the quality bar (don't forget this) and platform engineering is the next evolution here, where the tools, process, and flow are set to make the implementation of DevOps easier for most developers and DBAs. Even if your organization doesn't want to produce a platform, the idea of building and deploying tools (samples, models, snippets, etc.) to make someone else's job (or yours) easier and smoother is something we all can do. We can all aspire to be Jeff and get there with a little investment in our skill across time. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Use a Table Variable for Logging Entries that Need to Survive Rollbacks]( Louis Bravo from SQLServerCentral This article shows how a table variable can be used to capture error information and log it when your code doesn't work as expected. [External Article]( [Fun with Powershell]( Additional Articles from SimpleTalk Have you ever been in a situation that you want to call a cmdlet or a function with a parameter that depends on a conditional criteria that is available as a list? In this article I will show a technique where you can use PowerShell Dynamic Parameters to assist the user with parameter values. [External Article]( [LangChain Prompt Templates with OpenAI LLMs]( Additional Articles from MSSQLTips.com Learn how to get started with a few sample prompt templates by using the gpt-4 LLM within OpenAI and LangChain in a Databricks notebook. [Blog Post]( From the SQL Server Central Blogs - [Search for queries with Query Store GUI]( Cláudio Silva from Cláudio Silva Hey folks, long time no write! Today I want to bring a tip that I use every week but I found that most SSMS users are unaware it exists. I can... [Blog Post]( From the SQL Server Central Blogs - [Moving SQL Prompt History to Another Machine]( Steve Jones - SSC Editor from The Voice of the DBA A customer was asking about SQL Prompt recently and how their history is stored and if it can be moved to another machine. This post shows how this works,... [Practical Database Auditing for Microsoft SQL Server and Azure SQL]( [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Site Owners from SQLServerCentral Know how to track changes and key events in your SQL Server databases in support of application troubleshooting, regulatory compliance, and governance. This book shows how to use key features in SQL Server ,such as SQL Server Audit and Extended Events, to track schema changes, permission changes, and changes to your data. You’ll even learn how to track queries run against specific tables in a database. ss   Question of the Day Today's question (by Steve Jones - SSC Editor):  Error Severity 10 I have code that raises an error. RAISERROR ('My error', 10, 1); When the calling program receives the error, what severity is returned to the client that called this code? 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) Building a BacPac What option do I use in SqlPackage.exe to create a .bacpac file containing schema and data? Answer: Export Explanation: The Export command is used to create a .bacpac file. Ref: SqlPackage - [ [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 [Long running open temp_db transaction.Age in seconds = 3456 secs]( - Version : Microsoft SQL Server 2016 (SP2-CU17-GDR) (KB5014351) - Microsoft Corporation Enterprise Edition. We keep receiving these alerts. Are there any parameters which can be "turned off" to suppress these messages ? [Import sql objects definiton into individual file]( - Hi, I'm trying to put the initial object definition into Azure Devops. My sql servers are on prem 2016. How can I script all the objects definition( table, views, stored procs, function) with if exists drop on the top in to their own individual file so that I can push them to Azure devops. This […] SQL Server 2019 - Administration [Server Audit Specifications, Audit Action Type -- SELECT UPDATE INSERT DELETE]( - I'm trying to capture an audit of ANYONE logging into a database on my SQL 2019 Enterprise instance. When setting up my 'Server Audit Specifications' the 'Audit Action Type' drop down is not showing options to select SELECT UPDATE INSERT DELETE as presented in online tutorials. I am both LOCAL ADMIN and SQL sysadmin on […] [add a group in local security policy]( - i want to add a group in multiple local security policy like 'Adjust memory quotas for a process', 'Lock pages in memory' , 'Log on as a service', how can it be achieved as a script? please share ideas. [Veeam backups and Tlog maintenance]( - Was testing with VEEAM to do SQL backups... Using app aware, the set up indicates the tlog would be truncated. But after testing I found the tlog content was not being truncated after backup. Does anyone else use VEEAM with App Aware with SQL Server and how to you mange the size of your tlogs? SQL Server 2019 - Development [xp_dirtree]( - Hello. I'm using sys.xp_dirtree to get a list of files in a folder. exec master.sys.xp_dirtree '\\myServer\Users\Documents\BulkInsert\', 1, 1; This was working fine whilst the number of files was approximately 3000. Now the number of the files has double but xp_dirtree is only returned the circa 3000 files. I changed the statement to :- exec master.sys.xp_dirtree […] [Table reference issue]( - We've been tasked for updating a system for a client on a more modern approach. Currently they do their business in access forms. The problem is when I try to reference the table I added to their existing table, I get the following error ,' columns in table do not match an existing primary key […] SQL Azure - Administration [Azure SQL Limit on external connections]( - Hello All, I have a use case to setup a Azure SQL DB for analytics and report developers. Planning for 20 people that will be connecting via SSMS/Azure Data studio/ PBI , excel etc. Reading the documentation there appears to be a very strong limit on external connections (which I assume the above are?) Is […] Reporting Services 2008/R2 Administration [update data source on all SSRS reports]( - I have many reports that are using a shared data source. The data source looks like this and it is deployed to a certain location on the reports server. I crossed out some sensitive information. But hopefully you can see what this is. Our Windows admin is about to delete this account and he has […] Integration Services [Execution of a Job Skipping Tasks When Ran from Agent]( - Hello, I'm running VS SSDT 2017. I made a package in SSIS that I want for the SQL Server Agent to be able to execute as part of a scheduled job in the Job Activity Monitor in SSMS. The problem that I'm having is that when I execute the package from the activity monitor, a […] [Filtering result set with a value selected from another Sql Server]( - Hi, I have a very slow view at one of the customer's database. I could speed up the select if I could filter the data with Value_Date. The "problem" is that there are two Sql Servers in this equation and I don't yet know how to use variables / expressions to accomplish this... I know […] [VS Professional 22 (17.8.6) installed - I can\'t create a SSIS ETL Project]( - Hi, I used to work a lot with MS SSIS ETL but I have been away 5 years now - working with Sql Server, DB2 and Informatica PowerCenter ETL Now I have an assignment with a customer who wants me to convert their PowerCenter ETL to SSIS due to company strategic policy. I installed Visual […] MySQL [Synchronous vs. Asynchronous Replication]( - Hi All, I want to deploy both Synchronous vs. Asynchronous replication in mysql nodes at the same time, basically I need to setup a 3 mysql nodes as a cluster on AWS instances (Synchronous replication) to sync between each other and one mysql node in my on premise server with Asynchronous replication to work as […] SQL Server 2022 - Administration [Data Architecture Options]( - We have a requirement to have 0 downtime during Migrations, more like Active- Active. What options do we have on Microsoft SQL Server side ? What restrictions do we have on Azure MI Business Critical? basically scripts are applied , every week as a part of Product Release and we need 0 down time, any […] SQL Server 2022 - Development [Migrating from SAP SQL Anywhere to SQL Server 2022 Standard]( - Good day We are in the progress of migrating SAP SQL Anywhere to SQL Server 2022 Standard. However, we have encountered numerous cases where the stored procedures pass a calculation as a parameter, e.g. Declare @Par INT = 10, @ReturnValue INT = NULL; /* a lot of calculations and variable assignment happening in this block*/ […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -