Building SQL Memorial - Automating Updates (2021-01-28)

 SQLServerCentral Newsletter for January 28, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Building SQL Memorial - Automating Updates]( - [Finding Inactive SQL Server Databases]( - [Power BI Theme Generator New Methods and Customizations]( - [From the SQL Server Central Blogs - Server Review Essentials for Accidental and Junior DBAs—Setting Up (Part 1)]( - [From the SQL Server Central Blogs - Platforms for Events]( Question of the Day - [String Shrinking]( The Voice of the DBA  Daily Coping Tip Take a small step towards an important goal I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Impact Minutes When I started working in operations departments, we were always concerned about downtime. In a simpler world, often inside an organization, this meant was a particular machine working or not. We did have networking issues at times, but often we were measured by how often a server was not reachable from clients. These days, with many machines often involved in backing an application, downtime can be a debate, but often we have particular places from which we can test if an application is down. Some services, like Slack, might [test multiple parts of the application](, which I like. However, ultimately for any of these, there could be a simple (up/down) or complex (up, down, degraded, maintenance, etc.) status. I was listening to a DevOps talk recently from an Operations group that talked about how they prioritize and triage work. There are times that the amount of work during an incident overwhelms resources, so that they need to decide what to work on first, or who needs to work on what. This group had the concept of impact, which essentially was a product of two values, downtime and blast radius. Blast radius was essentially the number of people affected, though sometimes this was weighted. Finance or sales person impact might be greater than average employee impact. They would do a calculation and decide where to focus time. If one part of a website of say, 4 parts, was down, the impact could be lower than if the database is down. If a database is down, but 10 people are affected, this could be less important than a network issue affecting 100 people. I think I've often intuited the number of people affected, but rarely have I thought about this directly. To me, this is a good calculation to have handy, with an awareness of how heavily the various systems are being used. While most of us aren't supporting something as widely use as Slack, we often are supporting both big and small systems, and having a way to rank the relative importance is handy in a crisis. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Building SQL Memorial - Automating Updates]( Steve Jones - SSC Editor from SQLServerCentral In my last article, I wrote about the SQL Memorial structure for publishing information on the Internet for others to view. I had chosen Jekyll as a way of taking information in files and publishing it as a good looking website. However, I don't want to manually run Jekyll and copy the results to a […] [Finding Inactive SQL Server Databases]( Additional Articles from Redgate Phil Factor demonstrates how to use Extended Events to detect acquisition of Intent locks, and so determine which databases are active or apparently 'dormant', and then on which tables data has been modified recently, or which views and stored procedures have been run. [Power BI Theme Generator New Methods and Customizations]( Additional Articles from MSSQLTips.com New methods and usage for themes were recently introduced for Power BI and in this article we cover how you can use these and customize them for Power BI. From the SQL Server Central Blogs - [Server Review Essentials for Accidental and Junior DBAs—Setting Up (Part 1)]( Kevin3NF from Dallas DBAs Welcome to a new year, and a new look at what I’ve learned in my time as a beginner and junior DBA. So, I’ve been in this DBA gig... From the SQL Server Central Blogs - [Platforms for Events]( Andy Warren from SQLAndy in my last post I wrote about thinking of the tools as being a separate thing from the event. Not a complicated concept, but does it matter? Maybe. Let...   Question of the Day Today's question (by Steve Jones - SSC Editor):  String Shrinking I have a lot of data in my column that contains spaces. I decide to update the column like this: UPDATE dbo.strings SET stringval= TRIM(stringval) When I look at the data values, what do I see? 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) Python Dictionary to DataFrame I have this dictionary, which I want to convert to a dataframe. Mydict = { 'Broncos': [5, 12], 'Chiefs': [14, 2], 'Chargers':[7,9], 'Raiders': [8,8]} Which code will do this (assume I have pandas loaded)? Answer: df = pd.DataFrame(Mydict) Explanation: The pandas module has a DataFrame method. If you pass in the dictionary, you will get a dataframe back out. Here is the result of printing the dataframe from the code in answer 4. Broncos Chiefs Chargers Raiders 0 5 14 7 8 1 12 2 9 8 Ref: pandas.DataFrame - [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 [Grant user access to view database only]( - Hi I need to grant a user access to a DB on the server with read only access to view all tables and views the user is a domain user so I set them up in logins and gave them data reader only to the db what else do I need to do as when […] [Snapshot Folder is not deleting automatically in Transactional Replication.]( - Hi Team, I created transnational replication to replicate the data from one server to other server.I am daily generating snapshot for some of the tables on snapshot folder.But old folders are not getting deleted from snapshot folder.. any thought? SQL Server 2017 - Development [Lookup and insert into column without dynamic SQL?]( - Hi all, 99% sure the answer to this question is "it is not possible", but figured I'd check anyway. Suppose I have the following schema: CREATE TABLE #FieldValues ( ID INT IDENTITY PRIMARY KEY, Field1 VARCHAR(500), Field2 VARCHAR(500), Field3 VARCHAR(500) ) CREATE TABLE #FieldDefinitions ( ID INT IDENTITY PRIMARY KEY, DefinitionName VARCHAR(50), FieldName VARCHAR(50) ) […] SQL Server 2016 - Development and T-SQL [Looking for tips on Optimizing]( - I am probably going about this the wrong way, but my current solution is by using dynamic SQL and a loop. Table creation: DECLARE @dataTable TABLE (Item VARCHAR(255), Quantity INT, WeekStart DATE) DECLARE @headerTable TABLE (Item CHAR(4), Date1 VARCHAR(25), Date2 VARCHAR(25), Date3 VARCHAR(25)) HeaderTable actually has columns Date1 through Date52. I excluded those for sake […] [Find unmatched between 2 tables using field as selection criteria]( - I have the following tables as a example. Table 1 EmpID, LastName, FirstName 1,Smith,John 2,Jones,Bob 3,Citizen,Jane Table 2 EmpID,ReqID,ReqDesc 1,1,Car 1,2,Diploma 1,3,Phone 2,1,Car 2,3,Phone 3,1,Car 3,2,Diploma 3,3,Phone I want to return all records from Table 1 that don't have an entry in Table 2 that don't have a Diploma for example so ReqID = 2 […] Administration - SQL Server 2014 [Do I need to install two patches for 2014 individually?]( - We are on version SP3/CU4 for SQL Server 2014. Since CU4, there have been two security patches released, one on 2/11/20 and the other recently on 1/12/21. If I install the latest 1/12/21 security patch, will it "include" the patch for 2/11/20, or do I need to install both of them (in sequence)? Development - SQL Server 2014 [Need to strip off the beginning of a field in SELECT statement]( - I have this table that unfortunately has spaces in the field, which I am trimming using LTRIM(RTRIM(Field)) on. In addition, sometimes the field has "1101" at the beginning of the Field. Is there a way to remove it if it's there? Current SELECT statement: SELECT LTRIM(RTRIM(Field)) FROM myTable Example field values: 1101BRZ INSP etc. [Save results from Stored procedure to a CSV file.]( - I have a SqlAgentJob calling a stored proc. that loops through a list of locations and passes the location value and other parameters to a stored procedure. I would like to save each of the results to a CSV on the server. Because i have 50+ locations my current method of a separate sqlAgenjob using […] [Execute As not reading system view]( - I have a stored procedure with the following: SELECT physical_name FROM sys.master_files Where physical_name like '%' + DB_NAME() + '.%' When the procedure is compiled normally, this runs fine, but when it is compiled With Execute As 'Paleo-Server\SQLServerZaloha', this select statement returns a null. I ran this statement I found on the web: grant view […] SQL 2012 - General [Aliasing an instance name for remote connections]( - It's been a long time since I set up a 2012 server. We have an emergency where we have to set up a new one but can't upgrade for a few months, so we're doing another 2012 and I'm having issues with the SSMS connection string. Most of our servers we can connect remotely to […] [Do I need to install two patches for 2012 individually?]( - We are on version SP4 for SQL Server 2012. Since SP4, there have been two security patches released, one on 2/11/20 and the other recently on 1/12/21. If I install the latest 1/12/21 security patch, will it "include" the patch for 2/11/20, or do I need to install both of them (in sequence)? SQL Server 2012 - T-SQL [Summary query for number of attendees out of all]( - hi all i'm trying to write a query to summarize attendance counts per department i've two tables one for event log (login/logout) transactions and the other for users details i need to show the result as dept.   attend users   all users dept1       5    […] SQL Server 2019 - Administration [MYSQL ERROR - need desperate help]( - Hi, Im having a problem with new players selecting a player class. Keep getting this error. Message: Error Executing Database Query. URL: /ClassSelect.cfm? Location: C:\websites\myConvicts\www\ClassSelect.cfm Line #: 36 SQL: INSERT INTO DR_Player_Upgrade (PlayerID, UpgradeID, PurchaseDate) VALUES (2930848,27,CURRENT_TIMESTAMP) [Macromedia][SQLServer JDBC Driver][SQLServer]Violation of PRIMARY KEY constraint 'PK_DR_Player_Upgrade'. Cannot insert duplicate key in object 'dbo.DR_Player_Upgrade'. The duplicate key […] Reporting Services [Search Report definitions]( - I know that there is a query you can run in SQL to search the SQL text procedures & objects for a specific string. Is there a way to search the report definitions of all reports for a specific string (values, table references, etc.)? Analysis Services [DAX how to rewrite this FILTER to CALCULATETABLE?]( - Ahoi,  i have been starting to look into DAX and there is something i could not find an answer to somehow. I am very new to DAX so im trying to understand the basics and have come across something bothers me. How do i rewrite the following Query using CALCULATETABLE instead of FILTER?  […]   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -