Implementing Date Calculations in SQL (2020-03-05)

 SQLServerCentral Newsletter for March 5, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Implementing Date Calculations in SQL]( - [SQL Server on VMWare Performance Optimization]( - [Build your DevOps business case with industry insights]( - [From the SQL Server Central Blogs - SQL Homework – March 2020 – Database Configurations.]( - [From the SQL Server Central Blogs - Why can’t I remove this extra transaction log file?]( Question of the Day - [The gMSA]( Featured Script - [String Search]( [Redgate SQL Prompt]( The Voice of the DBA  Is 100% Security Possible? Microsoft has spent a lot of resources working to ensure their software can be automated, audited, and configured securely. After the SQL Slammer worm, there was an effort made by the organization to code more securely. Secure by design and by default was the goal, and they've continued in the years since to try and ensure we can better secure our systems. There's even a [Zero Trust methodology]( that they push these days. Microsoft is notoriously strict with security, disallowing networking to potential threats, and [they encourage you to do this as well](. They have [SAW](s and [PAW](s and [auditing recommendations]( and [advanced threat protection](. They have an [incredible security operations center](, which I'd like to think they use for their internal resources. And yet, we have [Microsoft support mis-configuring a database and exposing customer PII](. How does this happen when security is a big part of the Microsoft business, and something that many of us rely on them doing well? This was human error, [an issue with network security rules](, and likely due to the complexity of these rules, as managing these and understanding the end result of a large set is difficult for a human. Still, it's disappointing and daunting. As I talk with people about DevOps and ensuring we automate our best practices, I find many people embracing the idea of using software to ensure our systems are set up consistently and securely, in the same way in all environments. I find plenty of people that prefer having the computer make the changes for them and removing the chance they make a mistake. However, if all of the staff doesn't buy into the new process, it opens up potential places for problems. One human and cause problems by circumventing the system. In this case, Microsoft admits they have solutions to prevent and detect this, but they were not enabled. Likely, a human mistake, but this isn't clear. Perhaps the most disappointing thing about this data breech is the lack of details. I'd like to know what rules failed, and maybe more importantly, what systems are in place to protect against this and how are they configured. This is a great opportunity for Microsoft to share some knowledge and educate their customers, but they didn't take it. Specifics on the failure would help many of us ensure we don't fall victim to the same issue. Security is hard. It's a constant, ongoing battle to ensure we are following best practices, as well as learning and updating our knowledge all the time. If Microsoft can't do it, can we hope to? Maybe more importantly, if Microsoft doesn't help us understand how do secure systems well, with their experts and specialists, can we hope to do better? Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums]( [Redgate SQL Monitor](   Featured Contents [Implementing Date Calculations in SQL]( aveek22 from SQLServerCentral This article explains a way to use labels (last year, YTD, etc.) for report parameters. [SQL Server on VMWare Performance Optimization]( Additional Articles from MSSQLTips.com While VMWare hosts mission critical SQL Server applications around the world, there are several configuration and design choices that can bring your databases to a grinding halt. Learn how to optimally configure & optimize SQL Servers running on VMWare [Build your DevOps business case with industry insights]( Press Release from Redgate With over 2000 participants from every industry sector across the globe, the State of Database DevOps report will help shape the business case of any team looking to implement or improve their approach to database DevOps, no matter the platform they choose to work with. Including a foreword from Kellyn Pot’Vin-Gorman, Customer Success Engineer at Microsoft, discover the landscape of database DevOps for 2020. From the SQL Server Central Blogs - [SQL Homework – March 2020 – Database Configurations.]( Kenneth.Fisher from SQLStudies A little while back we tried configuring the instance. Sometimes though, instance settings are a bit heavy handed, so this ... Continue reading From the SQL Server Central Blogs - [Why can’t I remove this extra transaction log file?]( DesertDBA from The Desert DBA Before this post gets started, let’s get past the title. I know what many of you are thinking: what in the name of Paul Randal would make someone have...   Question of the Day Today's question (by Steve Jones - SSC Editor):  The gMSA What is a gMSA? 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) Finding the differences I have two sets in R, as shown here: > a <- c(3,65,3,5,7,8,4,3) > b <- c(3,65,5,7,3,9,7,1) I want to find those values in a that are not in b. What should I do? Answer: setdiff(a,b) Explanation: The built in setdiff() function will do this. The first argument is the set of items you want to know about, and the second is the set to scan for items. Ref: setdiff() - [Discuss this question and answer on the forums](  Featured Script [String Search]( slesicki from SQLServerCentral The script searches through every column of every record in a database to find a value. It works with numbers too. Simply assign a value to @MyString. Indicate what datatype(s) you wish to search through, and indicate if you want wildcards, and execute. The search is smart and will only search through columns where @MyString is […] /*------------------------------------------------------------------------------------------------- Name: StringSearch.sql Purpose: To search for a value in all applicable columns of a database. Date: 2017-08-24 Author: Patrick Slesicki Instructions: For SQL Server versions 2012 and later only. TRY_CONVERT function is used. Returns a SQL statement for all columns containing @MyString. Enclose the value for @MyString with tic marks ('), even if it is a number. Select the desired database, and run. It's best to choose a single data type option at a time. --------------------------------------------------------------------------------------------------- --Preliminaries -------------------------------------------------------------------------------------------------*/ SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED IF OBJECT_ID('tempdb.dbo.#Output') IS NOT NULL BEGIN DROP TABLE #Output END --------------------------------------------------------------------------------------------------- --Declarations: User input required --------------------------------------------------------------------------------------------------- DECLARE --String to search for @MyString AS nvarchar(99) = 'Phillip' --String search options ,@SearchChar AS smallint = 1 --char, varchar, nchar, nvarchar, text, ntext ,@SearchLOB AS smallint = 0 --varchar(MAX), nvarchar(MAX) ,@SearchWildCard AS smallint = 0 --Adds wild cards '%' to the front and rear of @MyString for string searches only. --Other data types ,@SearchGUID AS smallint = 0 --uniqueidentifier ,@SearchNumeric AS smallint = 0 --decimal, numeric, float, real, and money ,@SearchInteger AS smallint = 0 --bigint, int, smallint, tinyint --------------------------------------------------------------------------------------------------- DECLARE --Facts about @MyString @MyStringLength AS smallint --Length of @MyString ,@MyStringDecimalPoint AS smallint --Location of decimal point in @MyString ,@MyStringPrecision AS smallint --Precision of @MyString ,@MyStringScale AS smallint --Scale of @MyString ,@MyStringIsGUID AS smallint --Is @MyString a GUID? ,@MyStringIsNumeric AS smallint --Is @MyString a number? ,@MyStringIsBigInt AS smallint --Is @MyString a big integer? ,@MyStringIsInt AS smallint --Is @MyString an integer? ,@MyStringIsSmallInt AS smallint --Is @MyString a small integer? ,@MyStringIsTinyInt AS smallint --Is @MyString a tiny integer? --Facts for columns in cursor ,@SchemaName AS nvarchar(128) ,@TableName AS nvarchar(128) ,@ColumnName AS nvarchar(128) ,@ColumnDataType AS nvarchar(128) ,@ColumnID AS smallint ,@ColumnMaxLength AS smallint ,@ColumnPrecision AS smallint ,@ColumnScale AS smallint --Variables for dynamic sql ,@SQLCommand AS nvarchar(4000) --Outermost SQL command in dynamic string ,@SQLSubCommand AS nvarchar(1000) --Resultant command to be copied to new window for execution ,@MyStringWildCard AS nvarchar(100) --@MyString with wildcards added in front and back ,@MyStringSQLCommand AS nvarchar(100) --@MyString used in @SQLCommand ,@MyStringSQLSubCommand AS nvarchar(100) --@MyString used in @SQLSubCommand ,@Operator AS nvarchar(10) --Character types can use ' LIKE ' while other types use ' = '. Be sure to include spaces. ,@SqlDataType AS nvarchar(128) --Punctuation ,@cr AS nchar(2) = CHAR(13) + CHAR(10) --Carriage return and line feed ,@tic AS nchar(1) = CHAR(39) --Single quote, aka tic mark or apostrophe --Table variable and Temp tables DECLARE @DataTypeTable AS table(system_type_id tinyint NULL) CREATE TABLE #Output ( SchemaName nvarchar(128) NULL ,TableName nvarchar(128) NULL ,ColumnName nvarchar(128) NULL ,ColumnID int NULL ,SqlDataType nvarchar(128) NULL ,SQLCommand nvarchar(4000) NULL ) --------------------------------------------------------------------------------------------------- --Get facts about @MyString --------------------------------------------------------------------------------------------------- SELECT @MyStringLength = LEN(@MyString) ,@MyStringWildCard = CASE @SearchWildCard WHEN 1 THEN '%' + @MyString + '%' ELSE @MyString END ,@MyStringIsGUID = CASE WHEN TRY_CONVERT(uniqueidentifier, @MyString) IS NULL THEN 0 ELSE 1 END ,@MyStringIsNumeric = ISNUMERIC(@MyString) IF @MyStringIsNumeric = 1 BEGIN --find decimal point position in @MyString SET @MyStringDecimalPoint = CHARINDEX('.', @MyString) --find numeric precision and scale for @MyString SELECT @MyStringPrecision = CASE @MyStringDecimalPoint WHEN 0 THEN @MyStringLength ELSE @MyStringLength - 1 END ,@MyStringScale = CASE @MyStringDecimalPoint WHEN 0 THEN 0 ELSE @MyStringLength - @MyStringDecimalPoint END --find if @MyString is one of the following integer data types IF TRY_CONVERT(bigint, @MyString) IS NULL SET @MyStringIsBigInt = 0 ELSE SET @MyStringIsBigInt = 1 IF TRY_CONVERT(int, @MyString) IS NULL SET @MyStringIsInt = 0 ELSE SET @MyStringIsInt = 1 IF TRY_CONVERT(smallint, @MyString) IS NULL SET @MyStringIsSmallInt = 0 ELSE SET @MyStringIsSmallInt = 1 IF TRY_CONVERT(tinyint, @MyString) IS NULL SET @MyStringIsTinyInt = 0 ELSE SET @MyStringIsTinyInt = 1 END --------------------------------------------------------------------------------------------------- --Data types to search --Populate @DataTypeTable --------------------------------------------------------------------------------------------------- --character types IF @SearchChar = 1 BEGIN INSERT INTO @DataTypeTable(system_type_id) SELECT system_type_id FROM sys.types WHERE name IN('char','varchar','nchar','nvarchar','text','ntext') END --lob types --insert types if @SearchChar = 0 IF @SearchLOB = 1 BEGIN IF NOT EXISTS ( SELECT * FROM @DataTypeTable AS dtt JOIN sys.types AS ty ON dtt.system_type_id = ty.system_type_id WHERE ty.name IN('varchar','nvarchar') ) BEGIN INSERT INTO @DataTypeTable(system_type_id) SELECT system_type_id FROM sys.types WHERE name IN('varchar','nvarchar') END END --guid IF @SearchGUID = 1 AND @MyStringIsGUID = 1 BEGIN INSERT INTO @DataTypeTable(system_type_id) SELECT system_type_id FROM sys.types WHERE name = 'uniqueidentifier' END --decimal, numeric, float, real, money IF @SearchNumeric = 1 AND @MyStringIsNumeric = 1 BEGIN INSERT INTO @DataTypeTable(system_type_id) SELECT system_type_id FROM sys.types WHERE name IN('decimal','numeric','float','real','money') END --integer types IF @SearchInteger = 1 AND @MyStringIsTinyInt = 1 BEGIN INSERT INTO @DataTypeTable(system_type_id) SELECT system_type_id FROM sys.types WHERE name IN('tinyint','smallint','int','bigint') END ELSE IF @SearchInteger = 1 AND @MyStringIsSmallInt = 1 BEGIN INSERT INTO @DataTypeTable(system_type_id) SELECT system_type_id FROM sys.types WHERE name IN('smallint','int','bigint') END ELSE IF @SearchInteger = 1 AND @MyStringIsInt = 1 BEGIN INSERT INTO @DataTypeTable(system_type_id) SELECT system_type_id FROM sys.types WHERE name IN('int','bigint') END ELSE IF @SearchInteger = 1 AND @MyStringIsBigInt = 1 BEGIN INSERT INTO @DataTypeTable(system_type_id) SELECT system_type_id FROM sys.types WHERE name = 'bigint' END --------------------------------------------------------------------------------------------------- --Define cursor --Aside from datatypes, all filtering and exclusions are done in the WHERE clause of this SELECT statement. --------------------------------------------------------------------------------------------------- DECLARE TableCursor CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SELECT SCHEMA_NAME(t.schema_id) ,t.name ,c.name ,c.column_id ,ty.name ,c.max_length ,c.precision ,c.scale FROM sys.tables AS t JOIN sys.columns AS c ON t.object_id = c.object_id JOIN @DataTypeTable AS dtt ON c.system_type_id = dtt.system_type_id JOIN sys.types AS ty ON dtt.system_type_id = ty.user_type_id --Note the switch from system_type_id to user_type_id WHERE --Exclude system schemas SCHEMA_NAME(t.schema_id) NOT IN('cdc','sys') --Exclude system tables AND t.name NOT LIKE 'ms%' AND t.name NOT LIKE 'sys%' --Exclude tables with no records AND ( SELECT SUM(rows) FROM sys.partitions WHERE object_id = t.object_id AND index_id < 2 ) > 0 --Include/Exclude LOB types based on @SearchLOB selection --LOB types have a max_length of -1 AND c.max_length >= -(@SearchLOB) --Exclude columns for char types taking into account -- 1. @SearchChar and @SearchLob are distinct searches -- 2. max length of max_length is 8000 AND c.max_length NOT BETWEEN 0 AND ( CASE WHEN ty.name IN('char','varchar') AND @SearchChar = 1 AND c.max_length > 0 THEN c.max_length - 1 WHEN ty.name IN('nchar','nvarchar') AND @SearchChar = 1 AND c.max_length > 0 THEN c.max_length / 2 - 1 WHEN ty.name IN('varchar','nvarchar') AND @SearchChar = 0 AND @SearchLob = 1 THEN 8000 ELSE 0 END ) --Exclude columns where precision is smaller than @MyString's precision for decimal/numeric searches AND c.precision >= ( CASE WHEN ty.name IN('decimal','numeric','money') THEN @MyStringPrecision - @MyStringScale + c.scale ELSE 0 END ) --Exclude columns where scale is smaller than @MyString's scale for decimal/numeric searches AND c.scale >= ( CASE WHEN ty.name IN('decimal','numeric','money') THEN @MyStringScale ELSE 0 END ) --------------------------------------------------------------------------------------------------- --Open cursor and get records --------------------------------------------------------------------------------------------------- OPEN TableCursor FETCH NEXT FROM TableCursor INTO @SchemaName ,@TableName ,@ColumnName ,@ColumnID ,@ColumnDataType ,@ColumnMaxLength ,@ColumnPrecision ,@ColumnScale --------------------------------------------------------------------------------------------------- --Loop through cursor --------------------------------------------------------------------------------------------------- WHILE @@FETCH_STATUS = 0 BEGIN ------------------------------------------------------------------------------------------- --Assign values to the following @SQLCommand components -- @MyStringSQLCommand -- @MyStringSQLSubCommand -- @Operator ------------------------------------------------------------------------------------------- --character type exact match IF @ColumnDataType IN('char','varchar','nchar','nvarchar') AND @SearchWildCard = 0 BEGIN SELECT @MyStringSQLCommand = + @tic + @MyString + @tic ,@MyStringSQLSubCommand = + @tic + @MyString + @tic ,@Operator = ' = ' END --character type fuzzy match ELSE IF @ColumnDataType IN('char','varchar','nchar','nvarchar') AND @SearchWildCard = 1 BEGIN SELECT @MyStringSQLCommand = + @tic + @MyStringWildCard + @tic ,@MyStringSQLSubCommand = + @tic + @MyStringWildCard + @tic ,@Operator = ' LIKE ' END ELSE IF @ColumnDataType IN('text','ntext') BEGIN SELECT @MyStringSQLCommand = + @tic + @MyString + @tic ,@MyStringSQLSubCommand = + @tic + @MyString + @tic ,@Operator = ' LIKE ' END ELSE IF @ColumnDataType = 'uniqueidentifier' BEGIN SELECT @MyStringSQLCommand = + @tic + @MyString + @tic ,@MyStringSQLSubCommand = + @tic + @MyString + @tic ,@Operator = ' = ' END ELSE IF @ColumnDataType IN('decimal','numeric') BEGIN SELECT @MyStringSQLCommand = 'CONVERT(' + @ColumnDataType + '(' + CONVERT(nvarchar(39), @ColumnPrecision) + ',' + CONVERT(nvarchar(39), @ColumnScale) + '),' + @MyString + ')' ,@MyStringSQLSubCommand = @MyString ,@Operator = ' = ' END ELSE IF @ColumnDataType IN('float','real','money','bigint','int','smallint','tinyint') BEGIN SELECT @MyStringSQLCommand = 'CONVERT(' + @ColumnDataType + ',' + @MyString + ')' ,@MyStringSQLSubCommand = @MyString ,@Operator = ' = ' END ------------------------------------------------------------------------------------------- --sql data type ------------------------------------------------------------------------------------------- SET @SqlDataType = @ColumnDataType + CASE WHEN @ColumnMaxLength = -1 THEN '(MAX)' WHEN @ColumnMaxLength > 0 AND @ColumnDataType IN('char','varchar') THEN '(' + CONVERT(nvarchar(5), @ColumnMaxLength) + ')' WHEN @ColumnMaxLength > 0 AND @ColumnDataType IN('nchar','nvarchar') THEN '(' + CONVERT(nvarchar(5), @ColumnMaxLength / 2) + ')' WHEN @ColumnDataType IN('decimal','numeric') THEN '(' + CONVERT(nvarchar(20), @ColumnPrecision) + ', ' + CONVERT(nvarchar(20), @ColumnScale) + ')' ELSE '' END ------------------------------------------------------------------------------------------- --Sub Command ------------------------------------------------------------------------------------------- SET @SQLSubCommand = 'SELECT ' + QUOTENAME(@ColumnName) + ', * FROM ' + QUOTENAME(DB_NAME()) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@ColumnName) + @Operator + @MyStringSQLSubCommand + '' ------------------------------------------------------------------------------------------- --Main command ------------------------------------------------------------------------------------------- SET @SQLCommand = 'SET NOCOUNT ON' + @cr + 'IF EXISTS (SELECT * FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@ColumnName) + @Operator + @MyStringSQLCommand + ')' + @cr + 'BEGIN' + @cr + 'INSERT INTO #Output(SchemaName, TableName, ColumnName, ColumnID, SqlDataType, SQLCommand)' + @cr + 'SELECT ' + QUOTENAME(@SchemaName, @tic + @tic) + ', ' + QUOTENAME(@TableName, @tic + @tic) + ', ' + QUOTENAME(@ColumnName, @tic + @tic) + ', ' + CONVERT(nvarchar(15), @ColumnID) + ', ' + QUOTENAME(@SqlDataType, @tic + @tic) + ', ' + QUOTENAME(@SQLSubCommand, @tic + @tic) + @cr + 'PRINT ' + @tic + 'RECORDS FOUND IN ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@ColumnName) + @tic + @cr + 'END' + @cr + @cr ------------------------------------------------------------------------------------------- --Execute dynamic sql command ------------------------------------------------------------------------------------------- PRINT 'Checking ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@ColumnName) EXECUTE dbo.sp_executesql @stmt = @SQLCommand ------------------------------------------------------------------------------------------- --Get next record in cursor ------------------------------------------------------------------------------------------- FETCH NEXT FROM TableCursor INTO @SchemaName ,@TableName ,@ColumnName ,@ColumnID ,@ColumnDataType ,@ColumnMaxLength ,@ColumnPrecision ,@ColumnScale END PRINT 'Finished checking database ' + QUOTENAME(DB_NAME()) --------------------------------------------------------------------------------------------------- --Get results --------------------------------------------------------------------------------------------------- SELECT SchemaName ,TableName ,ColumnName ,ColumnID ,SqlDataType ,SQLCommand FROM #Output ORDER BY SchemaName ,TableName ,ColumnName --------------------------------------------------------------------------------------------------- --Clean up --------------------------------------------------------------------------------------------------- CLOSE TableCursor DEALLOCATE TableCursor DROP TABLE #Output --------------------------------------------------------------------------------------------------- --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 2016 - Administration [Disk latency Writes and the Recovery Interval]( - I have a number of clients in Azure who are experiencing Disk Write Latency of 200ms and above. The issue in Azure is IOPS are capped based on the size of the disk, so on a P30, 1TB disk you are capped at 5,000 IOPS per disk. This creates a number of issues: If you […] [SQL 2016 Merge replication issues]( - Last week, I ran into two merge replication related issues: For non-production, it's SQL 2016 to SQL 2016 merge replication, one schema change(a new column) was unable to replicate from publisher to subscriber. I had to reinitialize the subscriber with a new snapshot to get the replication article in sync. On production, it's SQL 2016 […] SQL Server 2016 - Development and T-SQL [Datatype differences when restoring a database from one server to the next]( - Hello, I'm performing a backup/restore operation from a SQL Server 2012 database server to a SQL Server 2016 database server. The database is currently set to SQL Server 2008 compatibility within both database servers using the same 'SQL_Latin1_General_CP1_CI_AS' collation from the server level to column level. Now when the database finished restoring on the 2016 […] [Roll multiple records into 1]( - I'm trying to roll multiple records into one replacing null value. IF (SELECT OBJECT_ID('tempdb..#Table50'))is not null DROP TABLE #Table50 CREATE TABLE #Table50 ( MyID int, A int, B int, C int) INSERT INTO #Table50 ( MYID, A, B, C) SELECT 1, NULL,NULL, 53 UNION ALL SELECT 1, NULL,NULL, 54 UNION ALL SELECT 1, NULL,924, NULL […] [SQL UniqueIdentifier question]( - Hello, Our reporting database truncates and loads latest data during each import and generates Unique Identifiers for Tasks and Project UIDs .  Example: ProjectTable TaskTable We have a SharePoint list where incremental updates are done using TaskUID from Task Table and ProjectUID from Project Table. If with every refresh, Project and Task tables regenerates […] [Extract string from string that contains specific first two characters]( - Hi I would like to extract from a full string the 'XX....' string. This specific string inst in the same position in my full string. - this string always starts with XX - this string always contains 6 characters  thanks in advance, [Receiving error: The xml data type cannot be selected as DISTINCT because it...]( - In the below query, the data returned in the query is in xml format. I'm receiving an issue with duplicate records being returned however, so I wanted to set it to "Select DISTINCT...", however in doing so, I'm now receiving this error, but I'm not sure how to correct it to ensure I still receive […] Administration - SQL Server 2014 [Upgrading SQL 2014 to SQL 2017 cluster]( - Hi All, Not sure whether to post it here or in SQL 2017, but here goes.... We have a Win 2012 R2 / SQL 2014 cluster for a mission critical database. It's a 3 node cluster with nodes in different subnets. As DR we logship to a 4th off site server. We want to upgrade […] SQL Server 2019 - Administration [MIGRATE SQL 2014 to 2019 (not UPGRADE)]( - Anyone had experience migrating a SQL Server to the latest version? We have 2 servers, SQL 2014 SP3-CU4 + Windows Server 2012 R2 and would want to migrate both server to SQL 2019 + Windows Server 2016/2019. Have to point out the following: The servers has a transactional replication running. The subscriber server is also […] [SQL Server installation with British English]( - What little hair I have left is being torn out in chunks trying to get to the right part of Microsoft to get a reasonable response, so I'm hoping you helpful lot here can answer my problem. When I try to install SQL Server 2019 and look at the instance properties, I see it has […] [Availability groups and sql agent]( - I have A proof of concept 2019 sql server with 2 active nodes and a read only reporting node. I'm looking at the SQL agent jobs and don't want them to fire on both nodes - i know the jobs need to be on both nodes for a failover scenario so my plan was to […] SQL Server 2019 - Development [Indexed view using remote database]( - Dear Friends, i have an requirement where i need to create a indexed view with a table which is available in different server. but i get an error as Remote access is not allowed from within a schema-bound object.  I understand this problem. but i want to create as it is. i can create the same table […] SQL Server 2008 - General [Continuously current list of attached files in SQL Server]( - This isn't strictly a SQL Server question, but there are some pretty sharp people on here, and I'm probably not the only one with a problem like this.  I have a database that has associated files in a folder/subfolder structure on the server – Windows Server Standard, 2007, SP2. The associated files are mostly […] Integration Services [SSIS Class not registered]( - Hello, I installed SSIS on a server by itself, it is 2016 version 13.0 version I followed permission directions from the link below: I tried to connect to SQL management studio version 16.3 version, to SSMS 18.4, and I get the following error message, I attached it. not sure why or what to do […] General [Trying to Sum by different levels]( - Hello everyone, If any one could please help me with this? I'm trying to add additional sum by Family and Sum by Store level Trying to get this result It seem that i only know how to use group by at the end of my statement.   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -