Using Pipeline Return Value in Azure Data Factory and Azure Synapse Analytics (2023-11-01)

SQLServerCentral Newsletter for November 1, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [SQL Server Central Is Upgrading Our Servers]( - [Using Pipeline Return Value in Azure Data Factory and Azure Synapse Analytics]( - [Why Database Discussions Have Moved From the Backroom to the Boardroom]( - [Normalize strings to optimize space and searches]( - [From the SQL Server Central Blogs - Using the Pure Storage PowerShellSDK2 - Part 3 - Getting Performance Data from FlashArray]( - [From the SQL Server Central Blogs - SQL Server Backups are Not a High-Availability Solution]( - [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( Question of the Day - [Multiple Encryptions]( The Voice of the DBA  Not Useless Features Today's editorial was originally published on Dec 1, 2017. It is being republished as Steve is out of town on the [Database DevOps Rocks Tour](. Some time ago I noticed Adam Machanic had written a post titled [The SQL Hall of Shame](. In it, Adam notes there are features that Microsoft will introduce in SQL Server and then let die. These are the useless features that aren't widely used, or even useful for many projects. You can read his list, and agree or disagree. I think there certainly are some features that were silly experiments and I wish they had been introduced as experimental, or beta, features early, allowing users to give feedback on whether they're useful and where they need refinement. I certainly think too many resources were initially wasted on items like MDW and Query Notifications, and far too little investment later. These days I think we'll see more early releases of items in Azure where users can experiment and provide feedback to help Microsoft decide if more investment is needed. At least, that's my hope. Today I'm wondering if you think there are features in SQL Server that do need more attention. Those features you use, but appear forgotten and you'd hope they are improved in the next version. For me, I certainly wish replication would get more attention, tooling, and resilience. I'm not sure if this needs an overhaul, like SQL Server 2005 was, or there just needs to be some reworking of the code, but in today's distributed world, we need a more reliable way to hook up portions of databases and move data around. Not ETL, not Availabilty Groups, a better replication architecture and implementation. There might be other features you use or want, but let me know today. What's a not useless feature that you want improved. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [SQL Server Central Is Upgrading Our Servers]( Steve Jones - SSC Editor from SQLServerCentral The SQL Server Central database servers are being upgraded on Thursday, Nov 2. [SQLServerCentral Article]( [Using Pipeline Return Value in Azure Data Factory and Azure Synapse Analytics]( arindamxs from SQLServerCentral Learn how you can return data from one ADF pipeline for use in another. [Technical Article]( [Why Database Discussions Have Moved From the Backroom to the Boardroom]( Additional Articles from SQLServerCentral There are good reasons why business leaders should care about their databases—and hidden costs for neglecting them. Our CMO, Kate Duggan, wrote about the risks and potential costs in this article on Bloomberg. [External Article]( [Normalize strings to optimize space and searches]( Additional Articles from SimpleTalk While this article is specifically geared to SQL Server, the concepts apply to any relational database platform. The Stack Exchange network logs a lot of web traffic – even compressed, we average well over a terabyte per month. [Blog Post]( From the SQL Server Central Blogs - [Using the Pure Storage PowerShellSDK2 - Part 3 - Getting Performance Data from FlashArray]( aen from Anthony Nocentino Blog Welcome back to the second installment of our blog series on using the Pure Storage PowerShell SDK2. In this post, we’ll learn how to retrieve performance data from FlashArray... [Blog Post]( From the SQL Server Central Blogs - [SQL Server Backups are Not a High-Availability Solution]( Kevin3NF from Dallas DBAs Please continue doing your backups! Backups are Disaster Recovery, yes…but not HA. Some will argue with this (in the comments most likely), but I broadly define “High Availability” as... [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( [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):  Multiple Encryptions I want to create a symmetric key with this command: CREATE SYMMETRIC KEY PIISymKey10 WITH ALGORITHM = AES_192 ENCRYPTION BY PASSWORD = 'MyS3cr#tP@ssword' , ASYMMETRIC KEY HRProtection , CERTIFICATE MySalaryCert , ASYMMETRIC KEY HRProtection2 , PASSWORD = 'AnotherS$trinSD'; What happens when I run this? Assume that the asymmetric key and certificate exist in the database. 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) Parsing Strings What is returned by this code? SELECT PARSENAME('a.b.c.d.e', 2) Answer: NULL Explanation: This returns NULL as Parsename returns null if the string is not a valid SQL Server identifier. In this case, an identifier is a string with no more than 4 parts separated by periods. This one has 5 items and 4 periods. Ref: PARSENAME - [ [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 [Execute AS and Impersonate]( - I am trying to write up a stored procedure to allow users to execute jobs in MSDB that are owned by another user. The job-owning user is a service account (set up as non-interactive) has elevated permissions which I do not want the business users to have themselves. The problem is that I can't seem […] [Troubleshooting a SQL Server Wait Resource Issue"]( - I'm encountering some wait resource entries in sysprocesses without specific details. Has anyone faced a similar situation? Any insights on how to decode or address this issue would be greatly appreciated. [help decoding a wait resource from sysprocesses]( - Hi After some help . I've got a bunch of wait resources from sysprocesses that don't include specific resource type i.e its not prefixed with KEY, or TAB etc This type of format 66:1:6666666 So I presume the first numbers are the databaseid, maybe the second number is file id , but I cant decode […] Administration - SQL Server 2014 [How to include newly system generated tables to SQL replication?]( - Hi there, I have this situation that I have been trying to get around for some time but I haven't been able to despite some considerable investigation. I have transactional replication between 2 servers, the standard run of the mill set up. I noticed after some time, the replication goes out of sync (or breaks) […] SQL 2012 - General [Can I still get a SQL 2012 eval for my home lab?]( - Just for testing upgrades.  All the links are dead.  I understand it's not in support but like to test the full upgrade properly. SQL Server 2019 - Administration [SSIS - "Access to the path '\\XXX\data2\TXX\zz\Tracker - xx.xlsx' is denied.".]( - Hi All, SSIS error: Recently the following error "Access to the path '\\XXX\data2\TXX\zz\Tracker - xx.xlsx' is denied.". i occurring. The SSIS package is executed through SQL Agent using a Domain service account and running as a job for years No changes to SQL Server no changes to the fileserver permissions etc. 1st thing checked is […] [Replica stuck in resolving state during upgrade]( - Hi, Last week we tried to perform an upgrade on a 2 node sqlserver 2012 availability group. OS is windows server 2019. Cluster functional level is set to 9. We set secondary node to manual failover, assync commit, etc.. the secondary node was updated ok. On the primary, still sqlserver 2012, all was also good […] [SQL Service is not starting after upgrade...]( - Seems to be Windows is somehow decided to upgrade something in SQL Server and after that it can't start. SQL Service is starting correctly only with trace flag 902. How to fix the issue? Script level upgrade for database 'master' failed because upgrade step 'ISServer_upgrade.sql' encountered error 15151, state 1, severity 16. This is a […] [Can trigger execution be excluded only for a particular login?]( - Hi, I have an ERP application which connects to SQL 2019 database by an user - "vwuser". Whenever the users does transactions via ERP , data population happens. Very few people have backend access, but auditors are asking for tracking the data modifications that happen via Backend (say data corrections via Update / Delete). The […] Reporting Services [Dynamic Subscription query with parameters????]( - Hi,  I'm trying to create a query within a dynamic subscription with a parameter for a Start and End Date. The query will not validate when I try to use @StartDate and @EndDate Something like: WILL VALIDATE --- Select ID from mytable where date between '10/1/23' and '10/31/23' Once I change the date to […] Third Party Products [Is SQL Search 3 still supported?]( - I'm looking through my machine to see what I have installed and what I can remove. I found a product called SQL Search 3, which is by Red-Gate. Is it still supported? Article Discussions by Author [Handling a Cancelled Online Index Rebuild in SQL Server]( - I was wondering what the expected behavior is when an online index rebuild (non-resumable) is cancelled in SQL Server? Are there any potential risks or considerations to keep in mind? Through Mcdvoice homepage, customer or participant can give his honest opinion (positive or negative feedback) to improve product quality, service, store quality & norms. […] SQL Server 2022 - Administration [time for stats to get updated when auto update statistics async = true]( - hi, I'm using the following database version of MS SQL SERVER Microsoft SQL Server 2022 (RTM-GDR) (KB5029379) - 16.0.1105.1 (X64) I was experimenting with the Auto Update Statistics Async = TRUE in my test database and noticed that the time it took for my table column stats to get updated took anywhere from immediate to […] [stats get updated even when query is trivial]( - hi, I'm using the following version of SQL Server Microsoft SQL Server 2022 (RTM-GDR) (KB5029379) - 16.0.1105.1 (X64) In my database, I've got the following options set to TRUE Auto Update Statistics Auto Update Statistics Asynchronously I've got the following script create table testtab (col1 int) DECLARE @count INT; SET @count = 1; WHILE @count<= […] [SQL Server - Kerberos Configuration Manager - Unable to connect]( - am currently working through replacing a development and production SQL server, moving from server 2012 to server 2022. These two servers have the SQL Service running as an AD account. Thus, I have gone through and configured the proper SPNs in Active Directory and configured Kerberos Constrained Delegation. Adding the SPN for the FQDN and […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -