SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Finding Keys

The Voice of the DBA

Daily Coping Tip

Make some vacation plans for the future

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

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.

The Second Half of 2021

July is starting, and with it we move to the second half of 2021, or H2 2021 for those of you that like the planning shorthand on roadmaps and project manager boards.

The first half of 2021 has felt like a big of progress in the world, as we try to recover from the pandemic that froze time for most of 2020. I felt like most of last year was one grinding day after another, with little change, lots of limitations, and a constant "sameness" in my life from day to day. While I like routine, I also like some changes and little alterations that keep life interesting.

I know the first quarter of this year felt like that as well, but slowly in the US things started to change. I got my vaccine does, something I didn't expect until this month or later. The US and a few other countries have deployed so many vaccine doses that life has started to change and open a bit. I know many other countries aren't as lucky, and I do hope that more assistance and sharing comes from the US and other privileged countries to ensure that we can end this pandemic.

As things change, I'm more hopeful than I was six months ago that we will move towards a more normal way of life, albeit likely will still some changes. I know I see less masks in the US, but not zero. We still have precautions in many places, and most people seem willing to follow whatever rules a business wants. More offices are opening up, although partial remote work seems to be the accepted practice. I've seen a few music and other entertainment events start to take place, so maybe we'll see more technical events. There were good decent number of people at DevConnections in Florida, so that was a hopeful sign as well.

We are all emerging from the pandemic at different rates, with vastly different risk tolerances. Not only are countries taking different approaches, but even within a country like the US, the experience can vary. On a few recent trips, it seemed to me that we all need to be flexibly and respectful of others, willing to adapt to the situations as they occur.

The second half of 2021 will be interesting, but likely very different from the last year. No matter where you are, and how you feel, I do hope that life improves and you can enjoy yourself, both at work and away from work.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Featured Contents

Mapping Data Flows in Azure Data Factory

Sucharita Das from SQLServerCentral

This article helps to create a data flow in Azure Data Factory, add conditional split logic to the flow, and transfer data from a file to an Azure SQL Database.

Stairway to DAX and Power BI - Level 17: Time Intelligence Functions: The DAX DATEADD() Function

Bill Pearson from SQLServerCentral

Business Intelligence Architect, Analysis Services Maestro, and author Bill Pearson introduces the DAX DATEADD() function, discussing its syntax, uses and operation. He then provides hands-on exposure to DATEADD(), focusing largely upon a popular use in creating prior-period values at multiple Date hierarchy levels.

What does SET NOCOUNT ON do?

Additional Articles from Brent Ozar Unlimited Blog

When you’re working with T-SQL, you’ll often see SET NOCOUNT ON at the beginning of stored procedures and triggers. What SET NCOUNT ON does is prevent the “1 row affected” messages from being returned for every operation. Read Brent's blog to see him demo it by writing a stored procedure in the Stack Overflow database.

Testing Flyway Migrations Using Transactions

Additional Articles from Redgate

When you are using Flyway, how can you test your database migration script first to make sure it works exactly as you intended before you let Flyway execute it?

From the SQL Server Central Blogs - DDL Trigger to log database level security commands.

Kenneth.Fisher from SQLStudies

In my last post I talked about reasons why your permissions might go missing. One of the reasons, and in ... Continue reading

From the SQL Server Central Blogs - SSIS Logging – Linking sysssislog with custom log

Brahmanand Shukla from SQL Server Carpenter

I was working on an ETL solution, which was expected to run for hours. It has become extremely important to track the errors properly, so as to debug the...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Finding Keys
I am using Always Encrypted in SQL Server 2019. I have a symmetric key for the key that is used to actually encrypt the data. Which DMV stores the metadata about this key?
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)

Debugging a PowerShell Function

I have this test function for debugging. My default value for $DebugPreference is SilentlyContinue.

function Test-Debug {
    [CmdletBinding()]
    param($a)
    Write-Debug ('$DebugPreference is ' + $DebugPreference)
    Write-Host ('Default: ' + $DebugPreference)
}

If I load this into my environment and execute it, what two values are returned for the $DebugPreference variable when I execute it like this?

Test-Debug 12 -Debug
Write-Host ('Default: ' + $DebugPreference)

Answer: Inquire and SilentlyContinue

Explanation: In this case, the Debug parameter changes the $DebugPreference inside the function to Inquire. When the function ends, this returns to the value previously set, which is the default. Ref: About_Preference_Variables - https://docs.microsoft.com/en-us/powershell/module/Microsoft.PowerShell.Core/About/about_Preference_Variables?view=powershell-7.1

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 2016 - Administration

SSRS Query Designer times out - Hello experts, I am getting the following error when trying to open the Query Designer for the a report Dataset in Report Builder. The report runs successfully in the browser and in Report Builder. And the data sources for the report validate successfully. I also checked for TLS issues, but the report server is correctly […]

Error 17311 while trying to run query with OPENDATASOURCE - SQL Server 2016 SP2 terminates suddenly with the error id 17311. Here's the SQL dump error: 2021-06-27 10:32:05.29 spid63 SqlDumpExceptionHandler: Process 63 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. 2021-06-27 10:32:05.29 spid63 * ******************************************************************************* 2021-06-27 10:32:05.29 spid63 * 2021-06-27 10:32:05.29 spid63 * BEGIN STACK DUMP: 2021-06-27 10:32:05.29 spid63 * 06/27/21 10:32:05 […]

SQL Backup/Restore - Hi, Production database : I have a database Backup size is 300 GB from production server. Total size of hard disk is 1 TR in Production. Test environment: I want to restore a database in test, not all the tables from backup mdf. , i need only 50 GB data, from the database backup. Total […]

SQL Server 2016 - Development and T-SQL

Unable to create USER with T-SQL - Hello Community, Can someone explain why I'm getting the error Incorrect syntax near '-' When I run the followng sql code: CREATE USER DT-SERVICES-TEST WITHOUT LOGIN; My guess is because of the hypen '-' If that is the case, can someone let me know how to add the code with the hypen? Thanks

sp_server_diagnostics_sleep - Hi, we are trying to bulk write the data in the table. but the process seems to get slow. On checking the wait type, i found continuous records of sp_server_diagnostics_sleep. Couldn't find the real reason for the latency of bulk insert in the table. Kindly help Saumik Vora

Need to get records based on Sub Query - I have written the query using ranking function. I tried to write the same query using sub query no clue. Can any body help The query below as follows WITH HighRecords AS ( SELECT BusinessEntityID, FirstName, MiddleName, LastName, TotalDue, ROW_NUMBER() OVER(PARTITION BY BusinessEntityId ORDER BY TotalDue ASC) AS RN FROM Person.Person Pp INNER JOIN Sales.Customer […]

Trouble filling missing months of data with zero values - Hello, Hopefully someone can help a novice with the following problem i have. We have as system that records monthly reported figures and for several accounts, quite often the sites don't report their figures. So for a 12 month period there can often be gaps. I would like to show all months in a result […]

Administration - SQL Server 2014

Unable to connect to SQL Server instance - I just created 2 named instances on 2 different server. Created firewall rules, enabled TCP/IP, made sure SQL Browser was running, both instances are running under default acct. I am able to connect to 1 named instance thru SSMS from my local machine and not to the other. I am also able to ping both […]

Development - SQL Server 2014

SQL Server 2014 Developer Edition - Hi, Can we use SQL SERVER 2014 Developer free Edition for UAT environment. Please help me. Thanks, Carmelo Labadie

SQL Server 2012 - T-SQL

Cursor replacement suggestions - I have a process that uses SqlAgent jobs to process data coming from many sources, and I'm seeing performance issues trying to process the data. Looking for suggestions on processing of the data table. I have a single SP currently processing from the table and it has a parm of equip_id so I break the […]

SQL Server 2019 - Administration

Who Update the Stored procedure Last time? - Hello EveryOne, Need your help once again. I have lots of Stored PRocedures in my Database and we are 3 sql dba/developer . But need to know who updated the stored procedure last time? or by whom the stored procedure was updated last time?? Any Help or suggestion would be appriciated. Thanks in advance.

SQL Server 2019 - Development

transposing date column to rows - Hello people, I am trying to transpose date column to rows. I am not sure if this can be done for more than 10000+ rows. For example, I am trying to convert the table in image 1 to image 2. Please note that the below is just a example extract of the data , the […]

Splitting adhoc queries into a data structure - Issue - Current adhoc SQL can be generated by our reporting module that is difficult to maintain much less decipher. The TSQL is huge and almost un-manageable. This makes it extremely hard to figure out what to do since we can have hundreds of queries Partial solution - instead of using profiler we have a […]

SQL Azure - Administration

Backup/recovery using native Azure tools - Hello, Hopefully someone who manages and administers hundreds of SQL Server VM's in Azure can share their experience. We have a few dozen SQL Server VM's in Azure and a small handful of managed instances. One of the issues we run into is the lack of visibility into backup failures, as well as being able […]

Powershell

load xls file to sql - Looking for a script to load xls files to SQL without using dbatools or a Driver that's need to be loaded... Is there any Native loads of xls to SQL using PS script? I've been trying to use a script that converts cls to csv, but I run into issues on fields where the delimter […]

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.