Managing Change (2023-08-16)

SQLServerCentral Newsletter for August 16, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Managing Change]( - [PostgreSQL Schema: Learning PostgreSQL with Grant]( - [PASS Data Community Summit 2023 - Sessions are available to view!]( - [From the SQL Server Central Blogs - SQL Homework – August 2023 – Have the best practices changed while you weren’t looking?]( - [From the SQL Server Central Blogs - A Basic SQL Script to Monitor Long Running SQL Server Agent Jobs]( - [SQL Server Execution Plans, Third Edition, by Grant Fritchey]( Question of the Day - [Restoring With Standby]( The Voice of the DBA  Code Building Code Today's editorial was originally published on Apr 9, 2019. It is being republished as Steve is out of town today. The dream for some people is to have an Artificial Intelligence (AI) system that you use to describe some requirements and it will build an application that meets your needs. Certainly some AI and ML systems have reduced the need to write code for portions of an application, but I don't think there is any AI framework that can build an entire application from scratch. I was reading [a blog post]( recently on using metadata in our database to produce a CREATE TABLE statement that could hold the output of a query. It's not AI, but this is something I've done in the past, using code to help me get work done. Excel was one of my earliest helpers and still is. I find myself sometimes using Excel to build a series of statements that follow a pattern, but the contents of which might be based on some result set. A common example is a set of inserts based on some data. I use values in cells to build up a final statement and then copy these to SSMS or another tool and execute them. It's quick and dirty, but it works well. In the past I have written code that would build other object code, usually to provide some API constructs for developers. In a few environments, we have had some standards about how to structure tables, views, and stored procedures, including at times an API-like standard that required certain functionality be implemented in stored procedures. Using a code writing stored procedure allowed me to quickly ensure that the required stored procedures were created and modified as tables were added or altered. This also ensured that we kept all these changes in sync, without depending on my to review every part of the API. I don't know that I'll see a true AI system that we can give a few specifications to and have it build a system, but the more we implement standards and known structures, the more we can use code to help us ensure those standards are implemented in a consistent manner. Using templates in our work, such as powerful snippets in SQL Prompt, along with code analysis that looks for poor practices can help us write better applications. Even if it doesn't do all the work, these helper tools certainly improve the quality of the code we do write. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Managing Change]( David.Poole from SQLServerCentral Time has brought clarity for changes which could and should have been provided by its instigators. We compare a change where clarity was sought to those that were chaotic and the approaches that were taken at their two poles. [External Article]( [PostgreSQL Schema: Learning PostgreSQL with Grant]( Additional Articles from SimpleTalk An important aspect of building and managing a database is organizing the objects within your database. You may have tables that support different functions, for example, a schema for warehousing operations and another for sales. Some logins may need access to some tables, but not others. You might want to isolate one set of objects within a database from other sets of objects. All of this, and more, can be accomplished using schemas within a database and PostgreSQL supports the use of schema for just these types of functions. [Technical Article]( [PASS Data Community Summit 2023 - Sessions are available to view!]( Additional Articles from PASS As hosts, Redgate are delighted to announce PASS Data Community Summit 2023 sessions are now live and available to view! [Blog Post]( From the SQL Server Central Blogs - [SQL Homework – August 2023 – Have the best practices changed while you weren’t looking?]( Kenneth.Fisher from SQLStudies It’s been a bit since I did one of these so let’s go back to the basics. Best practices. We ... Continue reading [Blog Post]( From the SQL Server Central Blogs - [A Basic SQL Script to Monitor Long Running SQL Server Agent Jobs]( SQLPals from Mission: SQL Homeostasis A Basic SQL Script to Monitor Long Running SQL Server Agent Jobs  As a SQL Server DBA, especially Production/Operations DBA in a relatively large environment,... [SQL Server Execution Plans eBook, Third Edition, by Grant Fritchey]( [SQL Server Execution Plans, Third Edition, by Grant Fritchey]( Grant Fritchey from SQLServerCentral Every Database Administrator, developer, report writer, and anyone else who writes T-SQL to access SQL Server data, must understand how to read and interpret execution plans. This book leads you right from the basics of capturing plans, through how to interrupt them in their various forms, graphical or XML, and then how to use the information you find there to diagnose the most common causes of poor query performance.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Restoring With Standby I have a SQL Server 2017 database that I backed up. On a SQL Server 2019 instance, I want to restore this backup. All paths for files are the same on both machines and exist. Which statement or statements below will fail? 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) A Bundle of Changes I have this code I'm running on SQL Server 2019. BEGIN TRAN ALTER TABLE dbo.Product ADD productdescription VARCHAR(max) CREATE FULLTEXT INDEX ON dbo.Product(productdescription) KEY INDEX ProductPK WITH STOPLIST = SYSTEM; GO COMMIT The objects listed exist, except for productdescription, which is being added. No other transactions are open and a full-text catalog exists. What happens when I run this code? Answer: This fails as fulltext DDL cannot be inside a transaction. Explanation: Fulltext DDL cannot be inside a transaction. This returns the error: Msg 574, Level 16, State 0, Line 5 CREATE FULLTEXT INDEX statement cannot be used inside a user transaction. Ref: CREATE FULLTEXT INDEX - [ [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 [How to identify changed values from a history table?]( - We have several tables with system_versioning set to on. There is a requirement now to produce a solution (function / stored procedure) to return a list of changed columns of a given table within a range of days. Below is an example of one of the tables - customer_sites and a bit of data: /**** […] [Run Stored Procedure on the first Monday if each month of Tues if Mon Holiday]( - Hello, I need to setup a Stored Procedure to run on the first Monday of each month that is not a holiday. If the first Monday of the month is a holiday, than I need the PROC to run on Tuesday. Via Scheduled Jobs, how is this possible? Can someone help with a query on […] SQL Server 2019 - Administration [repeated login failures.]( - Do repeated login failures into SQL affect performance. Surely some resources are used with each attempted ack & nack? Certainly log writes. I have careless app owners who repeatedly fall foul of my attempts to stop this happening as their apps try access every 10 seconds or so. They failures fill my logs & I […] [SQL SERVER AUDIT LOG can scheduled job be placed and get file in .csv format]( - Hi Team, SQL SERVER AUDIT LOG can scheduled job be placed and get file in .csv format we can see the audit logs using view logs rather than is there any way we can set the audit to run in frequency for every one hour and schedule it in a job where we can get […] [Deadlock - implicit transaction]( - Hi, We are experiencing deadlocks. The process not being the victim is doing an update by stored procedure which has begin tran, commit tran. However the transactionname in the deadlock graph is implicit_transactions. Could this be potentially causing the deadlock issue and from the application implicit_transaction is on?  [File does not close: VerifyShareWriteAccess.txt]( - Hi Folks. We have two SQL Server 2019 Enterprise in an High Availability Group (always on). The OS is Windows server 2019. For the witness of the failover cluster we use a file share. The cluster itself runs perfect. But we have some troubles with the file VerifyShareWriteAccess.txt. The cluster service opens the file (read, […] [Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]( - Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. see error below: I'm trying to login into a sql server on another domain (DEV). I run a command to launch SSMS and then login to a DEV server as follows: %windir%\system32\RUNAS.exe /smartcard /user:ABC@dev.com /netonly "C:\Program Files (x86)\Microsoft […] [Why would user account authentication issues arise in Azure?]( - Through a series of events too long to go into here, last year I was involved in helping to move a test SQL Server 2019 database from on-premises, to a VM in Azure. However, after it was migrated, they no longer needed me and that was the last I heard of or knew about it, […] SQL Server 2019 - Development [Using Dynamic SQL to execute Select from Openrowset with JSON]( - I have some code which will load in and process a JSON file using Select..from Openrowset as follows: Declare @JSON varchar(max) SELECT @JSON = BulkColumn FROM OPENROWSET (BULK 'C:\Users\User1\DR\staging\returns\RET02022203.JSON', SINGLE_CLOB) as j select @JSON i tried to turn this into Dynamic SQL so that I can pass in the path to the JSON file as […] [xp_cmdshell problem]( - Hi, i'm trying to run this command from within the SQL but i get this error 'C:\Program' is not recognized as an internal or external command, operable program or batch file. here is my code DECLARE @CONVERT_HTML_TO_PDF VARCHAR(4000) SET @CONVERT_HTML_TO_PDF = '"C:\Program Files\Microsoft SQL Server\MSSQL15.PRI\MSSQL\monitor_binaries\wkhtmltopdf.exe" "c:\new folder\1.html" "c:\new folder\1.pdf"' EXEC xp_cmdshell @CONVERT_HTML_TO_PDF what is wrong […] [Best way to handle incremental duplicate data in SQL Server?]( - I am processing .csv files daily which has persons data. Every person has a unique ID. My table has FileID and PersonUniqueID columns; it is working fine and saved to parsed table, i.e. with duplicate rows. Now would like to copy the unique data to new tables which is used by other applications. I can […] CosmosDB [To change the maximum throughput of an Azure Cosmos DB for NoSQL container]( - Hello,  I have a question, and please advise. When I wish to change the maximum throughput of an Azure Cosmos DB for NoSQL container from 4000 RU/s to 5000 RU/s, would that be sufficient if I use command: az cosmosdb sql container throughput update command --max-throughput ‘5000’ ???  Tanks in advanced  Reporting Services [Custom Authentication SSRS 2019]( - When trying to setup custom authentication on ssrs 2019 getting "ERR_CONNECTION_REFUSED " and this in the logs  ERROR: Error loading configuration file: The configuration file contains an element that is not valid. The #text element is not a configuration file element. library!DefaultDomain!13d0!08/02/2023-09:54:52:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: , Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration […] [SSRS Integration with Power BI]( - I am trying to integrate SSRS 2022 with Power BI. One of the steps is to go into Report Server Configuration Manager and go to the Power BI integration tab; however, I am not seeing a Power BI integration tab. Is there something special I need to do or install to see that tab?  […] Analysis Services [Date Difference of current record and its relative record using MDX]( - Dear All, I have a some rows where it points to another row in same table based on RelativeId column value. I need to find the difference of these two records date values using MDX. for ex. sample table structure with some values ID - HasRelative - Date - RelativeId 1 - False - 2023-01-01 […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -