Create an HTML Report on the Status of SQL Server Agent Jobs (2023-05-03)

SQLServerCentral Newsletter for May 3, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Create an HTML Report on the Status of SQL Server Agent Jobs]( - [PostgreSQL Basics: Object Ownership and Default Privileges]( - [PASS Data Community Summit registration is open!]( - [From the SQL Server Central Blogs - Adding microseconds to a timestamp in Azure Data Factory]( - [From the SQL Server Central Blogs - Find out how fast is your SQL database growing using the backup metadata from msdb]( - [SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance]( Question of the Day - [Readable SQLCMD Results]( The Voice of the DBA  The Public Perception of Security Issues It's my fault. That's what I think if there is a security incident with my employer that involves the database. It's almost my first thought when I hear about issues at other organizations, but since I've been a developer and administrator, and I know how complex systems are, I usually stop myself and try to learn more before I assign blame. The public and your customers also think that it's just your fault. At least, that's what I see and hear from friends. Non-technical people are very quick to assign blame and get upset. They can't understand why some companies get breached and others don't. To them, it's because the staff or management are lazy and haven't done a good job keeping their systems secure. However, even my technical friends get upset. I've had more than a few of them chastise an organization for getting breached when they themselves haven't always kept up to date on patches. I mean, how many of you are sure every SQL Server you have is at the latest CU level? How quickly do you patch? Are you sure your firewall people haven't accidentally misconfigured a rule for port 1433? Anyone can get breached, as noted in [this article](. However, a good response can set you apart, and I wish that more management and technical people would be prepared now for a data loss incident, a ransomware attack, or really any security incident. It's easy to panic and make rash decisions. The best time to draft your response is now, when you have a clear head and no pressure. Have a few people start to game out how to react, what words and message to send, and who will take responsibility for communicating with customers. It's worth a little exercise to discuss some possible responses to events and at least have the outline of a plan. And no matter what, be sure you have a copy of the plan air-gapped from your network. On a few flash drives, saved to a separate OneDrive/Google Drive/Dropbox account, or even printed out. The last think you need is for all of your work to be inaccessible because of something like ransomware encryption. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Create an HTML Report on the Status of SQL Server Agent Jobs]( Nisarg Upadhyay from SQLServerCentral SQL Server jobs are an integral part of any SQL Server environment. These jobs run a predefined set of tasks and commands at specified intervals, ensuring the database is up-to-date and performing optimally. As a database administrator, it is essential to regularly monitor the status of SQL Server jobs. One way to do this is […] [External Article]( [PostgreSQL Basics: Object Ownership and Default Privileges]( Additional Articles from SimpleTalk Understanding how roles and privileges work in Postgres is key to understanding the next, and often more confusing part of security, object ownership. [External Article]( [PASS Data Community Summit registration is open!]( Additional Articles from Redgate In 2023, connect, share & learn with like-minded peers, speakers, and industry leaders during the full week of data celebrations. Summit happens in person, from November 14th to 17th in Seattle. Check out the blog post and learn more. From the SQL Server Central Blogs - [Adding microseconds to a timestamp in Azure Data Factory]( Rayis Imayev from Data Adventures (2023-Apr-25) It’s not hard to see that many different teams were involved in building Azure Data Factory (ADF) product at Microsoft. It doesn’t have conformed constraints for naming the objects... [Blog Post]( From the SQL Server Central Blogs - [Find out how fast is your SQL database growing using the backup metadata from msdb]( SQLPals from Mission: SQL Homeostasis Sometimes I need to know how fast a database is growing, or which particular database is growing the most out of all the databases on a SQL instance.  Now... [SQL Server 2022 Query Performance Tuning]( [SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance]( Additional Articles from SQLServerCentral Troubleshoot slow-performing queries and make them run faster. Database administrators and SQL developers are constantly under pressure to provide more speed. This new edition has been redesigned and rewritten from scratch based on the last 15 years of learning, knowledge, and experience accumulated by the author. The book Includes expanded information on using extended events, automatic execution plan correction, and other advanced features now available in SQL Server.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Readable SQLCMD Results I have the new go-sqlcmd version of the sqlcmd utility. I have this query: SELECT @@VERSION, @@SERVERNAME, SUSER_SNAME (), USER_NAME (); How can I get nicely formatted results like this? [Vertically formatted results] 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) Convert Dates for XML I have a date value stored and I want to convert this with SELECT CONVERT() to a format that works well with XML. What should I use? Answer: CONVERT(VARCHAR(20), GETDATE(), 126), format is yyyy-mm-ddThh:mi:ss.mmm Explanation: The format of yyyy-mm-ddThh:mi:ss.mmm uses no spaces and is designed for XML. Ref: CAST and CONVERT (footnote 4) - [ [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 2016 - Development and T-SQL [Alternate SQL]( - In my sql result, I want to create a column called RCount. Condition: For each RecNo, AccNo * If the field Reason is "Accepted", it doesn't matter what other records contain for a specific RecNo, AccNo. It should return 1 for the very first instance of Accepted and ignore the rest. * If more than […] [How do I script the creation of a job to use environments?]( - I have been googling and googling and I have yet to find a way to check the environment box when configuring an SQL Job using an SSIS package. Specifically, when you configure a step of a job, there is a checkbox to use an Environment as shown in the image attached  I have been […] SQL Server 2019 - Administration [Updating a sql 2019 database from a sql 2000 database]( - Good morning I have a local sql server 2000 database with an old application, and I would like to make a copy of this database on sql server 2019 which is online with an ip address on a hosting, and will be updated automatically and instantly by this old database . Please suggest me a […] [How to Track Queries that cross databases or cross server]( - Situation: Main instance: maindb and backupdb archive instance: 192.168.1.150.[archivedb] there are many sps that query backupdb or query archivedb how can i track these queries. PS:can not modify these sps [AlwaysOn Issue]( - We've had an AlwaysOn cluster in Azure that is deploying using a Domain Independent cluster. We did our normal failover process to update to CU20. Unfortunately 2 of our 4 data bases ended up in a state of "Not Synchronizing" on the secondary. There were no errors. SQL Logs on the secondary show the DB […] [Permissions to view SSIS SQL Agent job step]( - I have a requirement for specific users to view SQL Agent jobs through SSMS. They've been granted the SQLAgentOperatorRole and that works up to a point. However for jobs owned by another login, if the job step is a "SQL Server Integration Services Package" type, when clicking on the "Configuration" tab, the "Connection Managers" and […] [sql server replication Mointoring alert]( - Hi How to monitor sql server replication i mean there is replication monitor but i wanted to check lag between publisher and subscriber. I mean in log shipping we know how many log files are pending to be applied on dr. In always on its redo and undo logs which can helps to estimate how […] [SQL SERVER 2019 ... ERROR TO CONNECT WITH A NEW LAPTOP]( - If I try to connect with a new laptop I receive this error ... (ARCA EVOLUTION SOFTWARE) This error compare also if I try to connect to database with MsExcel please help me to solve ... thks and rgds SQL Server 2019 - Development [COUNT DISTINCT OVER PARTITION BY]( -  Hello everyone, I have such a situation. I have Bill of Lading that has multiple Pallet Tags. I need to count all distinct Pallet Tags per Bill of Lading. Right now I am getting a number for example '771' which is wrong. It counts all the rows that exist for the Bill of Lading […] [Exclude Particular Table While Trigger Executes]( - Hi, I have a trigger which main purpose is to trigger alert whenever there is changes at DB Object and schema e.g Create/AlTER/Drop procedure,table, Views etc. The trigger works fine and trigger alert as expected. However I have one scenario where I want trigger to exclude one or two database tables even there is schema […] Azure Data Factory [Question: Adding set variable activity output to copy data]( - In azure data factory I created a lookup for getting the id, place. After that in a foreach I used a webactivity to run the an api to get the formatted_address. I extracted the formated_address through a set variable activity. I want to make a new sql table with the id and the formatted_address. I […] Connecting [unable to connect to SQL2005 Cluster via instance name with Microsoft Intune pc]( - We are currently testing a pc deployed and managed using Microsoft Intune. We notice we cannot connect to a clustered SQL Server 2005 instance using the instance name. IMO the problem is that with SQL 2005 the SQLBrowser service always replies using the ip address of the node that hosts the instance. When using the […] Editorials [Article review timeline]( - Hi, I have been frequenting this site for a while and I would like to contribute an article regarding a recent project of securing RDS that I helped with. I have submitted an article for review couple of weeks back and I am curious on the timeline when I can get some feedback on it. […] SQL Saturday and User Group Organizers [Ohio North SQL Saturday - Akron Ohio - 20 May 2023]( - Just putting this out there because it's also the first live event I'll be attending since February of 2020. The "Ohio North SQL Saturday 2023" will be in Akron, Ohio, USA for a full day of training on (as you may have guessed) Saturday, the 20th of May. Here's the link. Registration is free but […] T-SQL [Lookup table without modifying database]( - I have a production database that I will be running queries against. I am not allowed to modify that database. Then I have a lookup table in a local database on my computer with c:a 1000 rows. I want to use the lookup table when querying the prod database. I do not expect this to […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -