How to Move System Databases to a New Physical Directory (2020-05-25)

 SQLServerCentral Newsletter for May 25, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [How to Move System Databases to a New Physical Directory]( - [Learning New Skills]( - [New release: SQL Clone 4.4 supports your Database DevOps process]( - [From the SQL Server Central Blogs - Find Indexes Used In Query Store]( - [From the SQL Server Central Blogs - Explain CROSS APPLY like I’m 5.]( Question of the Day - [Rounding and Sums]( The Voice of the DBA   Daily Coping Tip Share photos of 2 things you find meaningful or memorable 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. Building Better Software for Everyone When I first started to use the Internet, everything was text. We used [Archie]( and [Gopher](, crawling through [Usenet lists](, with most everything in text format. When the [WWW]( gained popularity with Netscape and visual browsing, this was just a new way of getting information for me. It made sense, and seemed easy. These days, using the WWW is something many of us do without a second thought, browsing on mobile devices as well as large computer screens. Many of us have become used to transacting business and using software as a tool throughout much of our lives. However, that's not easy for many people, including lots of those with some sort of disability. To them, much of the content available seems disjointed and disconnected. Not too long ago the [US Supreme Court let a ruling stand]( that notes the Americans with Disabilities Act applies to online content. Plenty of web developers and designers might decry the additional work they may have to undertake to comply, and certainly lots of companies would rather not spend any resources, or few, in this area. However, this is something that has started to matter more to me over time. I'm getting older. At 50+, my eyes and ears don't work that well. I find myself struggling at times to consume the information on a screen. More than once I've gotten annoyed with the text on the screen and tried to zoom in, only to have everything get larger, requiring me to scroll left and right to read something. What happened to the reflow of text on the WWW when we zoom in? There are ways to change font sizes, and I find myself taking advantage of them more and more. However, there are many people with disabilities that are harder to overcome and they expect that websites and apps have accessibility features. I've seen no shortage of complaints that too many software designers are highly connected, physically capable 20 and 30 year olds, who design for themselves, not for a wider audience with varying needs. I am trying to do better. I don't want perfect to be the enemy of good, and I know that some of the content on SQLServerCentral isn't as accessible as it could be. When I find things pointed out, I work to change them, and I also am trying to do better with images and media content to be more compliant. As I've grown older, I appreciate more and more that my work gets used by many people, and the way they interact with the computer isn't the same as mine. Accommodating them just seems like the right thing to try to do. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [How to Move System Databases to a New Physical Directory]( Kim Killian-SiteDataView from SQLServerCentral What is the easiest way to move SQL Server system databases. Let me walk you through it! [Learning New Skills]( Additional Articles from SQLServerCentral It’s a great time to learn a new skill or improve your existing skills if you have the extra bandwidth while staying at home. [New release: SQL Clone 4.4 supports your Database DevOps process]( Additional Articles from SQLServerCentral How SQL Clone 4.4 enables fast onboarding for Database DevOps The latest version of SQL Clone showcases new integrations with our DevOps tooling. Join Microsoft MVP, Kendra Little, to learn more about how cloning technology can enhance your Database DevOps process. From the SQL Server Central Blogs - [Find Indexes Used In Query Store]( Grant Fritchey from The Scary DBA One of the most frequent questions you’ll hear online is how to determine if a particular index is in use. There is no perfect answer to this question. You... From the SQL Server Central Blogs - [Explain CROSS APPLY like I’m 5.]( Kenneth.Fisher from SQLStudies A good friend of mine (Randolph West (blog|twitter) asked that someone Explain CROSS APPLY like they are 5. So, here’s ... Continue reading   Question of the Day Today's question (by Steve Jones - SSC Editor):  Rounding and Sums I've got this code: WITH numberCTE (n) AS (SELECT n FROM (VALUES (3.7), (-2.1), (2.9), (-3.1) ) A(n) ) SELECT ROUND(SUM(n),0), SUM(ROUND(n,0)) FROM numberCTE Do you expect these two values in the result set to be the same? 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) Counting Some Rows I have two tables that are related. In my OrderHeader table, I have this data: [OrderHeader Data] I have this data in the OrderLine table: [OrderLine table data] These two tables are linked with the OrderKey in my Power BI Model. I want to add a column to the OrderHeader table that will keep the total number of rows from OrderLine that match the OrderKey for that row. What function should I use for my new column? Answer: COUNTROWS(RELATEDTABLE(OrderLine)) Explanation: The RELATEDTABLE() function will filter the rows for related tables. This will result in this data: [Related table results] Ref: - COUNTROWS - - RELATEDTABLE - [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 [Restoring a database backup from SQL 2000 to SQL 2017 (via SQL 2008) - not good?]( - We are upgrading our old SQL 2000 box to SQL 2017. I know you cannot restore a backup from 2000 directly to 2017, so we are first restoring it to a SQL 2008 box, backing it up from there and then restoring that to SQL 2017. When we restore that to SQL 2017 will that […] [Performance regression after upgrade to 2017]( - Hi, We recently upgraded our production DB server from 2008R2 standard edition to 2017 Enterprise edition. we have noticed queries that would run for couple sends are taking long time to run and causing timeouts in applications. I have rebuild indexes and updated the statics. I checked the sql server logs to see if there […] SQL Server 2017 - Development [Convert Date field to YYYYMMDD from YYYY-MM-DD in SSIS]( - Hi, I've got a table containing Dates in YYYY-MM-DD which I need to change to YYYYMMDD in SSIS. I've made some progress after watching this video from Steve Fox. I'm using SSIS & this all works OK until I want to create a Derived Column on a Date field. In the video, some expressions are […] [Remove or Replace unwanted Characters from a table]( - Hi, I have some Tables that I want to transform in SSIS. Any occurences of more than one comma or other Character (|,/ etc..) should be removed leaving just one. This needs to be applied to the whole Table. Can anyone advise the best way to do this in SSIS? Thanks SQL Server 2016 - Development and T-SQL [stored procedure insert into a table -parameter containing the name of the table]( - Hi, I have 10 tables each with 2 columns (Id- identity, Name_xxxx) The name of the second column is different from a table to other. I want to write a stored procedure to insert a record into the table, but with 2 parameters in input (the name of the table and the value) exec sp_InsertIntoTable […] Administration - SQL Server 2014 [Problem with a heap table]( - I inserted in a heap table millions of rows. When finished, i executed select t2.name,t1.name,t3.name,t3.rows from sys.sysobjects as t1 inner join sys.schemas as t2 on t2.schema_id=t1.uid inner join sys.sysindexes as t3 on t3.id=t1.id where t1.xtype='U' and t3.status & 64 = 0 and t3.status & 16777216 = 0 and t3.status & 8388608 = 0 and t2.name='dbo' […] [Duplicate statistics - execution plan disruption]( - Hello, I will need your insight on the content of the execution plans in relation to the statistics. Request 1: do the values ??in the section only contain the statistics actually retained for the plan in question or is it the list of all potentially useful statistics? Request 2: for a particular step, how […] SQL 2012 - General [Timeoffset calculation logic]( - Please help me with the timeoffset logic calculation. I get time offset and start time of a server as input. My requirement is to show the result to the user after 1 hr and 30 mins of start time. I'm unable to do the timeoffset logic calculation based on this data. My DB runs at […] SQL Server 2012 - T-SQL [Arithmetic Overflow Error]( - I am getting an Arithmetic Overflow error on this field, suggestions on a fix?  CAST(ROUND(CAST(COALESCE(r.ActualAllowed * 100.0 / NULLIF(r.Allowed, 0), 0) AS FLOAT), 1) AS VARCHAR(10)) + '%' AS FeeSchedulePercentDiff SQL Server 2019 - Administration [SSAS - Query time when processing - works with 2017 - timesout with 2019]( - Hi, I'm wondering if someone can help us out here as we are at a loss. We have at present a process which works like this; 1. Server A (SQL 2016) kicks off an SSIS package which is locally stored as a .dstx file 2. That package connects to Server B (SQL 2017) which has […] [connecting to named instances from sql server to sql server, firewalls]( - I am setting up a Win2019/Sql2019 server. Setting up the firewalls for the default services are fairly straight forward using the article: All the information is for inbound connections. I'm trying to connect to a named instance through the firewall which is an outbound connection. I can add a specific firewall rule for each […] SQL Server 2019 - Development [Query JSON data]( - I have a field in my DB that has a string of JSON data.  This is an example: [{"id":"36653","invoicenumber":"8224131","billinginvoiceid":"948439"},{"id":"36651","invoicenumber":"8224133","billinginvoiceid":"948437"},{"id":"36652","invoicenumber":"8224134","billinginvoiceid":"948438"},{"id":"36654","invoicenumber":"8224155","billinginvoiceid":"948440"},{"id":"36655","invoicenumber":"8224161","billinginvoiceid":"948442"}] As you can see, there are basically 3 fields: ID, invoicenumber, and billinginvoiceid.  This string can have 1 record or 1000 records, there really isn't a limit.  I need to be able to query that field […] [Invalid column name error]( - I am trying to delete duplicate rows, but I get an error message saying that 'RowNumber' is an invalid column even though I have that column (as an alias). I tried running the SELECT and DELETE statement together and it still didn't work. I know it doesn't exist in the base table because it is […] SSRS 2016 [how to get fixed length file out of SSRS]( - In rsreportserver.config file, how can I set CSV export to have a field delimiter of none So that I can extract a fixed length file? I tried keeping it empty but it gives commas after each field value. Also if possible, please provide the script to get fixed length file and some of the fields […] COVID-19 Pandemic [Daily Coping 15 May 2020]( - Today’s tip is to show your gratitude to people who are helping to make things better. My thoughts:   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -