SQLFacts - A Free Toolkit for SQL Server Database Engineers (2021-11-19)

SQLServerCentral Newsletter for November 19, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [SQLFacts - A Free Toolkit for SQL Server Database Engineers]( - [CRUD Operations on a SharePoint List using Python]( - [Searching for Objects in SQL Server Databases]( - [From the SQL Server Central Blogs - Notes on the 2021 PASS Summit]( - [From the SQL Server Central Blogs - Power BI Adding Translations to Rename Columns – XMLA, TOM, C#]( Question of the Day - [Setting up a contained database]( The Voice of the DBA  Daily Coping Tip Choose a different route and see what you notice along the way 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. Where is the Puck Going? During the PASS Data Community Summit last week, [Brent Ozar delivered the day 3 Community keynote](, and it was great. I really enjoyed the format and topic, and I look forward to how someone else might be creative next year with the time. I'd certainly love to do the keynote, though not sure I'd be eligible. I'm hoping a few others get interested and compete for the slot in 2022. One of the things Brent mentioned was about looking for skills to focus on, primarily for "builder," or those that develop new applications. He used [a quote from Wayne Gretzky](: "I skate to where the puck is going to be, not where it has been." The reference is to pick things that will be relevant in the future (where the puck will be), not where things are today. Lots of people ask questions and wonder about this when we look at technology. What should I learn? Where is my best investment for the next ten or twenty years? There also is plenty of fear that you might learn to be an expert in something like Notification Services (dead after 2005) or Database Mirroring (deprecated in 2012). The other day I heard a few people say "graph is dead" in SQL Server, and I might agree. The features exist, but are they really growing and improving enough to compete with a graph platform? I'd argue no. I don't have a crystal ball to tell you what will be popular. I have a magic 8 ball, but that's less helpful. Here are a few things I'd suggest to help you think about this. Please, if you have specific questions, engage in the discussion, and I or someone else can try to help. First, the ability to work in a team is important. The skills to communicate well, debate a point, and collaborate in how you work are important. Not critical because plenty of people can get by without these skills, but when we look to hire new people, often we want to bond. If I find 10 qualified candidates and get along well with 2 in an interview, I'm likely picking one of the two. Learn to speak in small groups, summarize your thoughts, advocate your position, and appreciate others' views. Also learn to accept defeat gracefully when things don't go your way. Second, learn more about the choices your employer has made. Brent mentioned this a bit, but honestly, if your employer is focused on SQL Server on premises or in a VM, learn to run that well. Learn more about how it works and how to solve problems or tune it. If they want to use AWS RDS or Azure SQL Database, focus on those. Spend spare time, or quiet time at work, becoming more proficient with the platform. Read about it and practice on it. It will help you enjoy your current job, increase promotion and raise chances, and give you some confidence if you do need to change jobs. Third, learn to learn. Always be learning, and follow the advice above, or even pick the things that pique your interest. The important thing here is that if you need to pick something up, you want to be comfortable with those skills. How do I search out info on the Internet. Can I find videos or articles to consume and can I take sample code and stat using it? Can I set up new environments? Am I comfortable poking around a cloud portal or digging into a programming API? Maybe most importantly, can I sit through and absorb information from a Pluralsight (or other) course? All good skills. Lastly, part of skating to where you want to go is writing a resume/CV that gets you an interview. I've given this advice, and Brent did as well. Include keywords on your resume. If you decided to use Snowflake, I'd find a way to mention Synapse and Redshift on my resume. If you are good at Snowflake, you are likely going to be good at Redshift and Synapse. What you aren't going to be good at is convincing a non-technical, HR person searching for your resume that these are synonyms if they aren't included in your resume. Maybe you only want Snowflake jobs. That's fine, decline the interview. Maybe you need a new job, and in that case, don't limit yourself. Get past the AI or human filters. Actively managing your career takes some work and effort, and it takes some knowledge. Improve your skills, but take advice from those of us that have walked the paths. Ensure you represent yourself well, both with your avatar (the resume/CV), and in person for the interview. These won't ensure you get a job, but they to help increase your chances. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [SQLFacts - A Free Toolkit for SQL Server Database Engineers]( Wingenious from SQLServerCentral SQLFacts is a FREE suite of 26 (and counting) tools for SQL Server database development, database administration, and performance tuning. The toolkit is all T-SQL code and it includes the functionality of hundreds of short scripts. [External Article]( [CRUD Operations on a SharePoint List using Python]( Additional Articles from MSSQLTips.com This article explains which Python library provides create, read, update, and delete (CRUD) operations on a SharePoint list along with examples. [External Article]( [Searching for Objects in SQL Server Databases]( Additional Articles from Redgate How to get the most out of SQL Search, a free database search tool for SQL Server Management Studio (SSMS) and Visual Studio that will locate database objects based on their names, columns, or text. [Blog Post]( From the SQL Server Central Blogs - [Notes on the 2021 PASS Summit]( Andy Warren from SQLAndy I attended the three main days this year and wrote notes as I went. As I sat down to type them up I started by going back to look... [Blog Post]( From the SQL Server Central Blogs - [Power BI Adding Translations to Rename Columns – XMLA, TOM, C#]( DataOnWheels from DataOnWheels If you are new to using C# and the Tabular Object Model (TOM), please check out the previous blog post ( for both an introduction to the topic and...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Setting up a contained database I have just installed a new instance of SQL Server 2019 and want to run this code: CREATE DATABASE mytest CONTAINMENT = PARTIAL What should I do first? 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 Priority Protocol Which protocol is always tried first from a client, if enabled? Answer: Shared Memory Explanation: Shared memory is always tried first, if enabled. Ref: Client Protocols Properties - [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 [optimize cost sort]( - hello , do you have any idea for optimize the sort object in my table I have only one clustered index in column id I created a nonclustered index on the dateInsertion column but I still have an index browse GO CREATE TABLE [dbo].[BLMachines]( [id] [int] IDENTITY(1,1) NOT NULL, [distinguishedName] [varchar](max) NULL, [samAccountName] [varchar](max) NULL, […] SQL Server 2016 - Administration [DBCC Checkdb cannot finish - Msg 8921, Level 16, State 1, Line 8 Check terminate]( - Hello We have this issue on one of our Prod databases. The database is fully operational but our DBCC checkdb job is failing with the error below: Msg 8921, Level 16, State 1, Line 8 Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. […] [alter partition function]( - Hello, I have a DB with partitions create partition function PF_IdEmpresas (smallint) AS range left for values (1,2,3,4,5,6,7,8,9,10,11,12,13) create partition scheme PS_BASE AS PARTITION PF_IdEmpresas to (BASE01,BASE02,BASE03,BASE04,BASE05,BASE06,BASE07, BASE08,BASE09,BASE10,BASE11,BASE12,BASE13,BASE14) In the filegroup BASE14 there are data with the values 14, 15 and 16 and I want to add a new filegroup, BASE17, for the data with […] Administration - SQL Server 2014 [How to distinguish human principals from all the rest in sys.server_principals ?]( - Hi all, If I run this query: select * from sys.server_principals where type = 'U' and name not like 'NT%' I get a listing of logins of both types: human which are our team-members, as well as like "...reports", "...developers", etc. I am tasked to collect a listing of only human members, but there is […] SQL Server 2019 - Administration [Missing 'sys.sp_MSIsContainedAGSession' causing failure in CU13 update script]( - After upgrading installing Cumulative Update 13 for Microsoft SQL Server 2019 (KB5005679), the server failed to start. Error logs showed that this was because the upgrade script msdb110_upgrade.sql attempts to run stored procedure 'sys.sp_MSIsContainedAGSession'. However, the server has no such stored procedure, nor do our other, non-upgraded instances have it. Error: 2812, Severity: 16, State: […] SQL Server 2019 - Development [Percent Rank]( - Hi Everyone I have an Access query that computes the percentile for a given set of values. The key is that the calculation is based on a rolling window of the past X days. For example, if calculated today, it will look at the last 30 periods (including today's date) and then determine the percentile […] [Output results to a table]( - Hi everyone One of my queries is running too slow so I would like to output the results of the subquery into a table and then use that table in the main query. Not sure if this will improve performance or not but it is worth a shot. I know it made a difference in […] [Reference Query within a Query]( - Hi everyone This is definitely a rookie question. In Access, I can reference another query by just using its name. I tried to do the same thing in SQL Server and I got an error. Here is my query: SELECT * FROM QRY_DATES The message I got was Msg 208, Level 16, State 1, Line […] [View Index]( - Hi everyone I am in the process of moving over Access queries to SQL Server. One Access query took close to 2 hours to run. Once moved over to SQL Server and using Columnstore Index, the same query took about 6 seconds. Wow! I have created a view to so I can reference it again. […] [Show Queries]( - Hi everyone I am moving from Access to SQL Server so I am learning more about SQL Server. In Access I can view the objects for my project (such as tables and queries). Is there a way to show the queries I have written in SQL Server so it shows up in the Object Explorer […] SQL Azure - Administration [Setting up Replication on a SQL Managed Instance]( - Hello, I have searched and found some articles on setting up replication in SQL MI between SQL Instances and going to on-premise but is there any article on how to setup just to replicate to a database on the same Managed Instance? I think the issue I am running into is with the setup\permission of […] Azure Data Factory [Removing specific rows in an Excel file using Azure Data Factory]( - I have a set of excel files inside ADLS. The format looks similar to the one below: The first 4 rows would always be the document header information and the last 3 will be 2 empty rows and the end of the document indicator. The number of rows for the employee information is indefinite. I […] Reporting Services [Reports upgraded to SSRS 2019 on WIN 2019 server is slower compared to SSRS 2008]( - We are upgrading SSRS at QBE from SSSR 2008 on Windows 2008 R2, to SSRS 2019 on Windows 2019 for the Report Server and Catalog DB . We’ve done the upgrade and have hit a re response time issues, where we have noticed degradation. Question: why is running the same reports (and fetching the parameters) […] Strategies and Ideas [Multiple sources for dimensions in Data Warehouse]( - I am currently working on a financial Risk data warehouse. For my collateral dimension, I am souring the data from one source system. However, after further research by the business analyst, we found a legacy application that also holds collateral information which the bank also needs in the data warehouse. Bar a few common attributes […] Integration Services [Excel file changing hourly]( - I have some excel files that got imported to a certain folder through ftp. this files are going to that folder hourly. every time an excel file goes into this certain folder it has all the accumulations of the excel files before, so only the most recent one matters. this basically means the moment i […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -