Use Logic Apps To Save Money In Azure: Data Engineering in Fabric (2024-10-16)

SQLServerCentral Newsletter for October 16, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Use Logic Apps To Save Money In Azure: Data Engineering in Fabric]( - [AWS Services Using SQL for Big Data Analysis]( - [From the SQL Server Central Blogs - The Only Valid Test of a Backup is a Restore]( - [From the SQL Server Central Blogs - Key mappings in Visual Studio Code]( - [The Definitive Guide to DAX: Business Intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel]( Question of the Day - [Crazy Code I]( The Voice of the DBA  The Modern Algorithm of Chance These days algorithms rule much of the world. From how supply chains are managed to how vehicles run their engines to the media that many of us watch on the various streaming services. I assume that most of you know that algorithms drive what you see on social media, on YouTube, and even the search results you get, and what you see might be different than what I see. There is a constant search for a perfect, or at least, very targeted way of getting you what you want. Or at least what the algorithm thinks you want. However, is that the best way for algorithms to be designed? It is for the companies that want to profit from your attention, but is this intense personalization better for us? There is [an interesting article on music discovery]( focusing on Spotify, since they are one of the largest streaming services. The article talks about the algorithm and how it tries to match selections to our tastes, basically a complex data analysis of our choices along with metadata that's been created around data that's hard to classify. There are attributes assigned to songs, but are these the attributes that make sense? That's a topic for another day. The result of this is that Spotify tends to recommend more of what we already listen to, which has also driven artists to change how they produce songs since the algorithm matters. This seems like a similar challenge to what I've seen with the written word. A long time ago many of us consumed the words (with less choice) in newspapers, books, and other physical media. However, we often ran into random things that were different because of our physical paths in life. We might encounter books in a shop or library and be attracted to a cover for some random reason. We might pick up an unexpected work lying adjacent to one in which we were interested and discover something new. The way we look at books, or anything, changes when we browse and randomly wander the world. These days, we have less of that, with algorithms in electronic systems that guide us further on a path we're walking, not allowing for chance encounters, or even wildly different thoughts because we stumbled on something. Even in our social media, this doesn't often happen. I'd hope that we might encounter a recommendation from another we wouldn't otherwise see, but the promotion of certain feeds and the glut of viral re-sharing often ensures that we don't see many random things. Instead, most of us see the same thing that many others do. Those of us who have studied computer science know random things are hard to create in computer systems. Building algorithms that embrace randomness isn't something many of us focus on, instead trying for matches that reinforce or duplicate something our clients already want/use/see/etc. That has helped create many businesses in the digital world, but I'm not sure that those businesses are always good for the world. I don't have a good solution for random chance, other than talking with others, especially those who live different lives from you, and embracing the way they view the world. Hopefully that leads to a book, movie, or other chance encounter that you might not otherwise have. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Use Logic Apps To Save Money In Azure: Data Engineering in Fabric]( John Miner from SQLServerCentral This next article looks at how a Logic App in Azure can start and stop various database services and save money. [SQLServerCentral Article]( [AWS Services Using SQL for Big Data Analysis]( Additional Articles from SimpleTalk AWS offers a robust suite of tools designed to manage big data effectively. This article explores focusses on how SQL is leveraged within Amazon Redshift and Amazon Athena. We will delve into the technical features, practical applications, and step-by-step examples for using these services, highlighting how they can transform raw data into actionable insights for your business. [Blog Post]( From the SQL Server Central Blogs - [The Only Valid Test of a Backup is a Restore]( Grant Fritchey from The Scary DBA Please, let me reiterate: The only valid test of a backup is a restore. THE ONLY VALID TEST OF A BACKUP IS A RESTORE. I’m happy that you have... [Blog Post]( From the SQL Server Central Blogs - [Key mappings in Visual Studio Code]( dbafromthecold@gmail.com from The DBA Who Came In From The Cold At a recent conference I was asked how I executed code during my presentation. Running code during a presentation should be as smooth as possible…so in VS Code I... [Definitive Guide to DAX cover]( [The Definitive Guide to DAX: Business Intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel]( Site Owners from SQLServerCentral Now expanded and updated with modern best practices, this is the most complete guide to Microsoft’s DAX language for business intelligence, data modeling, and analytics. Expert Microsoft BI consultants Marco Russo and Alberto Ferrari help you master everything from table functions through advanced code and model optimization.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Crazy Code I I have this data in my Customer table: CustomerID CustomerName 1 Steve 2 Andy 3 Brian 4 Allen 5 Devin I run this code: SELECT t.CustomerID , c.value FROM ( SELECT CustomerID , STRING_AGG (CustomerName, ',') AS me FROM customer GROUP BY CustomerID) t CROSS APPLY STRING_SPLIT(me, ',') c; What is returned? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Alessandro Mortola) STRING_AGG's behavior Executing the following script (Sql Server 2022), you get the table t0 with 10 rows: CREATE TABLE t0 ( id INT PRIMARY KEY , field1 VARCHAR(1000) , field2 VARCHAR(MAX)); INSERT INTO t0 SELECT gs.value , REPLICATE ('X', 1000) , REPLICATE ('Y', 1000) FROM generate_series(1, 10, 1) gs; GO What happens if you execute the following statements? - select STRING_AGG(field1, ';') within group (order by id)  from t0; - select STRING_AGG(field2, ';') within group (order by id)  from t0; Answer: The query (1) gives the error message "9829 STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation". The query (2) ends successfully Explanation: According to the [official documentation]( the data type returned by the STRING_AGG function, depends on first argument (expression). - NVARCHAR(MAX) results to NVARCHAR(MAX) - VARCHAR(MAX) results to VARCHAR(MAX) - NVARCHAR(1...4000) results to NVARCHAR(4000) - VARCHAR(1...8000) results to VARCHAR(8000) - Numbers and Date/Time related data types result to NVARCHAR(4000) For this reason the first statement returns a varchar(8000) and it gives the error because the size of the resulting string is greater than 8000 characters. The second statement aggregates data from a varchar(max) field, therefore there is no problem with the size of the result.   [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 [Linked server and trouble between manager and agent]( - Hello, I have a problem and i don´t understand it. After to link a server, i execute delete top(1) from LinkedServer.MyDB.dbo.MyTable From mananger works fine but from a agent's job fails with error 18452 "The login failed. The login is from an untrusted domain and cannot be used with Integrated Authentication." I don't know why […] SQL Server 2019 - Administration [Changetracking disabled in database snapshot, new bug in ms sql 2019 ?]( - Hi, We are using database snapshot together with changetracking to keep a datawarehouse up to date. The database snapshot is used to have transactional consistency for the duration of the warehouse update, without having any locking issues. CREATE DATABASE [DB_SS] ON (NAME = 'DB', FILENAME = 'DBFile') AS SNAPSHOT OF [DB] The changetracking functionality is […] [Impact of adding the opening of the Master Key in a SQL job for SSIS packages]( - Bonjour à tous, J'ai un problème d'exécution de mes packages SSIS. A chaque opération de basculement, les packages ne s'exécutent pas correctement et je suis obligé d'ouvrir manuellement la Master Key à chaque fois. La base de données SSISDB est en disponibilité active permanente (AG). J'ai vérifié les connexions et les configurations, mais je ne […] [Switch from MSSQL SVC to MSA - Unable to start services]( - Using MSSQL 2019 standard edition It was installed using SVC Account. But now I want to switch from SVC to Managed Service Account. When I granted folder level permissions for NT SERVICE\MSSQL$SQLSERVER and NT SERVICE\SQLAgent, and tried to switch, it is throwing below error after starting agent. it shows like Services are started aling with […] SQL Server 2019 - Development [Need to group the same valued ID fields to run an aggregate average over them]( - I have data that contains poll results, with the poll_id consistuting a single row of data for each candidate in said poll, so a poll_id will have a minimum of two different rows (not my design, parsed from ABC polling results). Sometimes there are more than 2 rows for each distinct poll_id as the survey […] [SSIS Question]( - Hi everyone. I am re-writing a portion of my SSIS logic. My SSIS package downloads daily stock market data and then performs calculations for each trading day. Once a particular day's calculations have been done and stored there is no need to re-do this work when new data is added. Only new data should be […] SQL Azure - Development [Azure SQL DB CLR]( - Hi, Azure SQL DB doesn't support CLR, if we have an application with CLR and assemblies, is there a workaround for it? SQL Managed Instance is an expensive option and Migrating to VMs will have a lot of management overhead. I would like to know if anyone has managed to migrate to SQL DB without […] [ETL data load DML SP slowness suggestion]( - Hi All, We are using Microsoft SQL Azure SQL 2022 (12.0.2000.8). Some of our ETL SPs are running very longer more than 30 minutes. The reason is it is doing a lot of DML - almost 15 (Sometime 100) million Insert, update and delete. Table size is (196113141 rows and size 120 805 792 KB) […] SSDT [SSIS package Excel source hanging in validation or prepare for execute phase]( - Hi everyone, For several weeks now we have issues with an SSIS package connected to an Azure SQL server which sometimes (at least every few days) hangs in the Validation phase of an Excel source task which reads an Excel file from a shared network drive, or lately, also failed with error in the Prepare […] Integration Services [An error occurred while skipping data rows]( - I am trying to build an ETL for an Existing Table. The ETL (SSIS) Takes data from Excel, Convert it into CSV. The Data in Excel Source is: INSERT INTO YourTableName ( [Fiscal year/period], [Company Code], [Contra Company Code], [Account], [Level 1], [Level 2], [Level 3], [Level 4], [Generic Material], [Metric], [Unit], [Value] ) VALUES […] SQL Server 2022 - Administration [Steps to restore database which is part of Trans replication and Allwayson db]( - We have a Setup where replication and AG is configured as below. Servers:Node1,Node2 ==>Node1 Instances: SQLNode1/distributor, SQLnode1/Pub, ==>Node2 Instances: SQLNode2/distributor, SQLnode2/Pub, --> Distributor database is configured as remote server and configured in AG between SQLNode1/distributor and SQLNode2/distributor -->Both Publisher(DB-A) and Subscriber(DB-A-reporting) database are in same instance SQLnode1/Pub and SQLnode2/Pub which are on configured on Same […] [What is causing this error?]( - (This is probably the wrong forum to be posting this question, so please forgive me. I did try to figure out the correct one, but none of the other forums looked right to me.) Every time I get into SSMS 20.2 I get this error: Does anyone have any idea what's wrong and how I […] SQL Server 2022 - Development [case statement with and like operators]( - Hello, I'm trying to remove results from this sql statement where the custid like '%abc%' and company = 'abc', but think I'm getting the syntax wrong. Not including the entire thing, hopefully it's enough. the case statement begins at t2.custid. Please help if you can, thank you! t1.Company, t2.CustID (CASE WHEN custid like '%abc%' and […] [update error. Subquery returned more than 1 value]( - Hi friends. how can i fix that? ;with cte as (select distinct(e.Id ) from SecurityKmsAbfa..EnterprisePositions e join SecurityKmsAbfa..Import_UsersTeh i on e.Name=i.vahedeSazmani join NewKmsAbfa..Members m on m.SSN=i.kodeMelli ) update SecurityKmsAbfa..users set EnterprisePositionId = (select id from cte ) from SecurityKmsAbfa..Import_UsersTeh i join NewKmsAbfa..Members m on m.SSN=i.kodeMelli join SecurityKmsAbfa..users u on u.Id=m.id where u.EnterprisePositionId is null [THE CSV PROBLEM]( - Hello everyone, First of all, thank you very much for this great community. I hope you can help me. I have been struggling for days with importing CSV files. The background is that I am currently doing an SQL course and would like to work with the CSV files provided. I have already tried all […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -