The Road to Database CI (2019-11-28)

 SQLServerCentral Newsletter for November 28, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [The Road to Database CI]( - [Accelerating Database Recovery with SQL Server 2019]( - [Finding Correlated Rows Using EXISTS or COUNT]( - [From the SQL Server Central Blogs - Why oh Why? Effects of Antivirus on a DB Server]( - [From the SQL Server Central Blogs - Your First Jupyter Notebook]( Question of the Day - [Happy Thanksgiving 2019]( The Voice of the DBA  Happy Thanksgiving 2019 Happy Thanksgiving to everyone in the US, and a happy Thursday to the rest of the world. Today is a day to appreciate what has gone well for you in life and be thankful for your good fortune. I try to do this regularly, but Thanksgiving is a time to share that with family for me. It's one of the relatively rare times that all my kids are home these days, so I'm especially thankful this year. For those in the US, enjoy your holiday. For everyone outside the US, I hope you have a productive, quiet, and stress free day Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums]( [Redgate SQL Source Control](   Featured Contents [The Road to Database CI]( Steve Thompson-454462 from SQLServerCentral.com One firm's journey on the road to mastering (or at least attempting) continuous database integration. [Accelerating Database Recovery with SQL Server 2019]( Additional Articles from SimpleTalk Learn how to turn on and use newer SQL Server database recovery option called “Accelerated Database Recover”. Read on.... [Finding Correlated Rows Using EXISTS or COUNT]( Additional Articles from Redgate Should you always use EXISTS rather than COUNT when checking for the existence of any correlating rows that match your criteria? Does the former really offer "superior performance and readability". Louis Davidson investigates. From the SQL Server Central Blogs - [Why oh Why? Effects of Antivirus on a DB Server]( SQLRNNR from SQL RNNR At best these phantom backups cause undue headache in troubleshooting. At worst, they make it impossible to recover in the event of a database related disaster. Join me for... From the SQL Server Central Blogs - [Your First Jupyter Notebook]( Grant Fritchey from The Scary DBA In April, I said I was going to start learning Jupyter Notebooks. It’s November. Let’s get going with your first Jupyter Notebook. A quick aside before we start. I...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Happy Thanksgiving 2019 It's Thanksgiving in the US today, and I got called into work to fix a potential issue with our site. The main Redgate site has a game designed to entertain customers by letting them put a series of actions in order to get their turkey dinner ready. There is a table with this data for the game: CREATE TABLE Thanksgiving ( StepKey INT IDENTITY(1,1) constraint ThanksgivingPK PRIMARY KEY , PreparationStep VARCHAR(100) ) GO INSERT dbo.Thanksgiving (PreparationStep) VALUES ('Buy Turkey'), ('Defrost Turkey'), ('Remove innards'), ('Preheat oven'), ('Butter outside'), ('Fill with stuffing'), ('Bake for 1 hour per pound'), ('Serve and enjoy') GO The site is supposed to randomly order these rows every time the page is loaded. This is the query that is being called: WITH pkCTE (n) AS (SELECT ROUND(RAND(StepKey),0) FROM Thanksgiving ) SELECT * FROM Thanksgiving t INNER JOIN pkCTE c ON t.Stepkey = c.n ORDER BY n However, this isn't working. The results come back as: Buy Turkey Buy Turkey Buy Turkey Buy Turkey Buy Turkey Buy Turkey Buy Turkey Buy Turkey What can I do to fix this? 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) Sorting the Data Frame I have this data frame in R: > passing.2019 Player Yards TD INT 1 Rivers 2609 12 7 2 Brady 2535 14 5 3 Wilson 2505 22 1 4 Stafford 2499 19 5 5 Rodgers 2585 17 2 I have the TDs also stored in a variable: > TD.2019 <- c(12,14,22,19,17) How do I sort the data frame by the number of TDs in a descending order? Answer: > passing.2019[order(TD.2019, decreasing = TRUE),] Explanation: The ORDER() function is built into R, but this won't work directly with the column names. It needs a vector. In this case the TD.2019 variable has that vector. Red: ORDER - [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 [Unable to Join Databases to AG]( - I have a stand alone SQL 2008 R2 SQL Server that I'm migrating to a 3 node AG. At various times during the testing of the restore process to get the databases onto the new SQL Server 2017 servers that are on CU16 I run into a scenario where one or more databases will join […] [Using Storage DeDupe for AlwaysOn Availability Groups]( - I have a Sr. Sysadmin here who wants to turn on storage dedupe for the three nodes that will be host AG's because he really wants space savings. The setup is that I actually have 3 sets of 3 nodes. Each set of three nodes will be hosting databases from what is now a single […] SQL Server 2017 - Development [sqlcmd - Unable to pass in exact file path as an input variable to :out]( - Hi there I have a routine which outputs an ID generated from a stored procedure into a text file as follows: :!!if exist \\LONDEVSQL01\Staging\SEG_DEV_H01\Import\SupplementaryImport\StoredProcedureRunTrackerID.txt del \\LONDEVSQL01\Staging\SEG_DEV_H01\Import\SupplementaryImport\StoredProcedureRunTrackerID.txt --:connect localhost\SQL2008R2 :out \\LONDEVSQL01\Staging\SEG_DEV_H01\Import\SupplementaryImport\StoredProcedureRunTrackerID.txt select Ltrim(Rtrim(@StoredProcedureRunTrackerID)) Now i wanted to change this , so that the path for :out is not hardcoded and passed in as a variable, ie […] SQL Server 2016 - Administration [Package error]( - when i ran the package through sql agent job it got failed but i did not find any information in job history Executed as user: . Started: 11:59:55 AM Finished: 12:00:43 PM Elapsed: 47.281 seconds. The package execution failed. The step failed. How can we trouble shoot [Extended events]( - MY EXTENDED EVENT SCRIPT: CREATE EVENT SESSION [CLogins] ON SERVER ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1) ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username) WHERE (([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%SELECT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%DELETE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%UPDATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INSERT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%CREATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%DROP%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%RENAME%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TRUNCATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%COMMENT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%MERGE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%CALL%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%EXPLAIN PLAN%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%LOCK TABLE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%GRANT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%REVOKE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INDEXES%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TABLE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INDEX%')) […] SQL Server 2016 - Development and T-SQL [The data types varchar and datetime2 are incompatible in the add operator.]( - In the procedure i have date column as datetime2 declare @date datetime2 = null, @CaptureError varchar(100) when i am logging this variable like below SET @CaptureError= @CaptureError + ' ,Date: ' + coalesce(@Date, 'NULL') this is getting error as data types varchar and datetime2 are incompatible in the add operator. what is the workaround for […] [SSIS For each Loop not finding files when deployed]( - Hi, I have an issue with a SSIS package deployed to the catalog. This package includes a For Each container that scans a folder for CSV files. I can run the package from Visual Studio on my development box using my credentials and it completes successfully finds the files targeted by the for each loop […] Administration - SQL Server 2014 [Problem with calculating an approximate space required for online index rebuild]( - I have a database called TestDB which is in simple recovery model. I performed index rebuild on my Test Server which has no other user transactions except me. Before an online index rebuild the sizes of mdf and ldf files were as below:          total size   […] Development - SQL Server 2014 [Determining if more CPU is needed]( - We have a data import & processing process that often longer than expected, often delaying the time at which users can begin working. I've determined that the CPU runs around 80% for the 1-2 hours the process runs (90 minutes is the SLA), but rarely hits 100 percent. I've found some queries that look at […] SQL 2012 - General [Join Query, two tables, two databases?]( - Is that possible? To have two databases with one query? If so the query below should populate some data! If not, how can I accomplish the task? SELECT JobSheet.dbo.JobHrs.JobNo, JobSheet.dbo.JobHrs.ActualHrs, JobSheet.dbo.JobHrs.ReworkHrs, TimeReporting.dbo.TimeData.Time, TimeReporting.dbo.TimeData.Rework FROM JobSheet.dbo.JobHrs INNER JOIN TimeReporting.dbo.TimeData ON JobSheet.dbo.JobHrs.JobNo = TimeReporting.dbo.TimeData.JobNo WHERE JobSheet.dbo.JobHrs.JobNo = '19-0228B'  General Cloud Computing Questions [Archival Options due to Regulation]( -  Hi Guys, I have an ongoing need to provide a archival solution for an ex-production system for Regulatory reasons. In a nutshell: active database is around 30GB, but there will be others pending of varying sizes there may be the need for a couple of queries a year initially, tapering off potentially need to […] SSDT [Using variables in multiple packages]( - I have a SSIS Solution built in Visual Studio Community 2019 using SSDT. The solution contains about 15 packages. I am using 5 variables (created in the first package) that I would like to use in all of the other packages. Is there a way to make a variable usable in other packages within the […] [Expression won't evaluate for property]( - I am attempting to parameterize the three properties for checkpoints in a SSDT 2017 SSIS project and keep receiving this error for the CheckpointUsage property: Error loading: The result of the expression "@[$Project::parCheckpointUsage]" on property cannot be written to the property. The expression was evaluated, but cannot be set on the property. I have the […] Analysis Services [Row Level Security based on multiple factors]( - Hey there, I recently got a request from some people here to implement another level of RLS for a couple of tabular models that I manage. Background: I work for a commercial real estate company, so the security that I have implemented is based on region. Someone in Toronto can only view data from Toronto. […] Events [PASS Local Group/Virtual Group/SQL Saturday organizers corner]( - A dedicated place for PASS Local Group/Virtual Group/SQL Saturday organizers to share marketing and promotional content as well as discuss what is working and what is not (credit for the idea goes to Cecilia Brusatori)   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -