Stairway to SQL Server Agent: Level 1: Setup and Overview (2020-02-19)

 SQLServerCentral Newsletter for February 19, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to SQL Server Agent: Level 1: Setup and Overview]( - [SQL Server CASE Statement Example]( - [Quickly Find and Mask all Sensitive Data with Data Masker for SQL Server]( - [Free eBook: Defensive Database Programming]( - [From the SQL Server Central Blogs - T-SQL Tuesday #123 – Life Hacks]( - [From the SQL Server Central Blogs - Power BI Real-time Streaming]( Question of the Day - [More Data]( The Voice of the DBA  The Soft Skill of Respect This editorial was originally published on March 14, 2019. It is being re-run as Steve is on sabbatical. When I started to work more closely with the product development teams at [Redgate Software]( I found it interesting that we devoted so many resources to user experience, testing, and other non-development tasks. Coming from more focused groups, this was new to me. At times this felt like an indulgence, and at times a lot of overhead that slowed down the coding process. Over time, I've grown to appreciate having different skills in our teams. Not that we build software in the best way possible, and certainly not the quickest, but we have some well coordinated and knowledgeable teams. We're still evolving and learning how to put together teams of people and effectively build software, but I think we're moving in the right directions. As soon as I read [this piece on hard and soft skills]( I thought of Redgate and all the non-coding people that build our products. What struck me was how often I'd devalued non technical skills, but the more I work with our (and other) software, the more I appreciate that those other roles are important. The piece rambles and wanders around the point, but I do see that there are a couple important takeaways I get from this. First, we need to respect others that perform different work from us if we are going to build synergy in our teams. The synergy is creating something that none of us could do individually, or even separately. We get more done by working together and that means we must be a team. Teams only come from respect between individuals, and that includes the managers. The second thing that I realized, is that it is important to understand a basic level of what other jobs entail. Too often we data professionals have been upset that application developers don't know more SQL. Many of us learn some of the domain specific knowledge of the data we deal with, in order to talk with customers, produce reports, etc. Following our own advice, we ought to know more about the development platforms, the challenges, their testability, and more, if for no other reason than we can better understand how the entire system works and can talk about it with others I certainly know that relationships and people are incredibly important in larger projects, which is another important takeaway from the article. We all do need to improve our hard skills, learning to code more efficiently and securely. We also need to remember the importance of softer skills, and the need for a wider variety of skills in our teams. A little appreciation for the need for more than expert level coders might help us create better teams that function more efficiently and help us produce better software. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums]( [Redgate SQL Monitor](   Featured Contents [Stairway to SQL Server Agent]( [Stairway to SQL Server Agent: Level 1: Setup and Overview]( Richard Waymire from SQLServerCentral.com SQL Server Agent is a Microsoft Windows service that allows a DBA to automate administrative tasks. SQL Server Agent can run jobs, monitor SQL Server, and process alerts. The SQL Server Agent service must be running before any jobs scheduled to execute automatically can be run [SQL Server CASE Statement Example]( Additional Articles from MSSQLTips.com While writing T-SQL code, sometimes it is necessary to divide the retrieved data into categories and perform specific operations based on their category. [Quickly Find and Mask all Sensitive Data with Data Masker for SQL Server]( Additional Articles from Redgate Khie Biggs, a software developer on the Data Masker team at Redgate explains how a recent set of Data Masker improvements should make it significantly easier and faster to determine what data needs to be masked, implement a masking plan, and then to apply the masking operation, to protect sensitive and personal data in all the tables and columns of your SQL Server databases. [Free eBook: Defensive Database Programming]( Press Release from Redgate Resilient T-SQL code is code that is designed to last, and to be safely reused by others. The goal of defensive database programming, the goal of this book, is to help you to produce resilient T-SQL code that robustly and gracefully handles cases of unintended use, and is resilient to common changes to the database environment. From the SQL Server Central Blogs - [T-SQL Tuesday #123 – Life Hacks]( taboggiano@gmail.com from Database Superhero’s Blog Jess Pomfret is hosting month’s T-SQL Tuesday, and asked us to share our favorite life hacks – something that makes our day easier.  I have a few,some people have told... From the SQL Server Central Blogs - [Power BI Real-time Streaming]( James Serra from James Serra's Blog With Power BI real-time streaming, you can stream data and update dashboards in real-time. Any visual or dashboard that can be created in Power BI can also be created...   Question of the Day Today's question (by Steve Jones - SSC Editor):  More Data I'm compiling some data from various sources, and I have this so far. > Avengers ID Age Name 1 1 34 Black Widow 2 2 48 Iron Man If I want to add a row that has these values: (3,100, "Captain America") Which of these should I choose? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Kendra Little) SELECT and Locking For a SELECT query running under the default “pessimistic” implementation of the read committed isolation level, locks are held.... Answer: For a very short time Explanation: I like to say that the default implementation of read committed “loves the row or page it’s with.” The default implementation of this isolation level in SQL Server is pessimistic, meaning that locks are issued even on reads to protect the data so that it’s not modified while you are reading it. However, read committed doesn’t hold these locks long for reads. If you’re scanning a heap or index, this means that data may move around while you are scanning it (if it isn’t the row or page you are reading at that exact moment). This can lead to weird phenomena like reading rows twice, missing rows entirely, and other incorrect results. A good resource to learn more about this is Microsoft’s “[Transaction Locking and Row Versioning Guide]( [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 - Development [Recursive CTE vs UNION ALL in a VIEW]( - Hi, I'm trying to improve a VIEW which is made of several UNION ALLs, something like this: SELECT 0 as OPNR, nivdop1.nr as DOPNR, 0 as ANTL, (nivdop1.ANT) / sumniv1.SUMANT as ANT, 0 as ANTS, nivdop1.SVIND, nivrv1.nr as RVNR, nivrv1.NAVN as RVNAVN, nivrv1.TYPE, 1 as NIVEAU, 0 as PARENT, nivdop1.LINE, 0 as PLINE from DBDOPSK […] [How to preserve leading zeros with insert into]( -      Below is the create table script and the insert into statement. Both work fine in SSMS. However, the insert into drops my leading zeros. These are important because I have a query that I want to test out that clearly specifics certain character counts and values. I am hoping to […] [FUN only, solving a Puzzle.]( - /* For FUN. I like puzzles, I like T-SQL coding, I like to show off :-), so why not combine these. This project was for FUN only, but I did learn new things. Goal: Build a Sudoku solver with the minimum amount of script. (Minimum number of lines or characters). It does have to solve […] SQL Server 2016 - Administration [Update Statistics is causing DB file to grow in always on envrionment]( - Hello! When I check what is causing DB files (.mdf) to grow, it is showing as Update Statistics. DB files are auto-growing by 1 GB. In about a month, it has grown by more than 10 GB. Could you please let me know the reason and how to prevent it from this?  Thanks in […] [Looking for recommendation on "cost threshold value"]( - H All, What is the recommended value for setting sp_configure setting "Cost Threshold For Parallelism" for an OLTP workload instead of default value 5? Regards, Sam SQL Server 2016 - Development and T-SQL [Workaround for executing dynamic sql in function]( - I'm trying to create a view which dynamically generates text from input parameters. But executing dynamic sql or stored procedure is not allowed in functions. Is there a work around for this? Below is sample script for: 1. Create tables, 2.Create function, 3.Create view, 4. Expected output ----------------TABLES------------------------- IF OBJECT_ID('dbo.emp_info', 'U') IS NOT NULL DROP […] [SQL server change table query not working as expected]( - Hi, It looks like that a CHANGETABLE query for a particular table is not working correctly. I created a record in the bill_details table and then updated it. The record was created with sys_change_version=5000. The records was updated with sys_change_version=5001. But the following CHANGETABLE queries are confusing me and apparently returning incorrect sys_change_creation_version: select * […] Administration - SQL Server 2014 [Trying to determin how much space I have a left on my server]( - Hi, The issue, I inherited, is that I have a 2012 SQL Server installation siting on a Win 10 machine; not sure if that matters. The C drive 93.6 GB free, and I can find how much space is allocated on each of my databases. I know there is a lot to look at such […] SQL 2012 - General [tempdb shrinking]( - Hi, Need some quick help here. I am unable to shrink tempdb files. There is lot of available free space. DBCC loginfo output We have an alert and incident opened for this and we have to close it. Other thing is that systems/storage team is not going to give us more space.  There are […] [Convert column values into single row comma separator]( - Team My requirement is to convert column values into single row comma separator along with single quotes. Pls help ServerName > Column Name Values in a row like below Server1 server2 server3 server44 I need a result like 'Server1,server2,server3,server44' SQL Server 2019 - Development [Query to fetch quarter & year from date]( - Hi, I am having the following query to fetch day & year of a particular date. Need to fetch Quarter & Year also. I tried in a similar fashion. But its not working. Please help on this. DECLARE @minDate_Str NVARCHAR(30) = '01/01/2020' , @maxDate_Str NVARCHAR(30) = '31/12/2020' DECLARE @minDate DATETIME, @maxDate DATETIME SET @minDate = […] [Running VBS Script through SQL Agent Job]( - Hello All, Glad to have found this board. I have a VB Script that is exporting a query to an excel file. It works perfectly when I run it from the command line. When I create a SQL Server Agent Job to execute the same script, it fails. The type is 'Operating System (CmdExec)' and […] T-SQL (SS2K8) [How to extract the right country info from the field?]( - This is very painful to me, I have a list of location and need to determine as much/close as possible(no need to be 100% accurate) for its country info, sample as below and two files attached here: one has 100 records, the other is the full list. San Diego, CA, United States Chicago, IL, United […] Reporting Services [SSRS report rows not summing correctly]( - Hi, I have produced a report in report builder. During the month the report runs ok, however when the report crosses into a new month e.g. January to February, it seems to split on the number of referrals whereas it should be a total number. Within my SQL code it shows the split with the […] General [SELECT different rows on multiple threads]( - I have an application which will be reading from a single table, in order to speed up the processing of each row it will run multiple threads, as soon as each thread finishes it will read again from the same table. My first approach is to select the rows assigned to an index of each […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -