Make SQL Server Agent Jobs HADR Aware (2020-10-22)

 SQLServerCentral Newsletter for October 22, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Make SQL Server Agent Jobs HADR Aware]( - [Welcome to Redgate Deploy and Cross-platform Database DevOps]( - [Introduction to DAX Financial Functions – Part 1]( - [From the SQL Server Central Blogs - Permissions script for Azure Synapse]( - [From the SQL Server Central Blogs - Writing Output from Powershell]( Question of the Day - [Required Environment Variables]( The Voice of the DBA  Daily Coping Tip Write down 3 specific things that have gone well recently 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. Conflict in Functional Teams Today we have a guest editorial from Alice Smith as Steve is on holiday. This was originally published on 13 Oct 2016. One of the essential lessons I’ve learnt from my time managing the SQLServerCentral editorial group is that conflict within a team can result in better decision making. Part of running a successful editorial machine is having lively, and open debate. This often means the editors disagreeing with each other over an approach to take, often leading to passionate disputes. When I first started managing this group, I was daunted by this level of conflict, mistakenly viewing the disagreement as a sign of dysfunction. I’d come from a company where the conflict between teams and people created a toxic environment, where people mistrusted each other, and management decisions were influenced by the dominant characters. I’ve heard stories about similar experiences in development teams, where the next feature the team will work on is the one that’s been put forward by the loudest in the room. In these instances, decisions have been heavily influenced by one of two people, rather than the manager having the insight from consulting the wider group. As a manager, fostering an environment of safe conflict, where people feel comfortable and confident to express their views without intimidation means that I get to hear a wide range of views and am in a stronger position to make an informed decision. Patrick Lencioni, author of the [Five Dysfunctions of a Team]( also champions the idea that absence of open conflict can lead to artificial harmony, which can start initiatives off on a weaker footing. I have certainly found that being in a team with this culture leads to stronger buy-in from more people, as you’re not a bystander in the decisions being taken. Sure, this can’t happen with every decision we make, but where we’re able to take the time to truly discuss important issues, I believe teams, and managers, make more informed and well thought through decisions. Alice Smith [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Make SQL Server Agent Jobs HADR Aware]( Steve Rezhener from SQLServerCentral Introduction Always On Availability Groups (AGs/AG... [Welcome to Redgate Deploy and Cross-platform Database DevOps]( Additional Articles from Redgate Until now, organizations wanting to introduce database DevOps have had to rely on – and purchase – different tools and solutions for different databases. A new cross-platform database DevOps solution, Redgate Deploy, changes the rules. Find out why in this latest blog post. [Introduction to DAX Financial Functions – Part 1]( Additional Articles from SimpleTalk Business Intelligence Architect, Analysis Services Maestro, and author Bill Pearson kicks off a series to introduce the DAX financial functions. In this article, he exposes five functions that are popular in the calculation of loans and other annuities. Those accustomed to these functions within Excel will find the syntax, uses and operation of the functions familiar within the Power BI environment. From the SQL Server Central Blogs - [Permissions script for Azure Synapse]( Kenneth.Fisher from SQLStudies I was recently asked to add on to my permissions stored procedures (sp_SrvPermissions, sp_DBPermissions and sp_AzSQLDBPermissions) and create one for ... Continue reading From the SQL Server Central Blogs - [Writing Output from Powershell]( Steve Jones - SSC Editor from The Voice of the DBA One of the things we often want to do when building a PoSh script is to get some output. I first learned about Write-Host, but I learned over time...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Required Environment Variables When I start a Linux container running SQL Server, what environment variables do I need to provide? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by BTylerWhite) Slicing a String in Python I have the following variable declared in Python: ssn = '123-00-4567' I need to print only the last four characters of this Social Security number. Which of the following methods will best accomplish this goal? Answer: print(ssn[-4:]) Explanation: The correct answer is: print(ssn[-4:]) This is an example of using Python's slice constructor. To slice a string, we must specify the start and end indexes, separated by a colon. It's also optional to include a step position. The last character of a string has an index position of -1. The second to last character of a string has an index position of -2. Adding the colon will substring our ssn variable to the last index position of the string. We could also have sliced this variable correctly using the following syntax: ssn[7:11] Another viable option would have been: ssn[7:] It's preferred to use the negative index in this scenario if we're unsure if the structure of the Social Security number will always contain hyphens. This is particularly useful in any scenario where we're unsure of the exact length of the string we're attempting to slice. References: - [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 [problem for the log reader]( - Good morning all I have configured a transactional replication where the disstribution server is remote the snapshot  it works well while the subscriber part does not work I had this i have this eroor The concurrent snapshot for publication 'BR_Traceability_OtherR&D_Repl' is not available because it has not been fully generated or the Log Reader Agent […] SQL Server 2017 - Development [Help with a query]( - Hello I am fairly new to SQL and tying to work out a code. I created the following query and it wont run. The RevenueCD field is located in the [Epic].[Finance].[UBRevenueCode] table and needs to be joined to the Epic.Finance.HospitalTransaction table. The common field in both tables is [UBRevenueCodeID]. When I tried to join them […] SQL Server 2016 - Administration [who's connect using R?]( - Hi, I look after a server and have users connecting with R who are causing alot of performance issues. Is there an easy way to identifying these people. any help welcome:) Thanks, Eamon  [Drop table]( -  i need to delete specific tables "_backup " which took before changes to the table, instead of deleting the table one by one , any script is there to complete the task at ones. SQL Server 2016 - Development and T-SQL [Filling Missing Weekend Dates]( - Hi and first time poster, so please be gentle! I have a table with the following sample data which excludes weekend dates for each employee:- Employee Date       Hours John Doe 15-OCT-20  7.5 John Doe 16-OCT-20 7.5 John Doe 19-OCT-20 7.5 I would like to have the missing dates inserted […] [Un-queried tables]( - Hi, Is there a way to know which tables from a DB are NOT being queried? Context : We do a lot of reporting from multiple data sources (SAP, cloud based apps, etc), so from time to time we require the replication of a bunch of tables for our reporting. Our developers prepare the queries […] [Executing another stored proc inside of a stored proc]( - Hello, Wondering if anyone has any insight into how I can do this: I have a lengthy stored proc which produces a number of metrics that I may need to use for other calculations. This stored proc uses OpenQuery to gather the data. I would like to create another stored procedure that calls this stored […] Development - SQL Server 2014 [Two Tables - Values from one in columns and other in rows w/ data from a third]( - I'm not sure why my other topic was removed (maybe too much code in the post) so I'll try again. I have two tables. We'll call them Subaccounts and AccountCategories. The Subaccounts table has just that - a list of Subaccounts and their descriptions (1 per row). See the attached file (Subaccounts tab). The AccountCategories […] [Identical indexes - one clustered PK, another non-clustered]( - I found a pair of two identical indexes, containing the same column - one is clustered primary key and another is non-clustered non-unique. When I suggested to remove the 2nd index, developers explained that we need both of them. First - for queries with wide column listing in Select clause, and the 2nd one - […] SQL Server 2019 - Administration [Cannot get SQL Server 2019 MDS Model values to display in Model drop list on MDS]( - Cannot get SQL Server 2019 MDS Model values to display in the Model drop list on MDS Website Title Page. Was able to get Models to display in SQL Server 2017 MDS OK. Managed to configure both MDS 2019 Website and MDS 2019 Web Service and Excel MDS 2019 Add-in successfully connecting to SQL Server […] [Get list of databases from failed SQL Server instance]( - We recently had a major SAN failure and completely lost many of our SQL Server instances.  For many instances, we had no user database backups (this was by design due to storage limitations).  However, we have system databases for all of the lost instances. I want to recover a list of the databases originally hosted […] [Find (Count) Joins inside Views]( - Good Afternoon All, I want to be able to count the number of joins used in views. We have a lot of views that are legacy and are awful. I was hoping to provide some kind of insight into what views could be improved or not used anymore -- using the number of joins as […] SQL Server 2019 - Development [SQL Agent and Windows Task Scheduler]( - Hi Guys, Has anyone used windows task scheduler with SQL Agent? I have a FTP which needs to be imported into a database every morning. At the moment we save the FTP as a CSV and currently import that into the database but I'm hoping to fully automate the importing process? I'm hoping widows task […] SQL Azure - Administration [Azure SQL Database and AD Authentication]( - Quick Question. I have a Database that sits on-prem, The database feeds a number of excel reports that are authenticated using the on Prem AD. I which to put this DB in Azure SQL Database keeping the AD authentication. I am guessing ADFS is required from the on-prem to Azure AD but my knowledge is […] Analysis Services [Only 90 Days of Data in Azure Analysis Services]( - I want to make sure there are only 90 days of data in my Azure Analysis Services (AAS) model. I'm thinking about using Power Query/M to only filter the most recent 90 days and then doing a Process Full. Is that the best approach to this? If so, how can this be implemented, especially since […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -