SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Adding a Database with Polybase External Tables to Always On Availability Group

ramya.sqlservr from SQLServerCentral

Learn how to ensure your PolyBase objects are accessible from all nodes in an Availability Group.

Implementing a Data Quality Monitoring Framework

Additional Articles from SQLServerCentral

For any Business Intelligence solution to be useful for analytical and decision-making purposes, it is imperative that the underlying data quality is good so, that reports generated are reliable to make business decisions. This article from Imran Quadri Syed walks the through steps involved to implement a successful Data Quality Monitoring framework that would validate incoming data against predefined data quality rules and capture data quality metrics.

From the SQL Server Central Blogs - Stairway to Microsoft Azure SQL Database Part 3 : Purchase Models DTU vs vCore continue

nelsonaloor from PracticalSQLDba

In the last post, we have discussed the DTU purchase model. Azure also provides another purchase mode which is called vCore. vCore purchase model available with all three deployment...

From the SQL Server Central Blogs - SQLpassion Online Training about Availability Groups

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...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	The First Sequence
I create this new sequence object: CREATE SEQUENCE dbo.IncrementByOne AS INT INCREMENT BY 1; What value do I get from this code? SELECT NEXT VALUE FOR dbo.IncrementByOne
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)

Rounding and Sums

I've got this code:

WITH numberCTE (n)
AS
(SELECT n 
 FROM (VALUES (3.7), (-2.1), (2.9), (-3.1) ) A(n)
 )
 SELECT ROUND(SUM(n),0), SUM(ROUND(n,0))
  FROM numberCTE

Do you expect these two values in the result set to be the same?

Answer: No

Explanation: These aren't the same, and you shouldn't expect this. In this case, the raw sum is 1.4. However, if we sum first and round, we get 1.0. If we round first, then sum, we get 2.0. Ref: ROUND - https://docs.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver15 SUM - https://docs.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql?view=sql-server-ver15

Discuss this question and answer on the forums

Featured Script

SQL Server Database engine patching

Dnirmania from SQLServerCentral

This script can install Service pack, security patch and Cumulative update on SQL instance(Database Engine).

#----------------- Loading Required assebmlies -------------------#
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')|Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") |Out-Null
[System.Windows.Forms.Application]::EnableVisualStyles()
#------- Declaring variables ---------#
$server = New-Object ('Microsoft.SqlServer.Management.SMO.Server') localhost
$version = $server.Information.Version.Major
$global:Binary_File_Loc = $null
$global:Pre_req_Folder_Path = $null
$global:Execution_type = $null
$global:Backup_type = $null
$global:OK_Button_value = $null
#------- Function to create Form ---------#
Function Get-Form{
$Font = New-Object System.Drawing.Font("Arial",8,[System.Drawing.FontStyle]::Bold)
$Form = New-Object system.Windows.Forms.Form
$Form.Text = "Patching Tool"
#----- Form item Decalartion -----#
$Exec_type_Label = New-Object System.Windows.Forms.Label
$Backup_label = New-object System.Windows.Forms.Label
$P_Loc_label = New-Object System.Windows.Forms.Label
$Binary_loc_label = New-Object System.Windows.Forms.Label
$Ok_Button = New-object System.Windows.Forms.button
$Cancel_Button = New-object System.Windows.Forms.button
$Fetch_P_loc_Button = New-object System.Windows.Forms.button
$Fetch_Binary_F_Button = New-object System.Windows.Forms.button
$P_Loc_TextBox = New-Object System.Windows.Forms.TextBox
$Binary_F_loc_TextBox = New-Object System.Windows.Forms.TextBox
$Opt1_Radio_button = New-object System.Windows.Forms.RadioButton
$Opt2_Radio_button = New-object System.Windows.Forms.RadioButton
$Backup_DropDown = new-object System.Windows.Forms.ComboBox
$Form.Height = 500
$form.Width = 600
#------ Labels Defination -------#
$Exec_type_Label.Text = " Execution Type:"
$Exec_type_Label.AutoSize = $True
$Exec_type_Label.Font = $Font
$Exec_type_Label.Location = New-Object System.Drawing.point(25,40)
$Backup_label.Text = " Backup Type:"
$Backup_label.AutoSize = $True
$Backup_label.Font = $Font
$Backup_label.Location = New-Object System.Drawing.point(25,110)
$P_Loc_label.Text = " Pre-requisites Location:"
$P_Loc_label.AutoSize = $True
$P_Loc_label.Font = $Font
$P_Loc_label.Location = New-Object System.Drawing.point(25,170)
$Binary_loc_label.Text = " Binary File Location:"
$Binary_loc_label.AutoSize = $True
$Binary_loc_label.Font = $Font
$Binary_loc_label.Location = New-Object System.Drawing.point(25,230)
#------ Radio Buttons Defination -------#
$Opt1_Radio_button.Text = "Perform pre-requisites, install patch"
$Opt1_Radio_button.Autosize = $True
$Opt1_Radio_button.Location = New-Object System.Drawing.point(205,40)
$Opt1_Radio_button.Add_Click({if($Opt1_Radio_button.checked)
{$P_Loc_TextBox.Enabled = $True
$Fetch_P_loc_Button.Enabled = $True
$Backup_DropDown.Enabled = $True}})
$Opt2_Radio_button.Text = "Install patch"
$Opt2_Radio_button.Autosize = $True
$Opt2_Radio_button.Location = New-Object System.Drawing.point(205,60)
$Opt2_Radio_button.Add_Click({if($Opt2_Radio_button.checked)
{$P_Loc_TextBox.Enabled = $false
$Fetch_P_loc_Button.Enabled = $false
$Backup_DropDown.Enabled = $false}})
#------ TextBoxes Defination ------#
$P_Loc_TextBox.Location = New-Object System.Drawing.point(205,170)
$P_Loc_TextBox.Size = New-Object System.Drawing.Size(300,30)
$Binary_F_loc_TextBox.Location = New-Object System.Drawing.point(205,230)
$Binary_F_loc_TextBox.Size = New-Object System.Drawing.size(300,30)
#------ Buttons Defination -------#
$Fetch_P_loc_Button.Text = "..."
$Fetch_P_loc_Button.Font = $Font
$Fetch_P_loc_Button.size = New-Object System.Drawing.Size(30,20)
$Fetch_P_loc_Button.Location = New-Object System.Drawing.point(505,170)
$Fetch_P_loc_Button.Add_Click({ $P_Loc_TextBox.Text = Get-FolderName })
$Fetch_Binary_F_Button.Text = "..."
$Fetch_Binary_F_Button.Font = $Font
$Fetch_Binary_F_Button.size = New-Object System.Drawing.Size(30,20)
$Fetch_Binary_F_Button.Location = New-Object System.Drawing.point(505,230)
$Fetch_Binary_F_Button.Add_Click({ $Binary_F_loc_TextBox.text = Get-FileName Get-Location})
$Ok_Button.Text = "OK"
$Ok_Button.Font =$Font
#$Ok_Button.DialogResult = [System.Windows.Forms.DialogResult]::OK
$Ok_Button.location = New-Object System.Drawing.point(175,330)
$Ok_Button.Add_Click({
Button-OK
$global:OK_Button_value = "OK"
if($Opt1_Radio_button.checked -eq $true -or $Opt2_Radio_button.Checked -eq $true -and $Backup_DropDown.text.Length -ne 0 -and $P_Loc_TextBox.text.Length -ne 0 -and $Binary_F_loc_TextBox.text.Length -ne 0)
{
$form.Close()
} })
$Cancel_Button.Text = "Cancel"
$Cancel_Button.Font =$Font
$Cancel_Button.location = New-Object System.Drawing.point(325,330)
$Cancel_Button.Add_Click({$form.close(); $global:OK_Button_value = "Cancel"})
# ------ Drop Down Defination ------#
$Backup_DropDown.Location = New-Object System.Drawing.Point(205,110)
$Backup_DropDown.Size = New-Object System.Drawing.Size(100,20)
$Backup_DropDown.DropDownStyle = [System.Windows.Forms.ComboBoxStyle]::DropDownList;
[void] $Backup_DropDown.Items.Add('Full')
[void] $Backup_DropDown.Items.Add('Log')
[void] $Backup_DropDown.Items.Add('Skip Backup')
$form.Maximizebox = $false
$Form.Controls.Add($Exec_type_Label)
$Form.Controls.Add($Backup_label)
$Form.Controls.Add($P_Loc_label)
$Form.Controls.Add($Binary_loc_label)
$Form.Controls.Add($Opt1_Radio_button)
$Form.Controls.Add($Opt2_Radio_button)
$Form.Controls.Add($P_Loc_TextBox)
$Form.Controls.Add($Binary_F_loc_TextBox)
$Form.Controls.Add($Fetch_P_loc_Button)
$Form.Controls.Add($Fetch_Binary_F_Button)
$Form.Controls.Add($Ok_Button)
$Form.Controls.Add($Cancel_Button)
$Form.Controls.Add($Backup_DropDown)
$Form.ShowDialog()
$Form.Dispose()
}
#----------- Function for OK Button ------------#
Function Button-OK
{
if($Opt1_Radio_button.checked -eq $false -and $Opt2_Radio_button.checked -eq $false)
{
[System.Windows.Forms.MessageBox]::Show('Please check one radio button','Patching Tool','OK','Error')
return
} elseif($Opt1_Radio_button.checked)
{
$global:Execution_type = 1
}elseif($Opt2_Radio_button.Checked)
{
$global:Execution_type = 2
}
if($Backup_DropDown.Enabled.Equals($true))
{
if($Backup_DropDown.text.Length -ne 0)
{
$global:Backup_type = $Backup_DropDown.SelectedItem.ToString()
}
else
{
[System.Windows.Forms.MessageBox]::Show('Please select one Backup type','Patching Tool','OK','Error')
return
}
}
if($P_Loc_TextBox.Enabled.Equals($true))
{
if($P_Loc_TextBox.text.Length -ne 0)
{
$global:Pre_req_Folder_Path = $P_Loc_TextBox.Text.ToString()
}
else
{
[System.Windows.Forms.MessageBox]::Show('Please enter Pre-requisites location','Patching Tool','OK','Error')
return
}
}
if($Binary_F_loc_TextBox.text.Length -ne 0)
{
$global:Binary_File_Loc = $Binary_F_loc_TextBox.Text.ToString()
}
else
{
[System.Windows.Forms.MessageBox]::Show('Please enter Binary file location','Patching Tool','OK','Error')
return
}
}
Function Get-FileName($initialDirectory)
{
#[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") |Out-Null
$OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
$OpenFileDialog.initialDirectory = $initialDirectory
$OpenFileDialog.filter = "Executable file (*.exe)| *.exe"
$OpenFileDialog.ShowDialog() | Out-Null
$OpenFileDialog.filename
}
#--- Function to get Change folder location----#
Function Get-FolderName
{
$browse = New-Object System.Windows.Forms.FolderBrowserDialog
$result = $browse.ShowDialog((New-Object System.Windows.Forms.Form -Property @{TopMost = $true }))
if($result -eq 'OK')
{
$browse.SelectedPath
}
else
{
exit
}
}
# ------- Function to get Job Status ---------#
Function Get-JobStatus
{
Param ($JobName)
Start-Sleep -Seconds 2
while(($server.JobServer.Jobs["$JobName"]).CurrentRunStatus -like 'Executing')
{
Start-Sleep -Seconds 10
$server.JobServer.Jobs["$JobName"].Refresh()
}
If(($server.JobServer.Jobs["$JobName"]).CurrentRunStatus -eq 'Idle' -and ($server.JobServer.Jobs["$JobName"]).LastRunOutcome -eq 'Succeeded')
{
Write-Host "Backup job completed successfully"
}
}
# ----------------- Function to perform Prerequisites --------------#
Function Perform-Prerequisites{
try{
#$Folder_Path = Get-FolderName
#----- Creating New File to keep current version of SQL Server ----#
$server.Properties|Where-object {$_.Name -eq 'Product' -or $_.Name -eq 'Edition' -or $_.Name -eq 'VersionString' -or $_.Name -eq 'ResourceVersionString' -or $_.Name -eq 'ProductLevel' } | Format-Table Name, Value | Out-file "$global:Pre_req_Folder_PathVersion_detail.txt"
Write-host "Version details stored in $global:Pre_req_Folder_Path location `n"
#----- Creating New file to keep all SQL services service account detail -----#
Get-WmiObject Win32_Service -Filter "Name like '%SQL%'"|ft __Server,State,Name,DisplayName,StartName, ProcessID, StartMode -AutoSize|Out-File "$global:Pre_req_Folder_PathService_Account_Detail.txt"
Write-host "Service account details stored in $global:Pre_req_Folder_Path location `n"
#------ Stopping SQL Services --------#
$Master_DB_loc = $server.Information.MasterDBPath
$Resource_DB_Loc = $server.Information.RootDirectory
Write-host "Stopping MSSQL services.... `n"
Get-Service "*SQL*" | Stop-Service -Force
#----- Copying all system DB files to New Folder ------#
Start-Sleep -Seconds 2
Get-ChildItem -Path "$Master_DB_loc" -Filter 'mast*' | Copy-Item -Destination "$global:Pre_req_Folder_Path"
Get-ChildItem -Path "$Master_DB_loc" -Filter 'model*'| Copy-Item -Destination "$global:Pre_req_Folder_Path"
Get-ChildItem -Path "$Master_DB_loc" -Filter 'MSDB*' | Copy-Item -Destination "$global:Pre_req_Folder_Path"
Get-ChildItem -Path "$Resource_DB_LocBinn" -Filter '*systemresource*' | Copy-Item -Destination "$global:Pre_req_Folder_Path"
Write-host "All system databases files copied to $global:Pre_req_Folder_Path location `n"
}
catch{
Write-Host $_.Exception.Message
Write-Host "Prerequisites are failed"
exit
}
}
# --------------- Function to Execute backup job -----------------#
Function Execute-Backupjob{
Try{
switch($global:Backup_type)
{
'Full'
{
$JobName = ($server.JobServer.Jobs| Where-Object {$_.Name -like 'Give JOb Name'}).Name #Provide Job name here if using SQL Agent jobs to perform backups
Write-Host "Executing $JobName Job"
Invoke-Sqlcmd -ServerInstance localhost -Query ("EXECUTE msdb.dbo.sp_start_job '$JobName'")
Get-JobStatus $JobName
break;
}
'Log'
{
$JobName = ($server.JobServer.Jobs| Where-Object {$_.Name -like 'Job Name'}).Name #Provide Job name here if using SQL Agent jobs to perform backups
Write-Host "Executing $JobName Job"
Invoke-Sqlcmd -ServerInstance localhost -Query ("EXECUTE msdb.dbo.sp_start_job '$JobName'") |Out-Null
Get-JobStatus $JobName
break;
}
'Skip Backup'
{
Write-Host "Backup has been skipped `n"
break;
}
}
}
catch{
Write-Host $_.Exception.Message
Write-Host "Backup job execution was failed"
exit
}
}
Function Get-Logfile{
#$version = $server.Information.Version.Major
$version = $version.ToString() + '0'
$progfiles = ${env:ProgramFiles}
$Path = $progfiles + "Microsoft SQL Server$versionSetup BootstrapLog"
Write-host "Use following path to access the log file"
write-host "$PathSummary.txt" #to_verify_the_path
cd $Path
.Summary.txt
}
Function Install-Patch{
try{
#------ Installing SQL server patch -------#
Write-host "Patch installation started..."
powershell.exe $global:Binary_File_Loc /quiet /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances
$installation_process = Get-Process "*SQLServer*"
while($installation_process.Count -eq 0)
{
Write-host "Patch File is getting extracted ...."
Start-Sleep -Seconds 10 # --- Time given to extract the patch file ---- #
$installation_process = Get-Process "*SQLServer*"
}
#---------- TO hold the powershell session ---------#
$installation_process = Get-Process "*SQLServer*"
while($installation_process.Count -ne 0)
{
Write-host "Installation is in progess ...."
Start-Sleep -Seconds 30
$installation_process = Get-Process "*SQLServer*"
}
}
catch{
Write-Host $_.Exception.Message
Write-Host "Patch installation was failed"
exit
}
}
Get-Form
# write-host $global:OK_Button_value
# write-host $global:Backup_type
# Write-Host $global:Execution_type
# write-host $global:Binary_File_Loc
# write-host $global:Pre_req_Folder_Path
#-------------------- Main code ----------------------------#
if($global:OK_Button_value -eq 'OK')
{
if($global:Execution_type -eq 1)
{
Execute-Backupjob
Perform-Prerequisites
Install-Patch
Get-Logfile
}
elseif($global:Execution_type -eq 2)
{
Write-host "Stopping MSSQL services.... `n"
Get-Service "*SQL*" | Stop-Service -Force
Install-Patch
Get-Logfile
}
}
else
{
exit
}

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

One of six 2017 instances offline - Always On. - Did patch Tuesday yesterday across 6 UCS blades. Servers are 2019 datacenter in a cluster. Each blade has 4 instances so 4 availability groups of 6 members each. One of the 2017 instances is in a bad state. I've tried a repair, I've tried removing updates (even though the other 5 prod blades are fine, […]

SQL 2017 AGL - only accessible locally - Hi, I've got 2 x SQL 2017 CU20 (Dev edition) boxes in a Windows cluster with an AG and listener (non readable sync secondary). The DBs are synchronised and the AGL is contactable through SSMS on the primary server when I'm RDPd on. The SPN is set up for the AGL name with the SQL […]

SQL Server 2016 - Administration

Logs - Can you check if anyone is using the database based on the logs? Want to check if anyone is accessing the db. If yes, with logs how is that possible? I was thinking of checking with schema changes in the db.

How do we tune a huge DB with ~2TB with huge tables , where do we start ? - Forum, How do we tune a huge DB with ~2TB with huge tables most of them are heaps and some have cluster and non clustered indexes, this is not about query tuning need to implement best practices to run this huge DB efficiently. Index rebuilt/reorg is in place and working as expected, archiving most unused […]

SQL Server 2016 - Development and T-SQL

Encryption table and saving in 2016 - Hi, How would you encrypt data from multiple tables and save in a temp tables using a script (tsql)? please share general logic in sql 2016 version. Should we use always on encryption? or something else and then also how do you decrypt the data from the same temp table and save those in the […]

Development - SQL Server 2014

Convert Sting value to DateTime2 - Hello, I am trying to convert '2020-04-29-14.41.26.686978' this value to DateTime2. Can you please suggested the best way. Thanks, Sab

SQL Server 2019 - Development

Export sql results to csv - Hi all, Hopefully you can help me. I have created a sql job which returns some results and then exports it to a csv file. But each column is merged into one cell. The way I have done this is using the sqlcmd feature as per the below: sqlcmd -i Is there […]

How to use stored procedure result set into a script task in SSIS - Hello, guys! I created a SSIS package in Visual Studio Community 2019: Everything is working fine, but I don't know how to pass the data from "Commutations" (SQL Task) to "Net Present Value" (Script Task). Basically, "Commutations" generates a result set that I'm passing to a SSIS object variable. This result set has a couple […]

Pivot Table Problem - I am having a huge brain fart on how to accomplish what I want. I want to pivot the attached set of data (sql file) to look like this (I know its an image but I am not savy enough to plop an actual result set in here). The attached file creates a temp file […]

Result data for date, even when it doesn't exist - I have this table: CREATE TABLE dbo.legalchanges ( userid bigint NOT NULL, TCChanges int NOT NULL, NonCompChanges int NOT NULL, ActChanges int NOT NULL, CatChanges int NOT NULL, SubCatChanges int NOT NULL, ExFromCompChanges int NOT NULL, MatterChanges int NOT NULL, BlockChanges int NOT NULL, VagueChanges int NOT NULL, datecreated varchar(27) NOT NULL ); The data […]

PRINT statement; output displayed on Results tab? - I came across this by chance when I was testing the assignment of variables. This SELECT query returns a single column with 12 rows. The last value is assigned. The result of this is the number 70013 displaying on the Messages tab in the results pane window. This makes sense. DECLARE @placeHolder int SELECT @placeHolder […]

WHERE table1.column1 = table2.column2 (does the order of assignment matter?) - I just started reading about the EXISTS operator. I have two examples. In both examples I have a sub SELECT with a WHERE clause. In the first example, at the inner WHERE clause I am referencing the "inner" table first, which is called orders, then the customer_id column within the orders table. Then I have […]

How to find multiple repeated occurrences with regular expression using like? - Seems SQL Server doesn't have a regular expression function. I am trying to query for records in which a varchar field contains values 123.x.5, where x is a integer of 1-3 digits. So I want to retrieve values 123.1.5, 123.12.5 and 123.123.5, but not 123.12.3.5. I can get the single digit case with "like […]

Integration Services

Execute Powershell Script from Script Task - Has anyone managed to execute a PoSh script from within a Script Task? When I started looking at this, it seemed like it would be straightforward enough and the process is described here. But ... the DLL which that article refers to (and which appears to be necessary to execute PoSh from C#) is called […]

COVID-19 Pandemic

Daily Coping 20 May 2020 - Today’s tip is: Hand-write a note to someone you love and send them a photo of it. http://voiceofthedba.com/2020/05/20/daily-coping-20-may-2020/

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.