Data Lineage Scripts for Microsoft SQL Server and Azure SQL (2022-10-19)

SQLServerCentral Newsletter for October 19, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Data Lineage Scripts for Microsoft SQL Server and Azure SQL]( - [The Basics of Inserting Data into a SQL Server Table]( - [Join the Redgate pre-con at PASS Data Community Summit]( - [From the SQL Server Central Blogs - Overcoming a storage failover issue with SQL Server on Kubernetes using Portworx]( - [From the SQL Server Central Blogs - What you can do is based on more than your direct permissions.]( Question of the Day - [Committing the Work]( The Voice of the DBA  Daily Coping Tip Take time to reflect on what you have accomplished recently 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 Work of Work I saw a [short post on meetings](, which made me chuckle. The post said that meetings are work, and sometimes they get in the way of other work. I think that's a sentiment that many people have, especially when they are pressured to write code or manage systems and their daily calendar gets cluttered with meetings. I find meetings to be very hit-and-miss for my job. Some are valuable and helpful, with plenty of others being unnecessary and an impediment to getting things done. It is hard to know when each of these might occur, but often I find status meetings to be more often not worth the time required to attend. They very often are not worth the amount of attention they require, which has me preferring I could just update a status somewhere rather than listening to others and presenting my own report. That being said, meetings aren't likely to completely disappear for any of us. Whether manager, developer, administrator, or anything else, it seems that getting together is still a regular past of working in an organization. While I do think the remote meetings result in more targeted meetings, focused on a particular topic, they also seem to be more jarring to me. The context switch is jarring, going from some piece of work to immediately being around other people. I find myself missing the pace of walking to and from a room and chatting with others. It's a good way to get prepared and decompress afterwards. Too many of us, do a poor job of accounting for the impact meetings have on the work we need to get done. Whether we are estimating our own workload, or trying to manage others and plan for what they will accomplish in a week, we struggle to consider the work of meetings Both the time spent in meetings, as well as the time preparing for a meeting, or the need to think about the result and action items from a meeting. That's not to mention the impact of context switching on our concentration. The idea of bunching of meetings to a few days or times during the week might be most helpful for me. I like the flexibility to move my creative work around as much as possible. There are times I can code or write, and when those times occur, I want to spend a lot of time in that state of mind. Interruptions are a killer to productivity. I find myself sometimes blocking off sections of days just to prevent meetings from being scheduled. If possible, I prefer a number of back to back meetings on one day and nothing on others. Meetings are a part of work, but they also are work. You can love or hate them, but finding ways to cope with the impact of meetings is helpful to becoming a more productive worker. It's also important to ensure your management recognizes the impact of meetings and tries to minimize that impact on any other deliverables on which you are working. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Data Lineage Scripts for Microsoft SQL Server and Azure SQL]( tomaz.kastrun from SQLServerCentral Learn how you can easily analyze the data lineage in your SQL Server database. [External Article]( [The Basics of Inserting Data into a SQL Server Table]( Additional Articles from SimpleTalk Before data can be read from of a SQL Server database table, the table needs to contain rows of data. One of the most typical ways to get data into a table is to use the INSERT statement. One row or multiple rows can be inserted with a single execution of an INSERT statement. You can even use the output of a stored procedure to insert rows. In this article, I will explore the basics of inserting data into a SQL Server table using the INSERT statement. [External Article]( [Join the Redgate pre-con at PASS Data Community Summit]( Additional Articles from Redgate Take on the challenge of deploying database changes every 30 minutes in Redgate's Summit pre-con 'The Journey from Manual Deployments to Database DevOps' on November 15. Hosted by Steve Jones, Grant Fritchey and Redgate DevOps experts, you'll start with a manual process and slowly implement changes that allow you to reap the benefits of automation. [Blog Post]( From the SQL Server Central Blogs - [Overcoming a storage failover issue with SQL Server on Kubernetes using Portworx]( dbafromthecold@gmail.com from The DBA Who Came In From The Cold In a previous post I talked about an issue with storage failover when a Kubernetes node goes offline. In a nutshell, the issue is that the attachdetach-controller in Kubernetes... [Blog Post]( From the SQL Server Central Blogs - [What you can do is based on more than your direct permissions.]( Kenneth.Fisher from SQLStudies What you have access to is not just what you have direct permissions to. The other day I needed to ... Continue reading   Question of the Day Today's question (by Steve Jones - SSC Editor):  Committing the Work I need to reverse two values in two different rows, and I want to ensure both changes complete. I run this code as the first code executed after connecting to the instance: BEGIN TRANSACTION UPDATE dbo.ArticleSeries SET BannerImageFileID = 234 WHERE ContentItemID = 10 UPDATE dbo.ArticleSeries SET BannerImageFileID = 235 WHERE ContentItemID = 11 COMMIT WORK 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) Autocommit Transactions If I have a transaction that commits immediately after the execution of the T-SQL DML statement, which type of transaction is this? Answer: Implicit transaction Explanation: An implicit transaction is one where the transaction starts and is committed by the statement, DML or DDL. In SQL Server, this is the default for most statements. An INSERT creates an implicit transaction by itself, by default. Ref: Transactions - [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 [Cons vs Pros of using SSISDB deployment vs File Sytem deployment of SSIS pkgs?]( - If we are using File System Deployment of SSIS packages pretty much accross the enterprise, and examining pros and cons of possibly switching to SSISDB catalog type of deployment  (SQL Server 2016 and 2017, Visual Studio 2015 (and other versions)). What should we be strongly aware of before engaging into such change? There got to […] [The subscription to publication 'xxxxxx' has expired or does not exist, how to r]( - Replication-Replication Distribution Subsystem: agent (null) failed. The subscription to publication 'xxxxxx' has expired or does not exist, how to remove it? [Uninitialized subscription]( - how to fix Uninitialized subscription in replication monitor? SQL Server 2016 - Administration [Azure Server VM connection to OnPrem SQL]( - Hi i have a Azure VM that cannot connect to my onprem sql2016 server via SQL connection string i have tested the following from my Azure VM to my onprem SQL RDP - Works SQL MGMT Studio - Works Ping - Works  SQL Server 2016 - Development and T-SQL [Help Modify Query to Strip Out Field Values]( - Hello Community, The following query will remove values after the comma in field category, and rename the field to updatedfiels with the values shown in the image below: This is fine. However, the query can't handle values without a comma. In the sample data below the value Finance is a single value, but when I […] [Query with a twist having pivot requirement]( - Hello friends, i am trying to get one type of data which has some weird mappings and pivot requirement as well. Here is the sql to create sample data: create table #temp_module (module_id int, module_name varchar(50), metrics int, createdate datetime) insert into #temp_module select 1, 'account', 2, getdate() union all select 2, 'inventory', 5, getdate() […] [Ranking functions]( - I need a second set of eyes to help with my lunacy/stupidity/senility. I've got a set of interview questions, the categories of the questions, and the interview guide that "packages" them together. I want each question category within an interview guide to be numbered, and each question within each category to be numbered. Like this: […] SQL 2012 - General [Where did all of the SQL Server DBAs go?]( - For 15 years, I was a Microsoft SQL Server DBA, Developer, etc with all of the accompanying Microsoft certifications (up to SQL Server 2012). Had to take a sabbatical for family reasons beginning in 2014. Now that I am attempting to re-enter job market, I have learned that ALL of my Microsoft certifications have gone […] SQL Server 2019 - Administration [Uninitialized subscription]( - Uninitialized subscription showing in my replication monitor,how can I fix it? SQL Server 2019 - Development [Getting MAX and MIN values for all values in a field]( - I have such a situation. I am trying to get MIN and MAX values of one field for all rows. MIN (WEEK_P) should be always one value 20220917 for all fields all the time. Now the Window Function doesn't work for some reason. Is there anything I am doing wrong?  SELECT FNL_DC AS 'DC', […] [value @@trancount in stored procedure]( - Hello everyone In my code, I often use nested stored procedures. The processing of my stored procedures all perform several operations in the database, so they are encapsulated in a transaction. My stored procedures children can also be performed as a parent. I therefore start all my procedures stored as a child by a BEGIN […] [Reset varchar length to 19 from 4000 in a concatenated date]( - I import a table from a comma delimited file that has a separate date column and a separate time column (among other columns.) I used to import the data into an Excel spreadsheet and manipulate it there and append the data to an existing table, but now I want to do it in Sql Server […] Azure Data Factory [Data factory question]( - Upon my adventures of Azure data factory and all the wonders it can do I have stumbled upon a issue that Microsoft support has no answer as of yet. Long story short, I am trying to ingest data from a storage blob in another tenant. That customer will only allow us to connect to that […] Reporting Services [Exceptionally slow response times for any SQL reports - SQL2012]( - Dear all - I've been advised by a colleague that their system has been upgraded from Oracle 12 to Oracle 19. Any SQL reports are connected via Oracle ODBC version 19 to the back end Oracle DB. Since the upgrade any SQL reports take a long time to run & eventually display a timeout message, […] Integration Services [SSISDB - where to find the dtsx XML text]( - I am trying to find out where the DTSX packages are stored in the SSISDB. I found numerous articles on how to pull that information when the packages are deployed to the msdb database but really having a hard time finding anything on the newer deployment style for the SSISDB. Any help getting in the […]   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -