Stairway to U-SQL Level 21: Exporting Data with Azure Data Factory (2019-07-31)

 SQLServerCentral Newsletter for July 31, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to U-SQL Level 21: Exporting Data with Azure Data Factory]( - [SQL Change Automation Scripting: Getting Data into a Target Database]( - [Fill In Missing Dates for SQL Server Query Output]( - [Free eBook: SQL Server Backup and Restore]( - [From the SQL Server Central Blogs - Containers make easy upgrades]( - [From the SQL Server Central Blogs - Understanding Columnstore Indexes in SQL Server Part 2]( Question of the Day - [Finding the username]( [Redgate SQL Provision]( The Voice of the DBA  Technical Debt This editorial was originally published on Nov 23, 2015. It is being re-published as Steve is on vacation. I was speaking with one of the development teams at Redgate earlier this year. They were working on a product, and had planned out a few sprints worth of work. Each sprint, called a train, was a couple weeks long, with specific goals and ideas to be implemented. That was all good, but I noticed that there was a sprint in the middle of the list that was devoted to technical debt. Technical debt is a strange term. It's one that many managers don't understand well, often because the code may work fine. I ran across [an interesting piece](MostlyHarmlessD/on-technical-debt-7bac65edf349) that looks at what the concept means, with what I think is a good explanation. We get technical debt when we sacrifice maintainability to meet another requirement. The piece also looks at the accumulation of the debt and why it becomes problematic later. Certainly the more debt that piles up, the mode difficult it can be to change code. Since we are almost always going to go back and maintain code, this becomes a problem. I think the ideas given to keep technical debt under control are good ones. We should make an effort to clean code as we can, though not make it such a priority that we end up causing more work with constant refactoring. We do need to get work done. However the suggestions given require a good amount of discipline and buy in from management, and I'm glad Redgate tries to keep debt under control. I think our developers like the debt trains as well. I thought the idea was pretty cool until I was looking for a feature to be completed and the technical debt train was running that week. I thought about complaining, but I decided to have patience and wait a week. After all, if the debt isn't kept under control, I might be waiting much longer than a week for some fix or feature next year. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums]( [Redgate SQL Provision](   Featured Contents [Stairway to U-SQL]( [Stairway to U-SQL Level 21: Exporting Data with Azure Data Factory]( Mike McQuillan from SQLServerCentral.com Learn how you can move data from your Data Lake to Azure SQL Database with Azure Data Factory. [SQL Change Automation Scripting: Getting Data into a Target Database]( Additional Articles from Redgate Your strategy for loading a freshly built database with data will depend on how much you need. If just a few rows, then single-row INSERT statements will be fine, but for more than that you'll need to build insert the rows using native-format BCP. Fortunately, SQL Change Automation (SCA) can be used with either strategy, as Phil Factor demonstrates. [Fill In Missing Dates for SQL Server Query Output]( Additional Articles from MSSQLTips.com In this tip we look at how to fill gaps in dates and times for SQL Server query output when no data exists for specific dates or times. [Free eBook: SQL Server Backup and Restore]( Press Release from Redgate In this free eBook Shawn McGehee offers advice on query tuning, cutting stored procedures, and system process design and implementation for high availability. Discover how to perform backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Redgate's SQL Backup tool. From the SQL Server Central Blogs - [Containers make easy upgrades]( Steve Jones - SSC Editor from The Voice of the DBA I’ve been working with containers and writing a bit about them for awhile. I find them to be fascinating and useful as a technology, and I’ve come to really... From the SQL Server Central Blogs - [Understanding Columnstore Indexes in SQL Server Part 2]( SQLEspresso from SQLEspresso In my last blog I explained what a columnstore index is, in this blog, we will dive into creating a clustered columnstore index and look at the performance differences...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Finding the username In Python, I have this code that runs: str = """EmailAddr,Nickname,Role ... sjones@sqlservercentral.com,Steve,Admin ... webmaster@sqlservercentral.com,Webmaster,Admin ... pressrelease@sqlservercentral.com,Press,author ... """ I now want to use a regular expression to find the username in the email. I run this: for i in re.finditer('([a-zA-Z]+)@([a-zA-Z]+).(com)', str): ... print(i.group(xxxx)) What should replace the "xxxx" to get the username from the email address? 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) The first step For the first histogram step in SQL Server 2017 statistics, what is the value of the average_range_rows column stored in sys.stats? Answer: 0 Explanation: By definition, the value for the first histogram step is always 0. This is documented just below the histogram image in the reference. Ref: Statistics - [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 - Development [Group and total by column Name]( - Guys, How do i group the below by type and date so i have a total for each day for each person by each type. So the desired results should look like: INTO #SampleData2 FROM ( VALUES ('2019-01-01', 'Dave', 'Break', 797), ('2019-01-01', 'Dave', 'Break', 746), ('2019-01-01', 'Dave', 'Break', 511), ('2019-01-01', 'Bethan', 'Break', 631), ('2019-01-01', 'Bethan', […] [What is wrong with my code?]( - Here is my code: CREATE TABLE ProjectCodes ( ProjectID varchar(22), ProjectName varchar(25), Level char(1), [Project Classification] varchar(14), [Project Type] varchar(11), Billable char(1), DEFAULT 'Y', [Allow Charging] char(1), DEFAULT 'Y', Active char(1), DEFAULT 'Y', [Contract No] char(17), [Task Order No] char(17), CONSTRAINT PK_ProjectCodes_ProjectID PRIMARY KEY CLUSTERED (ProjectID ASC) )  The error message is: Msg 142, […] [SQL error : Message 207]( - Can anyone help me understanding this particular werror :  Msg 207, Level 16, State 1, Procedure SP_FlagTests_CY, Line 266 Invalid column name 'BaseSolvencyBuffer_CYMinus1'. [Computed Columns Formulas]( - So, I'm trying to figure out what's the best approach for this: (FIelds:) A - currency B - Equals A C - (A+B)/2 D - Checkbox: If on, C=C/2 (meaning, not a new field, just turns C into Half of C) I need this fields to be "persisted", so that I can use them on […] [understanding behavior [with persist_sample_percent=on] on maintenance process.]( - When creating a table [s1].[tmp_t1], inserting some data into same table (~500 rows) from table [s1].[t1], dropping [s1].[t1], and renaming [s1].[tmp_t1] to [s1].[t1] as a part of a maintenance process, I am seeing update stats without using the persisted option and n rows that i last executed manually. I am wondering why the query optimizer […] [empty table is large]( - I have a table used for staging during ETL. It is empty most of the time, but on occasion, during data loading, it can get quite large. Even after all records are deleted, the table is still very large & this is making table scans very slow regardless of how many rows it has. Table […] SQL Server 2016 - Administration [Migration from MS Sql Server 2016 => PostgreSQL 9.6.12]( - Looks like this is going to be a thing for me sooner rather than later and I'm just starting to think about how to go about this. Does anyone have any good reference material or know about any conversion apps that are worth the effort? Also, does anyone have any advice for keeping a PostgreSQL […] [SQL Server Agent stops unexpectedly]( - SQL 2016 Enterprise running on Server 2012 We migrated a lot of ETL jobs to a new server and after about a week of running the agent stopped on us one morning. This happened again about 4 days later. Nothing in the agent or SQL logs. in both cases the event viewer showed an application […] Administration - SQL Server 2014 [Change Character]( - I need to change Varchar column from 100 to 200 for one of the table Table has 80 Million records and allows Null values. I don't have test server to test, so i am curious how long it will take to Alter Column from 100 to 200 Varchar Development - SQL Server 2014 [Is there CLEAN function, or similar, similar to the Excel function>]( - The Tables I use often times have control characters in them, like CRs and LFs. The data also has spaces. I know how to remove spaces, but how can I a function like CLEAN (in Excel) in my SSMS report?? possible?  thanks SQL 2012 - General [( - Seventy five percent of our skin is included water and collagen. Our skin is presented to cruel UVA and UVB radiation bringing about age spots, barely recognizable differences, and wrinkles. As we age, our bodies produce less and less collagen, prompting the arrangement of wrinkles and scarcely discernible differences. Most enemy of maturing items use […] SQL Server 2008 - General [how to get 0 if records have empty or null values when column datatype is numeri]( - Hi I have one doubt in sql server how to get 0 when records have empty or null values when column datatype is numeric in sql server else get max(id) values in sql server Table : empid CREATE TABLE [dbo].[empid]( [id] [numeric](11, 0) NULL ) ON [PRIMARY] GO INSERT [dbo].[empid] ([id]) VALUES (NULL) GO INSERT […] SQL Azure - Development [Azure SQL MI Question (tempdb issue)]( - We are getting following error while dumping data from Databricks into SQL MI. "Error : Could not allocate space for object 'dbo.SORT temporary run storage: 140799153471488' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting […] Reporting Services [Comparing list to only show Names that have not turned in something]( - I have 2 Data Sets. One that shows The peoples names and when they have turned in there field logs. Then I have a data set that is just a list of names. How can i compare the two data sets to only show the names of the employees that have not turned in a […] Integration Services [How to run package (catalog) from PowerShell with Environment variable in DTExec]( - Hello Everyone, I am new to PowerShell. I don't even know the basics. I have used the below script and able to run the package successfully. But the problem with this script is it just triggering the package and returning the execution id. Not sure whether the package got succeeded or not. Later I […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -