Stairway to Biml Level 8 – Using the Relational Database Metadata to Build Packages (2020-12-16)

 SQLServerCentral Newsletter for December 16, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to Biml Level 8 – Using the Relational Database Metadata to Build Packages]( - [Deploy Database Objects to Multiple SQL Server Instances Using PowerShell]( - [Effective Data Governance: Being Grown Up About Data]( - [Free eBook: Fundamentals of SQL Server 2012 Replication]( - [From the SQL Server Central Blogs - Migrating from SQL Server to Amazon AWS Aurora]( - [From the SQL Server Central Blogs - Provisioning storage for Azure SQL Edge running on a Raspberry Pi Kubernetes cluster]( Question of the Day - [The Classifier]( The Voice of the DBA  Daily Coping Tip Listen wholeheartedly to others without judging them 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. Your User Group Goals Many of you reading this piece have likely been to a user group at some point in your career. Some may go every month, some may go a once or twice annually , and some may not have been for years. I also know some of you don't have a group in your area, or perhaps not enough people for a SQL Server group, but I would bet if you are working in technology, there are enough people to have some sort of technology group, perhaps even an online [book club](. Recently I was speaking with Andy Warren about [his group in Orlando](. He's preparing to turn over leadership of the group to others, and [he's actually looking for comments]( on the docs he's put together. I'd urge you to take a moment and think about your group and how you want it structured and check out what Andy has done. He'd appreciate comments on his effort, whether you are in leadership or just show up at meetings. Our discussion, however, was on goals. What goals are appropriate for the group, and what guidelines should he leave for others. He's tended towards goals of "more", meaning more people touched, more attendance at meetings, more events. I've tended to aim for more ensuring you keep people interesting, inspired, and with some engagement, but not worried about size. Whether you are in the leadership of a group, just an attendees, or wish you had a more interesting group of technical people to meet with regularly, what do you wish your group would accomplish next year? Are there things that matter to you for an organization outside of work that might help your career? Take a minute to think and leave me a comment today. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to Biml]( [Stairway to Biml Level 8 – Using the Relational Database Metadata to Build Packages]( Andy Leonard from SQLServerCentral.com In this next level of the Stairway to Biml, we will examine how you can use the information stored in your RDBMS to build packages. [Deploy Database Objects to Multiple SQL Server Instances Using PowerShell]( Additional Articles from MSSQLTips.com In this article we look at a PowerShell script that can help you deploy database objects to multiple SQL Server instances. [Effective Data Governance: Being Grown Up About Data]( Additional Articles from Redgate William Brewer explains how to make data governance a continuous organizational activity, based on well-established standards and practices, rather than a knee-jerk response, and which skills and tools will help you achieve compliance, including SQL Data Catalog for discovery and classification of data held in SQL Server. [Free eBook: Fundamentals of SQL Server 2012 Replication]( Press Release from Redgate Fundamentals of SQL Server 2012 Replication provides a hands-on introduction to SQL Server replication. The book introduces you to the technologies that make up replication, and then walks you through setting up different replication scenarios. When you've finished reading, you should be able to implement your own multi-server replication setup while following the principle of least privilege. From the SQL Server Central Blogs - [Migrating from SQL Server to Amazon AWS Aurora]( Daniel Janik from Confessions of a Microsoft Addict Is Microsoft’s licensing scheme getting you down? It’s 2020 and there are now plenty of data platforms that are good for running your enterprise data workloads. Amazon’s Aurora PaaS... From the SQL Server Central Blogs - [Provisioning storage for Azure SQL Edge running on a Raspberry Pi Kubernetes cluster]( dbafromthecold@gmail.com from The DBA Who Came In From The Cold In a previous post we went through how to setup a Kubernetes cluster on Raspberry Pis and then deploy Azure SQL Edge to it. In this post I want...   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Classifier What does the classifier() function do in R? 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) A Minor Problem Don't run this code, but there is a problem in it. There is a dbo.Day2 table with a datavalue column and no other tables in the database. What is the problem? WITH cteData (lowerbound, upperbound, letter, pwd) AS ( SELECT SUBSTRING(datavalue, 1, CHARINDEX('-', datavalue) - 1) as lowerbound , SUBSTRING(datavalue, CHARINDEX('-', datavalue) + 1, CHARINDEX(' ', datavalue) - CHARINDEX('-', datavalue) - 1) as upperbound , SUBSTRING(datavalue, CHARINDEX(' ', datavalue) + 1, 1) , SUBSTRING(datavalue, CHARINDEX(':', datavalue) + 2, 50) FROM dbo.Day2 AS d) , cteSolution (occ, valid) AS ( SELECT LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) AS occ , CASE WHEN LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) >= d.lowerbound AND LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) <= d.upperbound THEN 1 ELSE 0 END AS valid FROM dbo.cteData AS d) SELECT COUNT(*) FROM cteSolution WHERE valid = 1; GO Answer: The second CTE has a syntax error Explanation: The problem with this code is in the second CTE. The FROM clause lists the cte as dbo.cteData. There is no schema qualification on the CTE. Ref: CTE - [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 [Chance data Capture with AG]( - Since CDC uses log reader to read the changes from the Log. If the SQL server is configured for AOAG group, I was thinking to enable the CDC on secondary replica instead of primary replica. Would you see any issues with this setup? I am thinking Change data capture are not supported on secondary databases that […] [TDE- Transparent Data Encryption related question]( - Hi Experts, Looking for suggestions on TDE encryption. We have a 3TB database in UAT env. Its in 2-node(node-A, node-B) Alwayson AG. SQL Server version is SQL 2017 EE CU22. We turned on TDE encryption node-A. It took more than 12 hours and completed of about 40% encryption. Meanwhile, unexpected failover occured. we brought it […] SQL Server 2017 - Development [Extract data with variable column names and order]( - Hi guys, Just looking for any good suggestions on how to tackle this problem. I have a table of attributes which are held in very wide sparsely populated table (at least it's not EAV!). For display purposes in the application there is a separate table that defines which columns are being used, their display order […] SQL Server 2016 - Development and T-SQL [Help Transforming TSQL to MSSQL]( - Hello, I am not great with SQL and the statement I wrote is in TSQL and it is working. I would like this same statement to run in MSSQL, can you please restate it correctly or put me on the right track? It is the With statement that is killing me! I think it needs […] [Enabling FileStream in AG participated Database]( - Hi team, I was asked to enable FileStream for one of the existing varbinary(max) column in existing table. It is already participating in Avalability Group where we have 1 primary & 1 secondary server. Now as part of the change, i am doing the below Enable FileStream at instance level by enabling it via SQL […] SQL Server 2019 - Administration [Perf hit for Increasing tran log frequency]( - we are currently doing a 1 hour tran log backup for a system, I'd like to push that up to 1 minute. Is there a performance hit to be concerned about with doing this? I've gotten some pushback regarding performance and doing tran log backups this frequent. My view is that each hit will be […] [tlog back up and normal restore back migration]( - Hi Expert,  why there is less down time in tail log backup as compared to the normal backup and restore in migration  Shree [SQL Server Agent Failing After Windows Shutdown]( - Hi All~ Where I work, we have Azure SQL Server that we are connecting to through SSMS. I have created several SQL Server Agent Jobs, using my own login as a Proxy. These jobs run a VBS Script (using a command line) that uploads data into our db, creates reports, and moves and manipulates files […] [Database Mail - Times out]( - Hello All, I have a lovely program called "TreeSize" on my SQL Server that can send an email out via SMTP just fine. I decided to try setting up Database Mail so I can get email alerts when Backups complete, etc. I am following these steps: Again, my TreeSize Application can send mail no […] SQL Server 2019 - Development [SQL Spatial - find all locations within X distance of a point.]( - I have a really stupid question, and I'm betting the answer is "Well, what happened when you actually ran the query?", but I'll ask anyway. (Maybe I just need a bigger spatial dataset). Say I have a center point P that's my location. I can do all the "find the distance" stuff just fine using […] [nested while]( - CREATE Table TABLEA(N varchar(50),Q int,R int) ALTER PROCEDURE [dbo].[SP](@N varchar(100),@Q int=0,@R int=0) AS BEGIN DECLARE @I int = 1 BEGIN WHILE (@I <= @R) BEGIN WHILE (@I <= @Q) BEGIN INSERT INTO [dbo].[TABLEA] SELECT @N,@Q,@R SET @Q = @Q - 1 END SET @R = @R -1 END END END exec [dbo].[SP] 'NameA',10,2 Current TableA […] SSRS 2016 [Execution Log Clean Up]( - The task that cleans up out of date execution log entries runs at 0200 every morning. This is not an ideal time for us because we're a 24 hour business and the most important times for are actually overnight. There have recently been complaints raised because SSRS is unavailable for a short period at 0200 […] Integration Services [SIS gets stuck on data flow task loading screen upon opening project]( - I'd like to start by mentioning that I have searched for this topic, although there are similar solutions out there, I haven't found one specific to my case and others I've tried haven't worked, so I am seeking any help relating to my issue. In SSIS, Anytime I open a project, whether I import it […] Anything that is NOT about SQL! [Searching for PII Data in Teams and Outlook]( - I've been performing a a Proof-of-Concept within our business, showing how Redgate's various tools can help the company to keep a track of PII data and let us use databases that have PII data as the basis for QA and Dev environments. One of the conversations with our InfoSec people raised a question that they […] PostgreSQL [OLE DB provider "PGNP" for linked server "MyLinkedServer" reported an error]( - Hi, I am trying to migrate SQL Server databases from 2008 to 2019. My old server has a linked server to PostgreSQL (version 8) I've installed the driver and created the linked server: I've installed the driver and created the linked server: EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'MyLinkedServer', @provider=N'PGNP', @datasrc=N'N'MyLinkedServer', @provstr=N'PORT=5432;CNV_SPECIAL_FLTVAL=ON;', @catalog=N'MyDB' ... The linked […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -