ASCII Text Dump (2021-01-15)

 SQLServerCentral Newsletter for January 15, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [ASCII Text Dump]( - [Using SQL Compare and SQL Data Compare within a PowerShell Cmdlet]( - [Azure Blob Storage Data Upload with SSIS]( - [From the SQL Server Central Blogs - Connecting to SharePoint Lists with SSIS]( - [From the SQL Server Central Blogs - Speaking at C# Corner’s Virtual Conference]( Question of the Day - [Checkout One File]( The Voice of the DBA  Daily Coping Tip Find three good things to look forward to this year 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. Improving Availability Groups [Availability Groups]( (AG) were introduced in SQL Server 2012, with the idea that we could dramatically improve (and ease) the burden of dealing with high availability in SQL Server. At the time the (code named) HADRON technology seemed full of possibilities. Since then, there have been some enhancements, but it seems that setting up and managing an AG, especially across subnets, isn't as simple as Microsoft would have us believe. One of the problems with AGs is that there are non database resources (logins, jobs, etc.) that create dependencies. Working around the issues is a headache for many administrators, and it shouldn't be. While there are [some enhancements potentially coming](, I don't know what shape these will take or if they will make things easier. Some of my previous work as a DBA relied heavily on SQL Agent and jobs, neither of which are handled by AGs, or by plenty of other HA/DR technologies. Instead, administrators cobble things together, save scripts, and manually repair issues after failover. To me, this is one area that I'd hope Microsoft enhanced for AGs. Another area is the listener, which seems to be brittle. It works great, or it's a nightmare to get working, without always an easy way to debug. I'd certainly welcome improvements here, including the ability for tooling to support multiple listeners easily. Many of you work with AGs now, and many more of you may in the future as the need for HA grows all the time for databases. Are there improvements you'd like to see in AGs, or any other SQL Server HA/DR technology? Feel free to [leave a comment]( or [submit something to Microsoft](. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [ASCII Text Dump]( saga from SQLServerCentral Introduction Recently, I had the task of analyzing a varchar field for special characters. The field contained text entered by the user with a standard US keyboard; however, the proprietary software was breaking up the text and delimiting it with characters whose ASCII values were above 127. Eyeballing the text that I dumped using a […] [Using SQL Compare and SQL Data Compare within a PowerShell Cmdlet]( Additional Articles from Redgate SQL Compare and SQL Data Compare can be used together, from the command line, to provide a complete build process, or to script out changes to both the database and its development data. For doing this routinely, I find it easiest to script the operation using PowerShell. [Azure Blob Storage Data Upload with SSIS]( Additional Articles from MSSQLTips.com In this article we look at how to upload and download files from Azure Storage using SQL Server Integration Services. From the SQL Server Central Blogs - [Connecting to SharePoint Lists with SSIS]( Tim Mitchell from Tim Mitchell SharePoint lists are popular and simple tools for storing modestly-sized discrete sets of data. SQL Server Integration Services (SSIS) includes a source component to allow reading data from SharePoint... From the SQL Server Central Blogs - [Speaking at C# Corner’s Virtual Conference]( Steve Jones - SSC Editor from The Voice of the DBA C# Corner is holding a virtual SQL Server conference on Jan 29-30, 2021. This is a charitable event, aimed at raising money for children affected by COVID-19. I’ll be...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Checkout One File I am working in git and I get an email that another developer has changed the GetCustomer.sql file in his featureCustomerUpdate branch. I am working on the featureOrderChange branch. How can I easily get the changes another developer made in his branch into mine? 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) The Strict Replace What is returned from this code? SELECT REPLACE('I do not want a chair' COLLATE Latin1_General_BIN, 'Chair', 'desk' ); GO Answer: I do not want a chair Explanation: The COLLATE command causes the string to be evaluated with a specific collation. In this case, the binary collation is used, and in that collation, "chair" does not match "Chair". Therefore, no replacement is made. Ref: REPLACE - [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 [Performance]( - I am assisting with a migration. doing a V to V migration from a legacy environment to the hosted infrastructure. I want to do some baseline testing and wondered if there are some scripts i could run in SQL that i could run on the legacy and on the new environment to compare so I […] SQL Server 2017 - Development [Temp Table Strategy]( - I have a set of reports - with user defined criteria. And an optional set of statistics which are produced on the report. These are not db table stats, but things like aggregates, trends and statistical analysis of the data. The happy user then downloads their data and moves on to something else. The reports […] SQL Server 2016 - Administration [Statistics issues]( - Hi All, Looking for some clarifications on UPDATING STATISTICS. In our production env, we have 3 TB database with Auto_create stats option is set to true. Every weekend on Saturday midnight we run update stats with full scan. However, during the mid of the week, application developers keep complaining that few queries are running slow. […] [memory utilisation]( - How can we calculate the memory and cpu utilisation of sql for the last 3 months ? SQL Server 2016 - Development and T-SQL [Update large table rows within a group]( - I have a large employee table with lots of columns and versions and one org lookup table also with some versions. I need to update a column org_id in employee table  which is all nulls now with no history. An employee can have multiple versions records each with same empid but same or different rc_code. […] [CAST(NULL AS varchar(1))]( - Hello All, I have been tasked to improve a very old SQL code we have and first thing I see (may not necessarily be the candidate for performance improvement though) going through the code is many CASE records with CASE WHEN myColumn IS NULL THEN CAST(NULL AS varchar(1)) .... Can one advise why someone would […] SQL 2012 - General [SSIS ForEach Loop not finding file]( - I used to know how to do this, I think, but I wonder if my memory is playing tricks on me. I need a ForEach loop to find a file that the name changes daily. Normally, I'd use a wildcard (*), but in this case it doesn't seem to be working. I've searched all my […] SQL Server 2019 - Administration [SQL Permissions]( - My understanding is you can't grant access to users with select, View database and view definitions etc. at instance level without any user databases created on it. Please advise? [authgrowth log during rebuil index]( - Good morning all , Have any idea how I can reduce disk consumption during the reindexing phase I use the IndexOptimize procedure of the olla script  SQL Server 2019 - Development [Better way to flatten meta type data?]( - I have the following table how would I be able to get a view similar to SELECT [ID], [NAME], [Imaging], [Long Term], [Auto], [Factory], [Central Office], [Pizza] FROM [Some SQL Magic Code]; The best I've come up with is multiple selects with where clauses to create tables and then join all of them. I'm not […] [Filling in Empty Rows]( - Given a date parameter I've a query that will return a two column result set. The first column is a number (1 -6) that represents a condition and the second column is a number that represents the count of records in that condition. A typical result set may look like this: 1, 30; 2, 100; […] SQL Server Newbies [SQL update to replace undetermined string value]( - Hello, I have a varchar column called "Message". Sometimes, this column contains a bit of text that begins with "**" and ends with "**". There can be text before and after, but what I'm looking to do is only remove the bits of text that follow the "**%**" pattern (which should only ever happen once […] Reporting Services [Connection Pool Timeout with Data Driven Subscription]( - Hi, Each morning, we run several data driven subscriptions which generate about 10,000 reports and email them as excel attachments. Recently we have resolved some issues we had with the subscriptions (such as bad email addresses) that were causing subscription errors. Most days, we run through these subscriptions error-free. Some days, we get a few […] Analysis Services [SSAS Tabular SSDT DAX Drillthrough Issue]( - Hello, I've come across a scenario where the drillthrough functionality is not working when multiple dates from a fact table is joined to a single date dimension. If I have a fact table with multiple status dates e.g. Resolved Date, Logged Date and join it to one Date dimension table, one of the relationship would […] Integration Services [SSIS Custom Task for creating Excel with formulae]( - Hello everyone, I am currently looking for a way to create Excel reports that include formulae (probably also Pivot tables and other stuff) from Reporting Services. Searching the web and the forums on SSC I found that therer is no simple way to achieve this. Would it be possible to do such a creation in […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -