Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Daily Coping Tip

Get outside today and give your mind a natural boost

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.

A Great Use for AI

In the last couple of years there has been a tremendous amount of hype for machine learning and artificial intelligence as a way to improve the world. Plenty of companies have tried to implement ML/AI to generate more revenue or improve their products, often with mixed success. However, I recently saw a place where I think AI might shine.

I've never owned a Roomba or any robot vacuum, and I've never encountered a poopocalypse scenario. I do have a cat that is an avid hunter, so I certainly could envision something similar with a carcass in the house, but apparently, some owners of these vacuums have had a very messy experience when they pet has an accident and the robot vacuum attempts to clean the floor.

The company has implemented a camera and AI to try and avoid this happening, as well as avoiding other obstacles. How this will actually work remains to be seen, but it's a good place to use AI to try and detect objects that might cause issues, notify the owner, and avoid creating a mess when trying to clean one up.

This is also a place of low impact if the AI doesn't work perfectly. If the model can't determine what an object is, avoid it and flag the situation. Allowing owners to provide feedback and improving the model over time is what I'd want to see, with regular improvements that might help the system tell when an object is something that could cause issues. If Roomba does a good job, they'll use this as an opportunity to gather data and improve their products.

AI/ML isn't often a build it and forget it technology for systems. These technologies use models, which are inherently incomplete and don't always match the real world well. They need a lot of training, with new data, across time to become something that works really well.

Are they worth the effort for most systems? I don't know. I do know that good data science is needed, lots of data for training and testing, and a set of boundaries where the system works well and where it doesn't. I suspect we'll see more businesses having success with AI over time, but not in all situations. I suspect older extrapolation and human judgment work just as well for lots of problems.

Knowing when each might work more efficiently will be a challenge for years to come.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

pass-pro-educational-series
 
 Featured Contents

How to Flatten JSON in Azure Data Factory?

diponkar.paul from SQLServerCentral

When you work with ETL and the source file is JSON, many documents may get nested attributes in the JSON file. Your requirements will often dictate that you flatten those nested attributes. There are many ways you can flatten the JSON hierarchy, however; I am going to share my experiences with Azure Data Factory (ADF) […]

Flyway Without the Typing

Additional Articles from Redgate

How to use Flyway configuration files to minimize typing during ad-hoc development from PowerShell or DOS; you just type in the Flyway commands you need and hit "go" and the config files take care of all the tiresome connection, authentication and project details.

Mount an Azure Data Lake Storage Gen2 Account in Databricks

Additional Articles from MSSQLTips.com

In this article we will walk through a clear end-to-end process of securely mounting an ADLS gen2 account in Databricks.

From the SQL Server Central Blogs - Script to create copies of the existing object

Brahmanand Shukla from SQL Server Carpenter

Many a time we need copies of an object but with different names. I also got a requirement to process the files in parallel using SSIS. We had already...

From the SQL Server Central Blogs - Rule 1: Be kind, be polite.

Kenneth.Fisher from SQLStudies

In my house, this is rule one. Sometimes it’s my wife or I reminding our kids, sometimes it’s them reminding ... Continue reading

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Getting the JSON Index

I have this JSON document:
DECLARE @Json NVARCHAR(MAX)
    = N'
[
    {
        "Name": "Broncos",
        "Division": "AFC West"
    },
    {
        "Name": "Chiefs",
        "Division": "AFC West"
    },
    {
        "Name": "Steelers",
        "Division": "AFC North"
    },
    {
        "Name": "Colts",
        "Division": "AFC South"
    }
];';
I want to query this and get a result set like this:
Element  Team     Division
-------- -------- ------------------------------------------------
0        Broncos  AFC West
1        Chiefs   AFC West
2        Steelers AFC North
3        Colts    AFC South
I have this query, but what do I put in place of "xxx" to get the index value of the data?
SELECT A.[xxx] AS Element,
       JSON_VALUE(A.Value, '$.Name') AS Team,
       JSON_VALUE(A.Value, '$.Division') AS Division
FROM OPENJSON(@Json) AS A;

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 Firewall Issue

What's the issue here?

Firewall set errors

Answer: The datatype is wrong.

Explanation: The problem is that the first parameter, the rule name, is an NVARCHAR() type. Since this procedure is actually an extended stored procedure in Azure SQL Database, the data types must match. This code would work:

exec sp_set_firewall_rule N'Test Rule', '1.1.1.1', '1.1.1.1'

