Lesson Learned #138: Transferring data between two tables is taking too much resources

by Contributed | Sep 30, 2020 | Uncategorized

This article is contributed. See the original author and article here.

Today, I worked on a service request that our customer has an internal process that every night they transferred data from table (iot_table1) to another table (iot_table2) based on several filters. Despite that the number of rows of the source table is increasing every day, this process is taking all the transaction log resource due to amount of data that this process is transferring. 

In this type of situation, our best recomendation is to use Business Critical o Premium because the IO capacity is greater if you are using General Purpose or Standard. But, our customer, wants to find an alternative to stay in the Standard/General Purpose without moving to Premium/Business Critical in order to reduce the cost. 

Let’s assume that our customer has two tables: IOT_Table1 (source) and IOT_Table2 (destination).

CREATE TABLE [dbo].[iot_table1](
	[id] [int] NOT NULL,
	[text] [nchar](10) NULL,
	[Date] [datetime] NULL,
 CONSTRAINT [PK_iot_table1] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[iot_table2](
	[id] [int] NOT NULL,
	[text] [nchar](10) NULL,
 CONSTRAINT [PK_iot_table2] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

We suggested different workarounds to prevent the execution of this process, for example, using an incremental process using Azure Data Factory or the following alternatives using SQL Server Engine:

• Alternative 1) Create a trigger that for every row that you are inserting in the table iot_table1 will be transferred to iot_table2. I’m sharing with you an example about it:

CREATE TRIGGER [dbo].[inserted]
   ON [dbo].[iot_table1]
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    INSERT INTO IOT_table2(ID,[TEXT]) SELECT ID,[TEXT] from inserted
END

• Alternative 2) Reduce the amount of numbers of rows to be transferred, for example, creating a table per day. 

CREATE TRIGGER [dbo].[inserted]
   ON [dbo].[iot_table1]
   AFTER INSERT
AS 
BEGIN
       DECLARE @DAY AS VARCHAR(2)
       DECLARE @MONTH AS VARCHAR(2)
       DECLARE @YEAR AS VARCHAR(4)
       SET NOCOUNT ON;
       SET @DAY = CONVERT(VARCHAR(2),DAY(GETDATE()))
       SET @MONTH = CONVERT(VARCHAR(2),MONTH(GETDATE()))
       SET @YEAR = CONVERT(VARCHAR(4),YEAR(GETDATE()))
       IF @MONTH=9 AND @DAY =25 AND @YEAR=2020
       INSERT INTO IOT_table2_2020_09_25(ID,[TEXT]) SELECT ID,[TEXT] from inserted
    IF @MONTH=9 AND @DAY =26 AND @YEAR=2020
       INSERT INTO IOT_table2_2020_09_26(ID,[TEXT]) SELECT ID,[TEXT] from inserted
    IF @MONTH=9 AND @DAY =27 AND @YEAR=2020
       INSERT INTO IOT_table2_2020_09_27(ID,[TEXT]) SELECT ID,[TEXT] from inserted         
    IF @MONTH=9 AND @DAY =28 AND @YEAR=2020
       INSERT INTO IOT_table2_2020_09_28(ID,[TEXT]) SELECT ID,[TEXT] from inserted         
    IF @MONTH=9 AND @DAY =29 AND @YEAR=2020
       INSERT INTO IOT_table2_2020_09_29(ID,[TEXT]) SELECT ID,[TEXT] from inserted         
END

• Alternative 3) Using Indexed views but it is depending on the definition of this table, because we have some limitations, for example: – https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15

• You could create an indexed view that the definition, as example, could be:

CREATE OR ALTER VIEW Data_Per_Day_29_09_2020
WITH SCHEMABINDING
AS
SELECT ID, [TEXT] from dbo.iot_table1 where DAY([DATE])=29 and MONTH([DATE])=9 AND YEAR([DATE])=2020

CREATE UNIQUE CLUSTERED Index Data_Per_Day_29_09_2020_X1 ON Data_Per_Day_29_09_2020(ID)

• When you have an indexed view the data will be automatically saved as materialized data, so, in every row that you added in the table depending on the value of the field DATE you are going to have a materialized view with data. If you run the query using this view SELECT * FROM Data_Per_Day_29_09_2020 WITH (NOEXPAND) the data that you are going to have is the materialized data and will be not retrieved from the table. If you use SELECT * FROM Data_Per_Day_29_09_2020 the data will be retrieved from the table.

Enjoy!!

Lesson Learned #139: Operating system error code 80(The file exists.) running a bulk insert

by Contributed | Sep 30, 2020 | Uncategorized

This article is contributed. See the original author and article here.

Today, I worked on a service request when our customer wants to have an error file in case that any issue may happen running a bulk insert process in Azure SQL Database. 

Unfortunately, every time that our customer execute the bulk insert process they faced the following error message: 

Msg 4861, Level 16, State 1, Line 19
Cannot bulk load because the file “Error.Txt” could not be opened. Operating system error code 80(The file exists.).
Msg 4861, Level 16, State 1, Line 19
Cannot bulk load because the file “Error.Txt.Error.Txt” could not be opened. Operating system error code 80(The file exists.).

I reproduced the issue and I found the reason why our customer faced this error: 

First of all, let’s try to create the example code:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123!!!!'
CREATE DATABASE SCOPED CREDENTIAL [JM_Scope] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=2019-12-12&ss=b&srt=sco&sp=rwdlacx&se=2020-10-28T23:38:22Z&st=2020-09-29T14:38:22Z&spr=https&sig=24TJMAf1hOzE2DDVqw1QRs....'
CREATE EXTERNAL DATA SOURCE [JM_EXT_DSource] WITH ( TYPE = BLOB_STORAGE, LOCATION = 'https://myblobstorage.blob.core.windows.net/jmread', CREDENTIAL = [JM_Scope] );

CREATE EXTERNAL DATA SOURCE [JM_EXT_DSource_Error] WITH ( TYPE = BLOB_STORAGE, LOCATION = 'https://myblobstorage.blob.core.windows.net/jmread/error', CREDENTIAL = [JM_Scope] );

CREATE TABLE Table1 (ID INT)

BULK INSERT [dbo].[Table1]
FROM 'filetoread.txt'
WITH (DATA_SOURCE = 'JM_EXT_DSource'
, FIELDTERMINATOR = 't'
, ERRORFILE = 'error.Txt'
, ERRORFILE_DATA_SOURCE = 'JM_EXT_DSource_Error')

I modified the filetoread.txt with an incorrect values as follows in order to raise a conversion issue.

1
2
3
IK

The first time that I executed the process everything worked as expected and I got an error:

Msg 4864, Level 16, State 1, Line 19
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 1 (ID).

Completion time: 2020-10-01T00:42:10.9217117+02:00

As result of this issue I got two files:

• Error.Txt that contains the data lines that are causing the error.
• Error.Txt.Error.Txt that contains the number line and the error message with all details.

The second time that I executed the process I got another error message: 

Msg 4861, Level 16, State 1, Line 19
Cannot bulk load because the file “Error.Txt” could not be opened. Operating system error code 80(The file exists.).
Msg 4861, Level 16, State 1, Line 19
Cannot bulk load because the file “Error.Txt.Error.Txt” could not be opened. Operating system error code 80(The file exists.).

This error is caused because if the ErrorFile exist in the destination folder there is not possible to overwrite and you need to delete the file. You have this information in this doc : “The error file is created when the command is executed. An error occurs if the file already exists. Additionally, a control file that has the extension .ERROR.txt is created. This references each row in the error file and provides error diagnostics. As soon as the errors have been corrected, the data can be loaded.“

Enjoy!

Project for the web – September 2020 Update

by Contributed | Sep 30, 2020 | Uncategorized

This article is contributed. See the original author and article here.

Our team recently updated the items on the Microsoft 365 Public Roadmap. You can see our upcoming features and when those features will be available! 

You can let us know what you think about these new features on Microsoft UserVoice. Join an existing thread or create a new one to tell us how to make Project better.  

We also love feedback on existing features! If you have feedback, submit it by using the ‘Feedback’ button in Project for the web. Make sure to include your email so we can contact you directly with any follow up questions or comments. We also monitor the comments on all blog posts, so let us know what you think about this or other articles! 

New Features: 

• Task Custom Fields: Store custom information about your projects efficiently by adding custom fields! For more information, check out the blog post on this feature.  
• Dynamics 365 Project Operations: Our team partnered with Dynamics to help launch Project Operations, a new application that has everything you need to run your service business. Check out our new features and capabilities released as part of wave 2 on October 1st. For more information on this product, check out the Project Operations overview page 
• Group by Assigned to: You can organize your tasks by assignee in the Board view. Get insight into what each person is working on! This feature should now be available to all Project users.  

• Self-Service Onboarding: Customers can make a self-service purchase online. New users can try out Project and other Microsoft solutions on their own! Check out the FAQ page for this feature to learn more. 

Upcoming Features: 

• Project & Roadmap in Microsoft Teams: Starting in October, you’ll be able to add a project or roadmap as a tab in Teams. Chat with your teammates while viewing and updating your work. 
• Export to Excel: Export your project grid into an Excel spreadsheet. Share this information with colleagues and analyze Project data using the power of Excel! 

• Share projects with groups you don’t own: You can see all your added groups when you add a group to your project. Easily share your work with all the important people without creating new groups. 

Answers to top questions: 

Q: I was not able to attend Ignite this year, but I still want to watch some sessions! How can I do that? 

Microsoft has several recorded sessions from Ignite that you can watch. Here are links to some of our favorites: 

• What’s New in Microsoft Dynamics Project Operations 
• Ask the Expert: Leverage Microsoft Project and the Project Accelerator 

Also, we have some post-Ignite opportunities for customer connection. These conversations will run through October and cover a range of topics. You can find more information on these expert connects, including the list of events & the sign up page, on this site. The sign up form for these events can be found here.  

Imagine Cup Junior 2021 AI for Good Challenge

by Contributed | Sep 30, 2020 | Uncategorized

This article is contributed. See the original author and article here.

By Anthony Salcito, Vice President, Education

During Microsoft’s recent global skills announcements, it was shared that over 149M new jobs will be created in technology over the next 5 years. While this shows the immediate need to upskill and reskill on technology to fuel economic growth and talent pipeline, the question remains – how we can ensure a more sustainable solution for many years to come?

At Microsoft, our mission is to empower every student on the planet to achieve more. Connected to this mission, Microsoft continues to work hard to spark student interest in STEM and Computer Science and prepare them for a path where technology is a core subject area connected to success in every role in the future.  That’s why I’m excited to share today’s launch of Imagine Cup Junior AI for Good Challenge 2021. This is the second year we’ve run this challenge for secondary students, inviting young and talented minds to come up with ideas to make their world a better place with the power of Artificial Intelligence (AI). In our inaugural year we celebrated 9 winning teams from the hundreds of students across 23 countries who took part, and I was amazed by the imagination of students, the quality of their ideas and submissions.

Imagine Cup Junior AI for Good Challenge brings new skills to students across all subject areas regardless of their experience in technology. No longer is technology a separate discipline but rather a foundational capability that will enhance every students’ future opportunities, no matter what job role they pursue in their future.  Students aged 13 to 18 can take part, individually or in teams up to 6, by developing an AI concept based on Microsoft’s AI for Good initiatives. These include AI for Humanitarian Action, AI for Earth, AI for Cultural Heritage, AI for Accessibility and new to our 2021 challenge, AI for Health.

While it’s been a challenging year with remote and blended learning becoming a part of many school days for students, we have introduced a number of new elements to Imagine Cup Junior AI for Good Challenge to increase the opportunity for all students to participate including webinars, hackathons and a beginners kit. To get started, educators need to register at https://.imaginecup.com/junior which will provide access to the Imagine Cup Junior resource kit which includes:

• Imagine Cup Junior for Beginners Kit – five 45-minute lessons that will prepare students for their challenge submission
• Educator guides, student guides, and slides for the following modules for those who would like to take learning further:
  • Imagine Cup Junior for Beginners
  • Fundamentals of AI
  • Machine Learning
  • Applications of AI in real life
  • Deep learning and neural networks
  • AI for Good
• Build your Project in a Day hackathon kit with videos from members of Microsoft’s Education, Artificial Intelligence and Cloud teams. This can be used in class to inspire students and coach them on how to get started, and perhaps even spark excitement to one day work in the field of AI
• Engagement plans for educators on how they can embed the learning within their curriculum
• Access to a series of AI webinars throughout the challenge and regional virtual hackathons for students to build out their projects live

Plus lots more, including challenges using Azure, Minecraft: Education Edition, and social kits and templates to celebrate taking part.

We are also empowering parents and guardians to register and submit on behalf of students in the event that learning from home continues, and the webinar and hackathon series will provide inspirational and exciting learning opportunities for students both at home or in school. 

Registration opens today and will close May 21 2021. To ensure the privacy of students, all submissions must be made by educators/instructors/parents/guardians on behalf of their students. While we can’t wait to see ALL the amazing ideas of students around the world, Microsoft will be proud to recognize the top ten ideas globally and recognize their achievement with an Imagine Cup Junior trophy.

Challenge rules and regulations can be found here.

It is never too early to get started, and we hope by cultivating student creativity and passion for technology it will spark interest in and support the development of careers at the cutting edge of technology.

Register today at https://imaginecup.com/junior and empower students to truly change the world. I can’t wait to see their innovation and ideas to help positively change the world!