Collections: Crossing intervals computed gaps (2020-03-23) (2020-03-23)

 SQLServerCentral Newsletter for March 23, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Collections: Crossing intervals computed gaps]( - [What’s New in SQL Monitor 10?]( - [SQL ConstantCare® Population Report: Winter 2020]( - [From the SQL Server Central Blogs - SQLpassion in the time of Covid-19]( - [From the SQL Server Central Blogs - SSIS Package Execution Failure – When google fails you :(]( Question of the Day - [Partitioning Limits in SQL Server 2019]( Featured Script - [Select Group Members from Logins]( The Voice of the DBA  Daily Coping Tip 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. - Keep Calm - Stay Wise - Be Kind Having No Routine I'm sure there are many of you out there struggling with changes across the last few weeks as the world slowly closes down from the normal patterns of life that we've known. More people are telecommuting and learning to collaborate in new ways. The disruption is far ranging and pervasive, invading not only work, but the rest of our lives. I'm struggling with the changes. I'll admit that, and I hope those of you that are struggling can do so as well. It does help, at least slightly. It's the first step (I think) to getting control of life and moving forward. I've worked at home for nearly two decades. I've worked at a job that is often isolated from others without any need to interact. However, this has been harder than previous changes. My wife and kids are here, struggling with their own routines being upended, and while they're old enough not to disturb me when I'm working, the changes are bothering me. I think some of this is the inability to get to the gym, either for myself or the kids I coach. Trying to take care of myself in a healthy way is a big part of my life. I'm struggling as a result of this. While I do get on a stationary bike 3-4 days a week now and try to do some yoga on my own (or with YouTube), I'm struggling to get motivated. For no real reason, but it's just hard with all of the news in the world. Turning off Twitter and avoiding news is something I'm trying to do, but I also want to be informed. I want to touch base with friends, and invariably some news comes up. It's also a part of my job to try and engage in the world, which means some of that stuff keeps leaking through. I'd like to think this is a short term crisis, but I really have no idea. As I look back week to week, I barely recognize the world. Things happen every few days that I never would have imagined outside of a Hollywood movie. It's shocking, saddening, and depressing. I haven't every struggled with depression, but I think I am a bit now, especially as I empathize with many others. The stories of struggles resonate with me and the dismissals are maddening. These are difficult times for many of us. I'm going to try a few things and see how I can cope. I'm adding daily things to the top of the editorial that I'm trying, and I hope you will as well. I'm going to see if I can shake myself out of a few things, but I'm also admitting it's hard. If I can help you I will, and I am reaching out to a few friends for help myself. I'll see if I can add in some virtual contact with others, as [Mike Walsh is doing](. It's easy to say, hard to actually take care of yourself mentally, but I urge everyone to make an effort here. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Collections: Crossing intervals computed gaps]( Bogdan SAHLEAN from SQLServerCentral Problem Combining overlapping intervals in order t... [What’s New in SQL Monitor 10?]( Additional Articles from Redgate New release: SQL Monitor 10 SQL Monitor 10 has landed! You can now integrate SQL Monitor alerts with your ticket management system, so chosen alerts are automatically raised as tickets. New suppression options give you granular control over what alerts are raised during specific times, such as maintenance windows. And, you can now annotate the server activity graph with specific events, so you can measure their impact on your servers. Discover the new features [SQL ConstantCare® Population Report: Winter 2020]( Additional Articles from Brent Ozar Unlimited Blog Ever wonder what’s “normal” out there for SQL Server adoption rates, hardware sizes, or numbers of databases? Let’s find out. From the SQL Server Central Blogs - [SQLpassion in the time of Covid-19]( Klaus Aschenbrenner from Klaus Aschenbrenner (Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance... From the SQL Server Central Blogs - [SSIS Package Execution Failure – When google fails you :(]( jphillips 46546 from Another SQL Geek We have all done it, get some sort of cryptic error and the first place we go to search is Google hoping the sea of message boards and blog...   Question of the Day Today's question (by MilesC):  Partitioning Limits in SQL Server 2019 What is the partitioning limit for tables and indexes in SQL Server 2019? 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) Using SQLCMD Variables I am writing a SQLCMD script in SSMS with SQLCMD mode. I set a variable with this script (ignore the double backslashes for now): :setvar DirectoryRootPath "Log.txt" Now I want to use this variable in another part of the script. What would I put in the place of XXX in this code to use the variable value here: SELECT 'XXX', myid, myname, mychar FROM dbo.MyTable; Answer: '$(DirectoryRootPath)' Explanation: The proper way to use this path in the SELECT is to use quotes and then surround the variable with $(). Ref: Edit SQLCMD Scripts with Query Editor - [Discuss this question and answer on the forums](  Featured Script [Select Group Members from Logins]( slesicki from SQLServerCentral Know who all the individual group members are for a given WINDOWS_GROUP login. This script uses the sys.xp_logininfo procedure to find group members for each login on an instance. This is useful when attempting to remove orphaned WINDOWS_LOGINS. May have issues working across your logged in domain. /*-------------------------------------------------------------------------------------------------Name: LoginWindowsGroupMemberSelect.sqlPurpose: To find all user accounts for a Windows Group in SQL Server logins.Author: Patrick SlesickiNotes:Returns all user logins for a Windows GroupSimply execute on an instance to get results.Tested on SQL Server versions 2012 through 2017. This should work on 2008 and 2008R2 but I've not tested it there.Adapted from a presentation by Laura Grob.Historyyyyy-mm-dd Init Description2018-02-14 PLS Created---------------------------------------------------------------------------------------------------Preliminaries-------------------------------------------------------------------------------------------------*/SET NOCOUNT ON;SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;/*-------------------------------------------------------------------------------------------------Declarations-------------------------------------------------------------------------------------------------*/DECLARE @WindowsGroupName AS nvarchar(128);DECLARE @GroupLoginTable AS table (AccountName nvarchar(128) NULL);DECLARE @GroupLoginMemberTable AS table(AccountName nvarchar(128) NULL,Type char(8) NULL,Privilege char(9) NULL,MappedLoginName nvarchar(128) NULL,PermissionPath nvarchar(128) NULL);/*-------------------------------------------------------------------------------------------------Find windows groups-------------------------------------------------------------------------------------------------*/INSERT INTO @GroupLoginTable (AccountName)SELECT nameFROM sys.server_principalsWHERE type_desc = N'WINDOWS_GROUP';/*-------------------------------------------------------------------------------------------------Cycle through groups to find members-------------------------------------------------------------------------------------------------*/WHILE EXISTS (SELECT * FROM @GroupLoginTable)BEGINSET @WindowsGroupName = (SELECT TOP (1) AccountName FROM @GroupLoginTable);INSERT INTO @GroupLoginMemberTable(AccountName,Type,Privilege,MappedLoginName,PermissionPath)EXEC sys.xp_logininfo@acctname = @WindowsGroupName,@option = 'members';DELETE FROM @GroupLoginTableWHERE AccountName = @WindowsGroupName;END;/*-------------------------------------------------------------------------------------------------Output results-------------------------------------------------------------------------------------------------*/SELECTWindowsGroup = PermissionPath,AccountName,PrivilegeFROM @GroupLoginMemberTableORDER BYWindowsGroup,AccountName;/*-------------------------------------------------------------------------------------------------END-------------------------------------------------------------------------------------------------*/ [More »](  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 [Programmatically create SSRS report as PDF]( - I would really appreciate some guidance on how to get vb code working in a SSRS report. Please refer vb code at end of posting. The code has been added to Custom Code (Report Properties, Code) and the code is called from a Text Box expression in the report. I have created a SSRS Data […] [How to check if a particular table is being used right now?]( - I am completing an SP that will do massive deletes based on certain criteria from a dozen of tables. On average each table is tens of millions of rows, no permanent or tuned indexes on it (it is a staging-swap kind of tables, temporary transactional data holders) but occasionally heavy selects can be run against […] [Running Total]( - I have a table that captures rainfall data approx every 10 minutes. I want to calculate a sort of a running total of the Last Hour's Rainfall and the Last 8 Hours Rainfall by Time. Last Hour's Rainfall = Sum(PrecipitationAmount) for the previous 60 minutes (PrecipitationMinutes is the number of minutes over which the PrecipitationAmount […] SQL Server 2016 - Development and T-SQL [Need some help getting info of people logged in, but from the POV of NON-Sysadm]( - I modified a proc that returns a result set of all user emails to the application in question, with one column that displays a sundial if that user is logged in. I did this by doing a left outer join to sys.dm_exec_sessions Left join sys.dm_exec_sessions b on login_name = Su.name and program_name like ' [I was asked to get rid of this Dynamic SQL for performance purposes.]( - Is it REALLY possible to bypass the @Where_Clause variable and make the below SQL non-dynamic? This is a statement from an SP that receives the below 5 parameters and dynamically builds the WHERE clause from them. I don't think it is possible NOT TO USE DYNAMIC SQL HERE, but double-checking with you guys. Thanks. -- […] [Need explanation for below query what it does]( -  @Date1 = '20150630' DATEADD(m,6, @Date1) < DATEADD(m,-12,GETDATE())  Ignore this, i got the answer after validating. Administration - SQL Server 2014 [SQl Stored Procedure - View Tuning]( - Hi, We have a stored procedure which calling view which contains bunch of tables in sql 2014 and 2017 and it's slow. View contains roughly 100K records. I was looking view and based on joins and where clause i have created index but nothing gain as performance it's same. I tried following: Created Index based […] SQL Server 2012 - T-SQL [Concatenating to a string]( - I am trying to concatenate values into one string value and what i have so far is progress, but I want to encapsulate my variables with single quotes. The below gives me e.g. Karen,Cox,Oakland,MN,95573 when what I need is 'Karen','Cox','Oakland','MN','95573': p.firstname + ',' + p.lastname + ',' + i.city + ',' + t.stateAbbrveation + ',' […] SQL Server 2019 - Administration [Gauging Performance Improvement after removing the unused indexes]( - Hi All, Removed some of the unused indexes. But, how to identify the performance improvement? Is there anyway to generate any metrics and giving that the managers?  Regards SQL Server 2019 - Development [PowerBI Report Builder - can it use/declare variables?]( - In SSRS, creating your own variables for use within your report is trivial... did MSFT leave this out of PowerBI Report Builder? In SSRS, I could create a dataset, and create a variable, and set the value using an aggregate for example (like a count of records in a dataset). I'm trying to do the […] [Ensuring a randomly generated number is always 8 digits]( - Hi there  I have inherited a piece of code which generates an Activation Code based on a seeded value as follows select cast (round(rand(31303258 * rand(DATEPART(millisecond,GETDATE()))) * 100000000,0) as int) where 31303258 is the seed or another 8 digit number  Now what I have noticed is that not all the time, a 8 […] Reporting Services [Programmatically create SSRS report as PDF]( - I would appreciate some guidance on how to get vb code working in a SSRS report. Please refer vb code at end of posting. The code has been added to Custom Code (Report Properties, Code) and the code is called from a Text Box expression in the report. I have created a SSRS Data Source […] SSRS 2016 [SSRS 2016 - force page break]( - Hello, How it is possible to dynamically force a page break? for example: if the final page is on pagenumber say number 9 then have a page break before this page? Thank you Integration Services [View package that has been deployed]( - First an explanation just to see if I am going about this the right way  I have a package that i've created using VS 2017 If run it from VS2107 i.e. locally, it works fine  I've created a SQL Agent job Now, if I deploy it via SSMS i.e. through deploy project in […] [SSIS & Azure SQL database]( - Hi , I have an on-premise SSIS package which connects to SQL Server database(Source). On connecting source using onpremise SSIS need to connect Azure SQL database , after connecting Azure SQL database need to truncate the stage table first and insert the source data. On success of source data to destination table (stage table), need […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -