Scheduling SQL Scripts Using Jenkins (2020-12-22)

 SQLServerCentral Newsletter for December 22, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Scheduling SQL Scripts Using Jenkins]( - [How To Install SSAS 2019]( - [Join the Redgate Tool Tips Swap]( - [From the SQL Server Central Blogs - Getting Started Reading Execution Plans: Highest Cost Operator]( - [From the SQL Server Central Blogs - Find columns with NULL values across the table]( Question of the Day - [More CTE Issues]( The Voice of the DBA  Daily Coping Tip Share a happy memory or inspiring thought with a loved one 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. Datetime3 I was reading about how [2020 ruined time for an author at The Verge](. I agree. I find that March feels like an eternity ago, but so much of the hours and days between then and now have blended into a blur from that I can't easily discern individual elements. As I cleaned up my [speaking CV]( for 2020, I find myself either unable to differentiate some events from others or feeling that others were years ago. Time perception is a funny thing, and as the article notes, we can perceive it in different ways, especially under stress or difficulties. I've seen some funny posts, like Pandemic Standard Time, the 211th of March, etc. In fact, this is day 286 of March for me. Mar 11 was the day that most things in my life shut down, though I could see that coming. It's gotten to be so crazy that I want to define a new data type, datetime3, which starts on that date and when I perform a datediff(day, 0, getdate()), I just get the number of days since my personal pandemic started. Maybe this could be a somber Easter Egg in SQL Server, a setting, like two digit year cutoff, that we each can set to remind us of the date when the world changed for us, and a new one began. I do think that with medical technology leaping forward that we can find some semblance of our old world, where we get together and share knowledge, smiles, and laughs. However, I also think all of us, and our organizations, will be somewhat changed by the events of 2020. I hope that begins in 2021. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Scheduling SQL Scripts Using Jenkins]( VishnuGupthanSQLPowershellDBA from SQLServerCentral Learn how a Jenkins server can be used to schedule SQL scripts to execute against SQL Server. [How To Install SSAS 2019]( Additional Articles from MSSQLTips.com In this article we walk through the steps to install SQL Server Analysis Services for SQL Server 2019. [Join the Redgate Tool Tips Swap]( Additional Articles from Redgate Learn and share tool tips with your peers. Redgate has launched their Tool Tips Swap to help everyone make the most of their Redgate products. To get involved, simply share your top Redgate tool tip for boosting productivity. All tips will be collated into a blog post and shared with the community. Plus, you’ll get the chance to win a 3-month subscription to Pluralsight. From the SQL Server Central Blogs - [Getting Started Reading Execution Plans: Highest Cost Operator]( Grant Fritchey from The Scary DBA Reading execution plans in SQL Server is just hard. There’s a lot to learn and understand. I previously outlined the basics I use to get started when I’m looking... From the SQL Server Central Blogs - [Find columns with NULL values across the table]( Brahmanand Shukla from SQL Server Carpenter Recently, I was working on one of the performance tuning assignment with an esteemed client. The size of their databases were growing tremendously. Growth in the database size is...   Question of the Day Today's question (by Steve Jones - SSC Editor):  More CTE Issues There is a problem in this CTE code. What is it? WITH cteData (lowerbound, upperbound, letter, pwd) AS ( SELECT SUBSTRING(datavalue, 1, CHARINDEX('-', datavalue) - 1) AS lowerbound , SUBSTRING(datavalue, CHARINDEX('-', datavalue) + 1, CHARINDEX(' ', datavalue) - CHARINDEX('-', datavalue) - 1) AS upperbound , SUBSTRING(datavalue, CHARINDEX(' ', datavalue) + 1, 1) , SUBSTRING(datavalue, CHARINDEX(':', datavalue) + 2, 50) FROM dbo.Day2 AS d ORDER BY d.letter) , cteSolution AS ( SELECT LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) AS occ , CASE WHEN LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) >= d.lowerbound AND LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) <= d.upperbound THEN 1 ELSE 0 END AS valid FROM cteData AS d) SELECT COUNT(*) FROM cteSolution WHERE valid = 1; GO 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) HA in Business Critical Managed Instance How is high availability handled in the Business Critical tier of Azure SQL Managed Instance in Nov 2020? Answer: Always On Availability Groups and Azure Service Fabric Explanation: The Business Critical tier uses Availability Groups and Azure Service Fabric. Ref: - Managed Instance Overview - - Premium and Business Critical service tier locally redundant availability - [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 [Always on AG group]( - One of our DR server went down over the weekend with blue screen and cause the synchronization to fail. The DB monitoring system crashed cause no notification sent. I found the DR server issue after 2 days. I agree lot of things need to be straightened out, why windows team did not notify us and […] SQL Server 2017 - Development [Alter authorization on view]( - Hi I have created a view in viewschema against table1 in tableschema I would like to change the owner of the view to to the tableschema. ALTER AUTHORIZATION ON viewschema.viewName TO tableschema When I run the above, I get Cannot find the principal tableschema, because it does not exist or you do not have permission. […] SQL Server 2016 - Development and T-SQL [How to Find Duplciate Data stored in multiple tables]( - There is probably a name/term for this problem and I'm just unaware of what it is. I know how to find duplicate data in a singular table but not when the data your comparing is stored in multiple tables, 2 tables with a parent child relationship. I Restored a copy of the AdventureWorks DB but […] [Trouble dynamically rolling up some accounts]( - Hello, Hopefully someone can help we with the following issue that i have. I have provided some test data in the format of two basic tables that closely replicates the issue. Sample data: CREATE TABLE xdbExample ( [Period] INT ,[Site] VARCHAR(6) ,[Account] VARCHAR(4) ,[Value] INT ) INSERT INTO xdbExample ([Period], [Site], [Account], [Value]) VALUES ('1901','SITE01','ACC1','1') […] [Combine Date & Time parts for Two Dates, then subtract]( - Hi,  I'm trying to combine a Date and a Time field into a DateTime, and then again for two more Date and Time fields, and then subtract the two combined DateTime fields within one select field of the SELECT statement... I can combine through CAST a Date field with a Time field, and I can […] [SSIS Config Files vs Config Tables]( - I have recently run into multiple solutions where they are using Configuration Tables to store Config parameters instead of Configuration Files. I'm a little leery to continue using the table approach. We don't have SSISDB, so that's not an option. What are your thoughts on using the table vs file approach? I've always used the […] SQL Server 2019 - Administration [Connection to SSIS after upgrade]( - I've just upgraded from SQL server 2014 to 2019 and also installed the latest version of SSMS. My server has always been on a local machine (i.e. server name was (local) as far as I can remember, or if not it was just the name of the machine in Windows). I don't have external users, […] [Secure Sockets Layer (SSL) encryption. Error: \"SQL Server did not return a respo]( - Bonjour , J'ai un problème de connexion sur le serveur SQL Joindre le message d'erreur Le pilote n'a pas pu établir une connexion sécurisée à SQL Server à l'aide du chiffrement SSL (Secure Sockets Layer). Erreur: «SQL Server n'a pas renvoyé de réponse. La connexion a été fermée.». La connexion SSMS fonctionne correctement si ce […] SQL Server 2019 - Development [SSIS Script Task for PowerShell]( - HI everyone! How do i update the script task to run on a specific server. This would change for each run. Or is there another way to run the powershell? SSIS runs the below: ssis exists on server 1 Copies powershell to server 2 Run powershell on server 2. (Assistance needed) [Access to SQL expression]( - Hi folks, I'm trying and failing miserably to convert an Access Expression to SQL query The MS Access Expression strips the following field data formats to extract the middle text between the 2 hyphens 2C00P02-CRIW-93201 (Extracted value: CRIW) Any help appreciated Left(Mid([LINE_NUM],IIf(Len(Left([LINE_NUM],InStr([LINE_NUM],"-")))=0,1,Len(Left([LINE_NUM],InStr([LINE_NUM],"-")))+1)),InStr(Mid([LINE_NUM],IIf(Len(Left([LINE_NUM],InStr([LINE_NUM],"-")))=0,1,Len(Left([LINE_NUM],InStr([LINE_NUM],"-")))+1)),"-")-1) [TSQL Script: Min, Max function]( - Hello All, I hope you are doing well. I have difficulties to write a sql script. May be you could help me. I have a table like this. And I want to have a result like this:   I used the min and max function but it doesn't work. Do you have any idea […] [CLR in SQL 2019 : Permissions]( - I'm using an CLR which I've used plenty of times in the past and have running fine on SQL 2017. I've implemented on a database in SQL 2019 and functions runs fine, returning the desired results. When I try to run the same code via a sql agent job, I get ... .NET Framework execution […] Reporting Services [SSRS and subscriptions with office365]( - Hi.I have a detail with the subscriptions in Reporting Services (SQL Server 2014).I have configured DBmail using Office365. When I run the report to send it by mail it sends: Failure sending mail: Error in processing. The server response was: 5.7.3 STARTTLS is required to send mail [BN6PR03CA0006.namprd03.prod.outlook.com]Mail will not be resent. I opened the […] Integration Services [PowerShell Process Task fails in locked down Windows 10]( - Hi Guys A weird one here. I run a process task to run powershell.exe with the argument -File C:\Ace\ResetSpreadsheet.ps1 This script runs a macro in a spreadsheet in the same folder to clear existing data and then saves and exits. Because the IT people lock our boxes down to the nth degree I had issues […] MySQL [TERADATA - Execute procedure permissions]( - im finding it hard to get a reply on other forums because of low activity in general, so apologies if this is in the wrong place Im a working in Teradata and i create a procedure and all is well then he following day, having logged off and back on, i dont have execute permissions […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -