SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

An Incomplete Window Definition

Featured Script

Simple T-SQL implementation for running SSRS subscriptions.

The Voice of the DBA

A Lack of Privacy in Smart Cars

I own a Tesla, and I love having a bunch of data about my usage of the car. I can see how much I've charged it, what it costs to power, where I drive, aggregates of my monthly usage, and more. It's especially cool when my wife is coming to pick me up, and I can see where the car is, so I know when to go outside if the weather isn't great. I also like the ability to cool or heat the car in advance of going outside in extreme temperatures.

However, all that data also means there are potential issues with privacy and certainly security. I am well aware that location and other data are being captured by Tesla as I use the car. However, it's not just Tesla. Lots of modern cars are collecting lots of data. Mozilla had a report on data privacy in cars, and all the manufacturers failed their data privacy test. There's also a summary at Engadget of the results.

Reading through the report, it seems that many of the manufacturers of cars are covering themselves from liability. Cars are sold and used in many jurisdictions and often there can be a wide variety of regulations about data, even inside of a single country. It seems that the policies are often written just in case something happens and they collect data from your use of the car (or they record you using the car).

A point I hadn't considered in the article dealt with the deletion of data when a car is sold. That could get tricky as not only is your usage data in the car, some data might potentially be in a manufacturer's database. Or it might be stored in devices at a service center. Who knows what gets stored and copied in modern cars as they are serviced. Will we potentially have issues with mechanics or other workers stealing and selling data from entertainment or other information systems in cars? The possibilities make my head hurt in this modern world where everything can, and often does, collect data.

I don't know what data is stored inside modern vehicles or other systems. In some sense, I think that companies ought to disclose what they collect, and include examples of what this data looks like. Even when I read policies, like this one from Microsoft, I'm not completely sure if I know what data they may be collecting.

The world of data privacy is complex, and as I've written before, I'm not even sure exactly how I would like my data handled. I think the GDPR is a good start, but I hope that we continue to evolve protections that ensure humans have more control over their data than the companies that collect it.

Steve Jones - SSC Editor

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

Featured Contents

Using the Import Extension in Azure Data Studio

Steve Jones - SSC Editor from SQLServerCentral

Learn how this extension in Azure Data Studio makes it easy to import flat files in a CSV or JSON format.

TABLOCK Hint can Negatively Impact SQL Server Concurrency

Additional Articles from MSSQLTips.com

In this article, we look at how using the Tablock hint in SQL Server could impact concurrency and block other sessions.

Working with Azure DocumentDB: SQL & NoSQL Together

Additional Articles from Dr. Dobbs

Updating and deleting documents, creating queries, and managing query results in Microsoft's new NoSQL database-as-a-service.

From the SQL Server Central Blogs - Is that the right question to ask?

K. Brian Kelley from Databases – Infrastructure – Security

This is not a “clickbait” title, but an important consideration when it comes to developing technical solutions. Let me give you an example between two questions for SQL Server...

From the SQL Server Central Blogs - Cosmos DB – Monitoring performance

Arthur Daniels from Arthur's Blog

Cosmos DB has become my latest focus and I hope to start sharing more about ways you can use it. Cosmos DB doesn’t provide the same granularity of query...

The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform

Site Owners from SQLServerCentral

Build efficient and scalable batch and real-time data ingestion pipelines, DevOps continuous integration and deployment pipelines, and advanced analytics solutions on the Azure Data Platform. This book teaches you to design and implement robust data engineering solutions using Data Factory, Databricks, Synapse Analytics, Snowflake, Azure SQL database, Stream Analytics, Cosmos database, and Data Lake Storage Gen2.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	An Incomplete Window Definition
I have defined code as follows in SQL Server 2022: SELECT SUM(sod.OrderQty) OVER(ob_OrderID_ProductID partition BY sod.SalesOrderID) AS Total FROM Sales.SalesOrderDetail AS sod WHERE sod.ProductID IN (710, 711, 712) WINDOW ob_OrderID_ProductID AS (ORDER BY sod.SalesOrderID, sod.ProductID) What is the default framing for a window definition such as 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)

Skipping Checks when Detaching

If I set the @skipchecks variable to false when running sp_detach_db, what does this do?

Answer: will run update statistics before detaching

Explanation: If this is false, update statistics is run. If it's true, then this isn't run. The default is null. Ref: sp_detach_db - https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-detach-db-transact-sql?view=sql-server-ver16

Discuss this question and answer on the forums

Featured Script

Simple T-SQL implementation for running SSRS subscriptions.

Louis Bravo from SQLServerCentral

I created this stored procedure to be able to run SSRS subscriptions using T-SQL code instead of adding a component in the SSIS package with the subscription id. Let's say we have a set of report subscriptions named like this: ClientReport1, ClientReport2, ClientReport3. To run them using the stored procedure, all we need is this […]

CREATE PROCEDURE [dbo].[SR_RunSubscriptions]
         @Pattern varchar(max)
AS
BEGIN
       SET NOCOUNT ON;
       SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
       DECLARE @SubscriptionID nvarchar(max);
       DECLARE DbcurSQL CURSOR FOR
              (
             SELECT SubscriptionID FROM
             [ReportServer].dbo.[Subscriptions]
             WHERE [Description] LIKE @Pattern
              );
       OPEN DbcurSQL;
       FETCH NEXT FROM DbcurSQL INTO @SubscriptionID;
       WHILE @@FETCH_STATUS = 0
            BEGIN
                EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription',@EventData=@SubscriptionID;
                FETCH NEXT FROM DbcurSQL INTO @SubscriptionID;
            END;
       CLOSE DbcurSQL;
       DEALLOCATE DbcurSQL;
END

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

Semaphore Timeout Expired Issue When Communicating with Web Server - I've been encountering a persistent issue while attempting to read/write to a web server. The error message I'm receiving is A transport-level error has occurred (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) I've already ensured that the firewall is enabled and properly configured, and there don't seem to be any […]

IMPLICIT TRANSACTIONS - Hi All, In our environment we have bunch of vendor specific db's where it is using SET IMPLICIT_TRANSACTIONS ON setting and it is causing a lot of BLOCKING. especially for long running adhoc batches with multiple statements and transactions,keeping the transactions open and holding locks for longer duration. So, the issue started when one of […]

Elevated permissions to developers? - Hi All, Is it a good idea to give any elevated permissions to developers? In one of the sub-prod environments, Dev team is running some newly developed stored procedures from SSMS and Frontend UI. When they see the blocking they are reaching out to us and we are providing where it is getting blocked and […]

Unsupported OS to update the driver - Hello, I have a client machine, which doesn't connect SQL Server. The driver seems unsupported but the operating system doesn't support to install the latest driver. Is there any workaround to connect to SQL using old version of driver?

SQL Server 2017 - Development

Commit transaction inside loops - Hello, our users are complaining about blocking during a delete loop so I'm thinking that adding begin tran; and commit tran; inside the loops would allow the other queries to squeeze through in between delete loops. Is this how people would achieve a reduction in impact while deleting data and is the below query pretty […]

SQL Server 2016 - Development and T-SQL

SQL runs slow in a union all statement - Could you please help? My Union all sql query consist of three individual queries that runs quickly on its own. However when they are in a union all statement it runs up to a point then kind of hangs and then resume after a while. It runs for a long time. The first two queries […]

Reporting Services

The subscription contains parameter values that are not valid - Hi, I've got an issue on SSRS where I have a daily subscription that works most of the time but sometimes the subscription fails with the message "The subscription contains parameter values that are not valid". This is without any changes to the subscription and when you manually run it, it goes through successfully. Checking […]

Analysis Services

Invalid location of measures when opening tabular model in excel - Hello, I built my first Analysis Services Tabular project, however the icon of measures and dimensions were not as expected. Also location of measures is under the dimension. (images attached) Any idea what could be wrong with the project setup ? Thank you.

Working with Oracle

Sequentially update a column with a decrementing value - I want to update the "QTY_REQ" column in the PLSQL table in the screen cap below sequentially row by row by dividing out a number (e.g. 7) among the rows. I want to start with the row with the highest value in QTY (QTY 4, LOC 10800B41) and enter 4 for QTY_REQ. Then go to […]

Sequentially update a column with a decrementing value - I want to update the "QTY_REQ" column in the PLSQL table (in the screen cap attached) sequentially row by row by dividing out a number (e.g. 7) among the rows. I want to start with the row with the highest value in QTY (QTY 4, LOC 10800B41) and enter 4 for QTY_REQ. Then go to […]

SQL Server 2022 - Development

SQL Server Developer Edition on Windows VM - I'm currently facing a peculiar issue with SQL Server Developer Edition on my Windows VM, and I'm seeking your expertise to help me troubleshoot this problem. Has anyone faced this issue?

Can you reference 'Inserted' or 'Deleted' Trigger Table in SQL Mail Query - Hello, I am trying to reference the results from the Inserted or Deleted tables from a SQL Trigger in teh @query param of sp_sendmail but doesn't seem to work. Getting the dreaded: Msg 22050, Level 16, State 1, Line 54 Failed to initialize sqlcmd library with error number -2147467259. This usually happens when you are […]

Primary key column question - Hi, I'm new to sql server and successfully created a table of columns, however after creating the table I realised I forgot to add the primary key column. I used the ALTER command for and used a query to create a new column for the primary key. This was successful but it added the column […]

Newby - Stuck with a trigger. - Hello Forum, Apologies for this post, I'm stuck with an update trigger that i need to amend. I need to capture the Date, OrderID and CustID, Surname and Firstname from a trigger which prevents an update on table. The Date, OrderID and CustID , Surname and Firstname need to go into a table. I can't […]

Check Code Correction Current Period Prior Period Movement - Please assist me?I am trying to calculate Current Period Prior Period Movement from my data, the date columns are: Please check my code for any errors, code runs, i just want to make sure it does what it's supposed to do. Small data set is attached. -- Use IF EXISTS to check for the […]

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.