Ref: sp_set_firewall_rule - https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-firewall-rule-azure-sql-database?view=azure-sqldw-latest

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
Wait stats - I am not seeing any high resource utilization like CPU/Memory and Disk queue length. But seeing a waits  during the performance issues. Is that be related to the issue? Please advise?
SQL Server 2017 - Development
Need help on ( Keep it existing, Add new ) & ( Remove existing, Add new ) - I've table & EXISTING data as below, Scripts as following, CREATE TABLE [dbo].[IncidentProgressAssignPeople]( [Id] [int] IDENTITY(1,1) NOT NULL, [IncidentProgressId] [int] NOT NULL, [ToUserId] [nvarchar](450) NOT NULL, [ReplyDate] [datetime] NOT NULL, [isInProgress] [bit] NULL, [isInProgressDate] [datetime] NULL, [isResolved] [bit] NULL, [isResolvedDate] [datetime] NULL, [dtVersion] [timestamp] NULL, CONSTRAINT [PK_IncidentProgressAssignPeople] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX […]
SQL Server 2016 - Administration
AD Group SQL Login Access Details - Good Morning, We have AD Group with members. The AD Group is created as SQL Server login. I would like to know what all access this login has. Please let me know.
Default Trace File Q - Are Does Not Exist and Do Not Have Permission events inc - SQL SERVER 2016 (13.0.5026.0) NOTE: The reference SQL code is not something I/We created but was setup by Prophix when they implemented the system for us. I am only responsible for trouble-shooting it when things fail, not for the T-SQL code it uses. We have a B2B app named Prophix that drops an existing table […]
Administration - SQL Server 2014
Probleme With Access to Reporting Services SSRS - Hi I have a problem when i try tio display Report, (Win Server  2008R2 And SQL 2014 ) i can access only with a Local account, but for the athers AD account it display an error like see attach, But before it s working correctly. Withe the local account i can display the page, but […]
3 node Always On but only 1 node points to correct listener IP - Hello, I have 3 node SQL server 2014 SP3-CU4-GDR, and each server is on a different subnet: server1: 192.168.1.25 server2: 192.168.2.25 server3: 192.168.3.25 and I have the listener with mutliple subnets configured like so: 192.168.1.0/24 - ip address 192.168.1.26 192.168.2.0/24 - ip address 192.168.2.26 192.168.3.0/24 - ip address 192.168.3.26 192.168.4.0/24 - ip address 192.168.4.26 so […]
SQL 2012 - General
What makes MS SQL Server worth paying for over MySQL? - Considering well designed, large DBs that have high usage, what do MS SQL Server and another commercial DB systems offer that MySQL and other free systems don't? Is it a performance thing? security, backup, redundancy?
How to use inner join instead of where exists statment ? - I work on sql server 2012 i face issue ican't replace (where exists) by inner join so How to do it SELECT pr.partid from parts.Nop_Part pr with(nolock) inner join extractreports.dbo.RequiredPartsPL rp with(nolock) on rp.partid=pr.partid inner join Parts.Nop_PackageAttribute pa WITH(NOLOCK) on pa.packageid=pr.packageid inner JOIN dbo.Core_DataDefinitionDetails dd WITH(NOLOCK) ON pa.[Key] = dd.ColumnNumber --and dd.acceptedvalueid=64 where exists(select 1 […]
SQL Server 2019 - Development
Copy files from folder using SQL Server. - Good day everyone. I hope someone can help me on that one. I have a database that include file folder and document location into tables and the actual document is store on a document server. I would like to copy specific documents to a different location and rename the document to a more readable name […]
Merge Conflicts with SQL Server Database Project - I hope someone more familiar with database projects can provide me some guidance. I am currently trying to introduce a better source control solution and automated deployment solution for a project I have joined. The current state is I have a database project in Visual Studio 2019 which will be used to maintain an Azure […]
Implicit conversion on hash key column is causes very slow insert - I am trying to insert some data into some stage tables where the insert is taking far too long. For example a table containing up 600000 records is taking nearly an hour to complete. In the select part of the query we are are creating a hash of the columns which is later used for […]
SQL Azure - Development
Unable to add data disk in Azure SQL Virtual Machine - Trying to extend my data disk to get some free space in the drive. But I couldn't able to resize the data disk in my SQL Virtual Machine in Azure as the resize option greyed out. And the disk is managed disks only. Please some one can suggest me on this...., it would be greatly […]
Amazon AWS and other cloud vendors
Does AWS RDS SQL SERVER support to file access from EC2 server local path? - I would like see is there any option to access the EC2 machine file from RSD SQL Server? Notes: EC2 and RDS were in the same VPC. I am using RDS SQL server Standard Edition.   Can you please help me on this? if it doesn't are there any plans to? Thanks in advance!
General Cloud Computing Questions
anyone using vercel sqlite3? - Hello! I am trying to use sqlite3 on Vercel just to setup my environment, but I don't know how. I saw that when deploying python app, Vercel installs sqlite3 on their own but I can't find a way to access it ... my python app can't find sqlite3 module . How can I get an […]
SSDT
Execute sql task behave differently when run in isolation versus part of package - SQL Server 2019 Azure. I have a basic fact load package that someone has tagged an execute sql object onto the end of that updates various fields in a fact table. when I run the whole package, 2 out of the 6 columns do not update and when selecting from the table, they appear as […]
 

 

RSS FeedTwitter

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

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -