SQLServerCentral.com Community Update for March 23, 2020

Problems displaying this newsletter? View online.

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 - https://docs.microsoft.com/en-us/sql/ssms/scripting/edit-sqlcmd-scripts-with-query-editor?view=sql-server-ver15

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-------------------------------------------------------------------------------------------------*/

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 […]

This email has been sent to newsletter@newslettercollector.com. 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.