Working with Azure IoT Central (2021-08-02)

SQLServerCentral Newsletter for August 2, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Working with Azure IoT Central]( - [Beautify HTML Tables in Emails for SQL Database Mail]( - [Overcoming Database DevOps challenges - Part 1]( - [From the SQL Server Central Blogs - Padding a string in SQL Server]( - [From the SQL Server Central Blogs - Azure Platform Series – renaming your YAML pipeline in Azure DevOps]( Question of the Day - [The Double Table]( The Voice of the DBA  Daily Coping Tip Be a realistic optimist. Think about what could go right 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. Interconnected Temp Files The other day I went to cook dinner for the family. I had picked a [new recipe]( (everyone loved it), and it was going to be a bit of prep. Before I started, I turned on the speaker in the kitchen, connected my phone, and started Spotify. I got 2 sec into the song, just enough for me to turn and reach for the cutting board when the music stopped. I turned back, started it and everything repeated. I tried a few times, but it kept happening. I opened Spotify on the iPad we have in the kitchen, where the recipe was displayed and tried there. I had the same experience. At this point, I was getting annoyed and a little stressed. I needed to get cooking, but I also wanted some music. Maybe a little bit of OCD coming out as I checked my desktop with the same result. I updated the credit card and had my daughter check her app. A little searching around had me try different things (rebooting, log out/in, etc.). Finally, I found one person that noted clearing the temp files on my desktop might help. I did that, deleting a few GB and cleaning out the UserData folder for Spotify. I restarted the app, and things worked. I walked back to the kitchen and the iPad, and music played there as well. Finally, I could get dinner started. I've been enamored with some of the Spotify-connected features, allowing a few of us to listen together. I like when I listen in the car (or desktop) and then move to the other location, I can pick up where I left off. However, I hadn't expected something like corrupt or data problems on my desktop to affect me on another device. As we start to interconnect more apps, it's possible that a problem on one device might affect others. We do interconnect some systems in the data world. We have clusters and Availability Groups, and we certainly sometimes have instances or databases that create dependencies between two systems. I doubt that many of you have one instance cause a problem with another, but it's worth keeping in mind. We want connected systems, but we don't want failures in one place to cascade throughout all the nodes. I like connected things, but I want loose coupling. I want one system to run on its own if the other has an issue, but I do want them to share data or status to improve the operation of the software. The big thing is that I don't want one device (my desktop) to affect the operation of another (my phone). At least not while I'm cooking. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Working with Azure IoT Central]( Sucharita Das from SQLServerCentral Gathering data from sensors and Internet of Things (IoT) devices is becoming more and more common in many organizations. This introductory article shows how you can get started with IoT Central in Azure. [Beautify HTML Tables in Emails for SQL Database Mail]( Additional Articles from MSSQLTips.com In this article we look at how to format HTML tables sent via SQL Server Database Mail to make the emails and data easier to read. [Overcoming Database DevOps challenges - Part 1]( Additional Articles from Redgate When integrating database changes into a DevOps process, the biggest challenge is to standardize and coordinate the different approaches to application and database development. Discover the key tools & training resources to help you and your team. From the SQL Server Central Blogs - [Padding a string in SQL Server]( Kenneth.Fisher from SQLStudies I’ve been working on converting a piece of DB2 code into T-SQL and one of the functions I had to ... Continue reading From the SQL Server Central Blogs - [Azure Platform Series – renaming your YAML pipeline in Azure DevOps]( HamishWatson from The Hybrid DBA's Blog This blog post is about YAML pipelines in Azure DevOps. I had a repo called InfrastructureAsCode for a client. I have been transitioning them to use YAML for their...   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Double Table I have this code: CREATE TABLE dbo.DoubleTable ( myid INT NOT NULL CONSTRAINT DoubleTable PRIMARY KEY , Val VARCHAR(20)); GO When I run this, I get this error: Msg 2714, Level 16, State 5, Line 19 There is already an object named 'DoubleTable' in the database. Msg 1750, Level 16, State 1, Line 19 Could not create constraint or index. See previous errors. Why? 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) Azure Dataset File Formats As of May 2021, which of the following file formats does Azure Data Factory not support? Answer: YAML Explanation: YAML is not supported at this time. Ref: Azure Data Factory Connector overview supported File Formats - [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 [SQL Server Agent Jobs are not logging history]( - A few days ago, my SQL Agent Jobs stopped logging to the history table. They don't show up in view-history and they are not in the sysjobhistory table. I do limit to 10000 rows with max 500 rows per job, but I've also tried not limiting it at all. The SQL Agent servers runs under […] SQL Server 2016 - Administration [Table Size out of control..]( - In the dev environment there is a table that gets loaded with verbose log data by a DOT NET app. The only thing that I care to retain in that table are entries designated as ERROR level entries. There are a total of nearly 2 million entries into this table every hour. What do you […] [Updating MDS on SQL Server 2016 without updating DB engine?]( - Hi, I am currently investigating removing the need for Silverlight for MDS on a Shared SQL Server 2016 instance I have just inherited. I have been asked to do it as soon as possible. In my research this morning, I have found the following documentation: Upgrading Master Data Services, which specifies an upgrade path without […] [New Index Maintenance Methods Recommended by Microsoft]( - With reference to the following MS article... That article was where the old "Best Method" recommendation of Reorg between 5 and 30% and Rebuild at > 30% fragmentation used to live. You can verify that by doing an "edit" on the page and looking for where they made the change on lines 73 thru […] [AG is continually reporting failover due to mirroring connect error]( - Hello experts, I'm seeing this Always On Availability Group behavior around every 5 minutes. And it is not like the AG is moving back and forth - it is always saying it failed over the BARDB02. The AG group FOOAG01 failed over to BARDB02. In the SQL error log, I see this error, which I […] [Removing "to Disk" Backup Files]( - Hi, I am taking our SQL servers and changing the way we back things up. Right now we use ola scripts to go to disk. We are now going to use a 3rd party tool to take over the backups. Questions Besides trans log shipping, is there anything that SQL Server would use the backup […] [SQL Agent output to file]( - What would the preferred method of outputing a cmdexec step? cmdexec enter: d:\jobs\myjob\step1.cmd > d:\jobs\myjob\step1.log 2>&1 2. go to advanced page and enter output file there?   SQL Server 2016 - Development and T-SQL [Routine to Limit Query Results and Output to a File]( - Dear Group: Please note: I can only use solutions that involve SSIS and/or Stored Procedures due to limits placed on our team and environment. As much as I understand and agree there are better solutions, we are limited to only things that involve SSIS and commands that can be executed inside a stored procedure. We […] SQL 2012 - General [MSSQL 2012 -> MSSQL 2019 -> MSSQL 2012 - Older version of Database]( - Hi, I migrated a Database from MSSQL 2012 to MSSQL 2019 and that's supported. But, now i got this ingenious idea of migrating back from MSSQL 2019 to MSSQL 2012 since i made some changes in the database. SQL 2012 Version: Microsoft SQL Server Standard (64-bit) Version 11.0.7507.2 SQL 2019 Version: Microsoft SQL Server Enterprice(64-bit) […] SQL Server 2012 - T-SQL [datetime column, Day of Week with count, how to add month/yr grouping]( - SELECT count(*) ,DATENAME(dw, [myDateTime]) as DOW FROM [myDatabase].[dbo].[myTable] group by DATENAME(dw, [myDateTime]) this gets me what i need for like a total for day of week but what would i need to do in order to get like a grouping by month, DOW, Count and a grpuping of Year, DOW, count currently i get DOW, […] SQL Server 2019 - Administration [error when using date column with remote oracle table]( - I realize this might be out of scope for the forum but figured it's worth a shot... I have a process where I copy data from a table in our local SQL Server instance to a remote Oracle table via Linked Server. I can do the INSERT without any issue at all. I can also […] SQL Server 2019 - Development [How to extract date from a long string]( - Hi All, have a long string like below in my column and I need to extract only the date (2021-07-05) from it. Could any one please help Name - Koteswararao vp Department - Prod Location - Hyderabad Domain - ND Job Title - Technical-ABAP Date of Hire - 2021-07-05 Vendor ID - v2345 Candidate ID […] SQL Azure - Administration [Migrate On-prem DB]( - Hi Experts, Is there anyway to migrate database with filestream to SQL Managed instanace? Will Azure database migration service will help to achieve this task?  TIA General [Index frangmenting]( - Good day, What would cause fragmentation on the Unique Clustered index on a table (its one and only index) that is only ever read from, and never written to? Thanks, P SSDT [Having users access .dtsx (package file) saved on LAN]( - Hello All, I created a package file (.dtsx) via SSDT. I took the folder containing this file and placed it on a shared drive, with the other associated files there as well (see attached image). The package is being executed from an excel file via this command, dtexec /File \\path\to\file\filename.dtsx; The file executes for me […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -