SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

The Backup Operator in MSDB

The Voice of the DBA

Daily Coping Tip

Enjoy photos from a time with happy memories

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.

Location Data Is Not Anonymous

We are truly in the era of big brother and 1984, where any of us can be tracked if we use a mobile device. That's a scary thought and one that many people might not like. There are plenty of people in management that want to increase revenue or profits from the use of data, even if we might think their decisions are unethical or immoral. When there's money to be made, often they ignore other issues.

There was a post from Bruce Schneier earlier this summer that noted various public data, some sold by brokers, was used to track location and usage of a device by de-anonymizing aggregated data. The post ends with these statements:

"Location data is not anonymous. It cannot be made anonymous. I hope stories like these will teach people that."

That's a somewhat scary thought. While I like knowing where my loved ones are, I'm not sure I want any random person to be able to track me with public information. Unfortunately, that is unlikely to be the case in the future. Lots of the data we have rarely worried about being public is much easier to access when records become available digitally to anyone. They ability to work with large amounts of data allows anyone with a connection to gather information that might be used in ways we never imagined.

For me, public records of property at the most annoying, as I constantly get unsolicited real estate offers to buy houses. For others, there could be concerns about safety, stalking, or other nefarious purposes. Swatting and other forms of harassment are far too common in the modern world.

While many might see the GDPR, and similar laws, as too restrictive for businesses and government, I think privacy is more important than ever. We ought to have more strict regulation on the use of data about humans, and force companies to obtain consent, allow it to be revoked, and be forced to properly manage and secure our data.

This doesn't just apply to location data, but any data about humans. I hope the future becomes a place where we globally consider digital privacy a human right.

Steve Jones - SSC Editor

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

Featured Contents

Incremental Data Loading Using ADF and Change Tracking

Sucharita Das from SQLServerCentral

This article looks at moving data in three related tables to Azure SQL Database using the change tracking feature in SQL Server to determine which rows have been changed.

Building blocks of Extended Events data collection

Additional Articles from SimpleTalk

Extended Events have been part of SQL Server since 2008. In this article, Ed Pollack explains the building blocks of Extended Events data collection.

SQL Server Diagnostic Memory Queries

Additional Articles from Glenn's SQL Server Performance

Microsoft Data Platform MVP Glenn Berry demonstrates a set of eleven SQL Server Diagnostic Memory Queries that help you understand your memory usage in SQL Server.

From the SQL Server Central Blogs - Getting Up and Running with Dapr

Steve Jones - SSC Editor from The Voice of the DBA

I’ve heard about Dapr a few times from developer friends, but hadn’t really understood it that well. I had a webinar coming up, so I decided to spend a...

From the SQL Server Central Blogs - #PowershellBasics: Bulk insert.

Kenneth.Fisher from SQLStudies

In my last post I talked about invoke-sqlcmd. It’s a nice easy way to run a query or a .sql ... Continue reading

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	The Backup Operator in MSDB
I have a default US English installation of SQL Server 2019 and connect as a sysadmin. I create a new user with this script: USE [master] GO CREATE LOGIN [JoeBackup] WITH PASSWORD=N'XXXxxx', DEFAULT_DATABASE=[sandbox], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE [sandbox] GO CREATE USER [JoeBackup] FOR LOGIN [JoeBackup] GO USE [sandbox] GO ALTER ROLE [db_backupoperator] ADD MEMBER [JoeBackup] GO This user logs in and is placed in the sandbox database. The user, JoeBackup, then executes this script: USE msdb GO SELECT * FROM dbo.backupmediaset AS b GO What happens?
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)

Deleting a Folder in Azure Data Factory

I have a pipeline in Azure Data Factory, and I have added a Delete activity to my pipeline. I want to delete all the files in the "August2021" folder, and the folder itself after I've imported data.

How do I configure a single Delete activity to do this?

Answer: Configure the folder path in the dataset and check recursively in the Delete activity

Explanation: If you check the recursively option in the delete activity, the folder is also deleted. Ref: Working with the Delete Activity in Azure Data Factory - https://www.sqlservercentral.com/?p=3910643&post_type=ssc_article&preview_id=3910643

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 - Development and T-SQL

Result set different after breaking up case expression in where - I'm back again with more dumb questions. This time I am dealing with refactoring a single select with nested case expressions in the where clause. I'm trying to optimize this, and in attempting to do so, I broke up the query into multiple selects w/ union alls. This does run quite a bit faster, but […]

Sending Locking Alert - I have 2 tables the first one holds the dbwait information(locks), and the second table holds the db sessions related to the locks. I'm in build block stage where I have the files loading to SQL, and will make updates to the Table Defs(PK,Index) after some testing. Just trying to work thru the logic of […]

Export empty JSON with headers & footers - Hello, I have inherited a process that utilises an SSIS package and related stored procs that creates a JSON file. The file we create looks like - A number of objects in an array in a nested hierarchy. If we have no data from our process at present we create an empty file. What I […]

How to Convert Oracle SQL to T-SQL - Hello Community, I have been given a challenge of converting the following SQL code written in Oracle to T-SQL. INSERT INTO tbl_probate_case ( case_data_id , cd_reference , case_created_date , created , submitted , examined , stopped, issued , issued_in_20days, ce_app_type , ce_app_sub_date , ce_reg_location , ce_will_exists , ce_iht_gross_value , ce_iht_net_value , ce_deceased_dod , ce_deceased_other_names , […]

The metadata could not be determined because statement - Hi everyone, I am running into a \n issue and it seems like none of the solutions I found online helped me to fix it. So basically I want to have the result of a stored procedure into a temp table. I cannot use functions as it gets complex. so My first solution didn't work […]

SQL 2012 - General

Adding a PRIMARY KEY CLUSTERED - Is there a T-SQL way to add the following to an existing table that already has data in it?: PRIMARY KEY CLUSTERED ( [CALL_ID] ASC, [DATE] ASC, [TIME] ASC, [CALL_TYPE] ASC, [DOMAIN] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON […]

SQL Server internal variable with DML type - Can you advice how to retrieve the DML type a query is running? Does an internal variable exist for this? I mean, after running a query DELETE FROM .... the DML type would be DELETE, after INSERT INTO TABLE, the DML type would be INSERT INTO. Thanks

SQL Server 2019 - Administration

Back up and restore Synchronized database - Hello I have 2 database servers one as a primary and another as a secondary. I have synchronized them through availabity group settings. The vendor of the database wants to do some updates on the database locally which means i have to take a back up of the database and send them the back up. […]

Please recommend me some documents about SQL ? - I am working with a new field, which is SQL, do you know about it? Reading documents over the Internet makes me unable to understand some parts. I am looking for some external documents and books, can you recommend them to me?

SQL Server 2019 - Development

Results-To-Text , Programatically - I sometimes run queries against an IBM DB2 database, using an old Navigator tool that has limited functions. In some cases I want the results in 1 pane, so I can do various string searches with CTL-F. Is there a way to write a query to dump all the rows, with line breaks, into 1 […]

How to return a range value based on the first day of year and the value of row - Hello, i have a table called Events, the value of that table like as bellow : create table Events ( id int, code_events varchar(10), Events varchar(10), Events_start datetime, Events_end datetime ) insert into Events values(1,'AC_83','Event 1','2020-07-15','2020-07-30') insert into Events values(2,'AC_84','Event 2','2019-06-01','2030-07-30') insert into Events values(3,'AC_86','Event 3','2020-07-15','2022-12-15') insert into Events values(4,'AC_83','Event 1','2020-09-15','2025-04-30') insert into Events values(5,'AC_87','Event […]

SSRS Report Parameters Interactive - Hi, I have a table that has a dept_id, sect_id, div_id, item_id, and description. The data will be reported on base on the users entry on dept_id, sect_id, div_id and item_id. For some reason I cannot work out the parameter entries in such a way that it is interactive depending on the user input. I […]

Reporting Services

Data Driven Subscription by Email? - Hi, Try to find an example of a data driven subscription Via Email. What I would like to do is run a report with each row has an employee associated with a client. I would like to send via email only the clients associated with the client . Thanks

Anything that is NOT about SQL!

Right place for newly graduates? - Hi everyone, I have a brother who just graduated with a programming major. And he seems lost in his own world so i’m here asking y’all where is the right place for newly graduates? Should they work as an intern or an official employee?

General

Oracle: ORA-01843: not a valid month - Hi I have the following query which works fine in Oracle SQL Developer. When I try to use it in Microsoft PBI I get the following error "ORA-01843: not a valid month". Does anyone have any ideas on why? with a as (select user_id,dstamp d,code c, from_loc_id f ,to_loc_id t,sku_id,container_id cid,notes,update_qty u,v_order_sub_type_1 ,v_order_type,substr(from_loc_id,0,6)||''||floor(to_char(dstamp,'sssss.ff')/300)ac from dcsdba.inventory_transaction itl where dstamp […]

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.