You are here:   Blog
Register   |  Login

Blog Archive:

* Can be used in order to search for older blogs Entries

Search in blogs

Blog Categories:

* Can be used in order to search for blogs Entries by Categories

Blog Tags:

* Can be used in order to search for blogs by keywords


Awared MVP


Microsoft® Community Contributor 

Microsoft® Community Contributor

 Read this before you use the blog! Maximize

Recent Entries

Author: ronen ariely Created: 08/10/2010 21:01 RssIcon
In Ronen Ariely Public Blog you can find technical posts mostly in the field of Data Platform and developing.
By ronen ariely on 24/04/2023 17:17

כמה פעמים שמעתם את הצעקה לעזרה או שאתם את הצעקה "המחשב שלי מקרטע! הדיסק של מערכת ההפעלה מלא! עזרו לי!!!"

בפוסט זה אני ארשום כמה מילים שבדיוק לפני כמה דקות הגבתי ב WhatsApp על הצעקה. זה לא פתרון מלא ולא מדריך מסודר אלא רק תגובה ראשונית שצריך לסדר מאוחר יותר למדריך מסודר יותר...

By ronen ariely on 10/03/2023 03:00
אם אתה עובד עם sqlcmd אז אולי פספסת את הופעת הבכורה של ה-sqlcmd החדש, המכונה go-sqlcmd. כלי זה מבוסס על מנהל ההתקן של Go mssqldb אשר פותח לעבודה עם שפת Go. הכלי החדש עדיין בגרסת הרצה בטא אבל מיקרוסופט הודיעו שהם מצפים שהוא יחליף את ה-sqlcmd הקיים תוך כדי הוספת יכולות חדשות. הכלי תומך מערכות מבוססות מעcדי arm64 בנוסף למערכות x64, הוא תומך עבודה עם Azure SQL Database והתקנות מקומיות של SQL Server. כדאי להמשיך ולקרוא כאן ->
By ronen ariely on 25/02/2023 14:18
Following the inquiry in the forums, it seems that there is a bug when we are using sp_refreshsqlmodule on a stored procedure which call HIDDEN system-versioned temporal tables with hidden columns. In this post I will reproduce the issue and present a simple explanation. For solution we will need Microsoft team to fix the bug.
By ronen ariely on 18/02/2023 05:38

When creating a VIEW using SQL Server Management studio (SSMS) wizard, then you might encounter the error: The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. This error is is NOT from the SQL Server but from the SSMS application. In this post I will elaborate a bit more, demonstrate the issue and explain the meaning of the message. There is no need to solve anything in most cases and it's just a warning for the user.

By ronen ariely on 21/12/2022 03:31
The issue: When we use windows operating system or most modern system and an error raise in applications, then in most cases the error will be alerted in an alert windows. In this post I will present how to get the text of the error message out of the error message window - this action is highly important for example in order to search for more information using a search engine or in order to ask for more information in forums.
By ronen ariely on 10/11/2022 20:56
Simple script to edit Azure VM Firewall rule using PowerShell and use the current public IP. This can be use to update for example the RDP rule or the SQL Server rule so we will be able to connect the server from local location.
By ronen ariely on 19/08/2022 16:23
Windows system uses files with the extension .msi for installation, using a built-in utility (small application) named Msiexec.exe. Obviously such extension can be used by other applications or include Malicious content as well, which is why this post only focuses on legit files. These files are packages (like for example files with the extensions .zip) that contains files to be installed and installation information like installation locations and other properties. It may be used for Windows updates or third-party software installers. This post come as an answer to a question in the forum and it elaborates a bit more about .msi extension and how you can extract the files inside the package without installing.
By ronen ariely on 27/07/2022 06:02
Without any announcement (which I know about), quiet as a mouse, the Microsoft Ignite site came to life with new information about Microsoft Ignite conference 2022 – Mark the date!
By ronen ariely on 27/07/2022 03:44
During the last year I got reports from people that joined meetings that they do not see the chat button when they are in the meeting. In this post I will discuss the issue and present optional solutions which other people reported that solved it in their case.
By ronen ariely on 25/07/2022 22:41
לפני יומיים מיקרוסופט הכריזה על האפשרות להשתמש בהצפנת נתונים עם מפתחות מנוהלים על ידי לקוח (CMK) עבור Azure Database for MySQL! האפשרות להביא מפתח משלך (BYOK) להגנה על נתונים במצב מנוחה, יכולה להעלות את האבטחה ולאפשר מיגרציה וסינכרון בין השרתים המקומיים לביןם השרתים בענן. אתה יכול להשתמש בתכונה זו כדי ליישם הפרדת חובות לניהול מפתחות ונתונים. בנוסף, ניתן לנהל ולארגן מפתחות באופן מרכזי באמצעות Azure Key Vault. השימוש בהצפנה מנוהלת על ידי לקוח ניתנת לך את הכוח והחראיות על מחזור החיים של מפתח האבטחה, הרשאות שימוש במפתח ופעולות ביקורת על מפתחות.
By ronen ariely on 15/07/2022 10:22
I am writing this post following a question and the discussion over Facebook about the question what is the different between JOIN and UNION. There are many common mistake which even experts are doing in defining these two task and some of these appeared in the mentioned discussion. I prefer to write the answer here since Facebook is not the best interface to write a long message - drive me nuts when in the middle of the writing Facebook raise and error and I lose all I wrote.
By ronen ariely on 14/07/2022 00:38
Following a question in the forum, I want to present the issue of using a variable in the a VIEW and how we can solve it as well as the right way to solve such request (hint: think if you are stuck in an "XY problem").

This discussion is only theoretical and only focus on the question.

By ronen ariely on 14/06/2022 00:36
Following my lecture at the .Net conference, about how to develop extensions to SQL Server Management studio, you can download the code and compiled package here. This is small add-in for SSMS in order to work with JSON and actually be able to read the JSON code. It has a "pretty JSON" functionality amount other tools.
By ronen ariely on 24/05/2022 17:50
לפני כמה ימים העתקתי טבלה מעמוד אינטרנט. הטבלה כללה עמוד עם כפתור של מחיקה וכפתור של עריכה - תמונות אשר משמשות ככפתורים. לשמחתי העתקת הטבלה לאקסל הצליחה להמיר את המידע טוב אבל השאיר לצערי את שתי העמודות הכוללות את התמונות. בפוסט זה אני אסביר בקצרה כיצד שנוכל למחוק את כל התמונות שיש לנו בגיליון (Sheet) בקובץ אקסל.
By ronen ariely on 20/04/2022 04:36
By default when we add a new external user to a team on Teams using his email, then the user gets a default name based on the email address. This means that if the person uses an email address which is not based on his real full name. then other members on the team might not be able to find the person in the team or recognize his user name when he will participate in activities. IN this short post I will present the issue and the "Best practice" which is configure the name of the user while we adding him to the team. I will also discussion how to fix the user information if he was already added without the right displayed name.
By ronen ariely on 15/04/2022 04:34
Delete old backup files can be done by running transact SQL query from a job that we schedule. Deleting files using transact SQL query can be done in multiple approach using external languages and OLE object, built-in undocumented Extended Stored Procedures and more. In this post I will go over some of the options we can use. This post is not a deep dive into the solutions but a review. For more information I am adding relevant links to each option I mention.
By ronen ariely on 29/03/2022 10:03
When I wrote this post I had a dilemma regarding the title, since I want to bring here 2 different aspects. The first aspect is technical and related to presenting a proven solution in terms of performance over the use of internal queries. The second aspect is more of a psychological discussion related to the choices that users make while completely ignoring performance in small systems. This post discuss using the function LAG and LEAD vs using sub-queries.
By ronen ariely on 18/03/2022 17:32
In the last several days there are reports regarding a strange behavior of SSMS which seems like started with version 18.11 and continue in version 18.11.1: A Beeps sound can is played each time we close the Query Editor Window. My tests suggests that this is a bug related to not disconnecting the server...
By ronen ariely on 26/02/2022 08:21
SQL Server Management Studio version 18.11 was released a few hours ago! What's new in 18.11?
By ronen ariely on 19/02/2022 18:27
Explanation, workaround and optional solution for the error: This operation has been cancelled due to restrictions in effect on this computer.
By ronen ariely on 01/02/2022 11:41

In the last several weeks I see many questions regarding Microsoft released of the new support to manage backups of SQL Server on premises in the Azure storage. This post is not intended to replace the official documentation, but only to simplify operations in a practical way. This post present step by step tutorial on backup to URL, backup to Azure Storage, and restore back to on-premises.

By ronen ariely on 20/01/2022 10:09
If you get the error: "The subscription is not registered to use <provider name>" then this post might help you understand and solve the issue. This can be related to change which was done in your subscription type.
By ronen ariely on 13/01/2022 00:42
step by step creating UBUNTU Azure VM and install SQL Server
By ronen ariely on 01/01/2022 13:40
The function GREATEST was added to Azure SQL but it not yet supported on SQL Server on premises (or synapse dedicated sql pool). In this short post I'll present a few options to implement the same result and discuss performance a bit.
By ronen ariely on 27/12/2021 15:13
This is a simple demo of using FILESTREAM and FILETABLE, following a question in the Microsoft QnA forum
By ronen ariely on 07/12/2021 18:07
Create table permission and Provide permission on the table only to the user which created the table, is not a recommended action, but if this is your requirement then this post would help in the task. It is more important to read this post in order to understand why this action is not recommended than to use it to actually implement such scenario.
By ronen ariely on 01/12/2021 21:07
It is quite common, especially when we are using free subscriptions, to have an issue to create Azure service in specific region, creating service using specific tier, or even creating the same service multiple times. These issues related to limitation in the Azure Subscription resource quotas. This post follows such issue which was raised in the Microsoft QnA forum. In this post I will explain the issue and present optional solution or clarify the limitation.
By ronen ariely on 23/11/2021 11:47
yesterday Microsoft Released the new Cumulative Update #14 for SQL Server 2019. Read more...
By ronen ariely on 19/11/2021 11:39

SQL Server 2022 is almost here. During the Microsoft ignite 2021 conference, on November 3, Microsoft announced the new version and started to present the new features and abilities. If you are working with SQL Server then it is now time to focus on SQL Server 2022. Read more on how to Get start with SQL Server 2022 - list of links

By ronen ariely on 05/11/2021 00:00
New as of today! Microsoft improved the function STRING_SPLIT and added a new optional parameter name: enable_ordinal, which allows us to get the returned SET in an order guaranteed, according to each substring's position in the input string 
By ronen ariely on 31/10/2021 09:44
Several days ago, I publish a riddle in the SQL Server product family on Facebook. The short version of the question is this: When we are executing a CREATE TABLE then the get the error that the table already exists, but when ew are exploring the SSMS Object explorer, then we cannot see any such such table. Can you explain why and how to reproduce this station?
By ronen ariely on 22/10/2021 05:36
Once again, we will dive into the internals of the SQL Server, since someone asked in the forum "How to find who created a database"....
By ronen ariely on 04/10/2021 01:02
This is an answer to a question in the Microsoft's QnA forum. As a result of a bug in the forum, I could not publish the answer
By ronen ariely on 22/09/2021 12:28
Line breaking, also known as word wrapping, is breaking the displayed of a section of text into lines so that the text will fit into the available width of the editor. When writing queries this feature is not so useful as breaking the script line may make the query less readable, but when writing long comments this feature become one of the most useful feature. This post simply shows you how to use word-wrap by default or add a command button to Toggle Word Wrap - it's a built-in feature which is less known and if you did not used it yet, then it is time to use the power of word wrap
By ronen ariely on 09/08/2021 16:32

Happy (Hijri) New Year! Today millions of Muslims celebrating the new year according to the Hijri calendar and this is a great opportunity to lean how SQL Server support (or not) working with Hijri dates. I dedicate this post to all my Muslim friends. This post is in Hebrew and is addressed to members in Israel who work with the Muslim calendar. Unfortunately many Muslims in Israel do not speak English and I do not speak Arabic, but we all here speak Hebrew. I hope this post will helpful and useful for you.

שנה טובה (Hijri)! כיום מיליוני מוסלמים חוגגים את השנה החדשה על פי לוח השנה ההיג'רי וזו הזדמנות מצוינת לבחון כיצד SQL Server תומך (או לא) בעבודה עם תאריכי ההיג'רי. אני מקדיש את הפוסט הזה לכל חבריי המוסלמים. פוסט זה הוא בעברית ופונה לחברים בישראל שעובדים עם לוח השנה המוסלמי. לצערי הרבה מוסלמים בישראל לא מדברים אנגלית ואני לא דובר ערבית, אבל כולנו כאן מדברים עברית. אני מקווה שהפוסט הזה יהיה מועיל ושימושי עבורך.

By ronen ariely on 07/08/2021 03:25
הנושא של רשיונות לשרתי SQL, מורכב מאוד ומעלה הרבה שאלות. בפוסט זה אני אנסה להסביר ולענות על שאלות נפוצות אשר עולות בפורומים. יש לזכור שמודל הרשיונות משתנה בין גרסאות שונות של השרת. בכל מקרה תמיד מומלץ לפנות למסמכים הרשמיים של מיקרוסופט או לקבל תמיכה בנושא ממנהל חשבון טכני של מיקרוסופט שזה תפקידו ומומחיות שלו - Microsoft Technical Account Manager (TAM).
By ronen ariely on 23/05/2021 11:40

A few years ago the GitHub organization set up a new service which operates under the github.io domain owned by GitHub. This service allows us to host websites for free. Site files are stored in a project's repository under a GitHub account. This article combines information from a number of official documentation and posts on the web and provides a first look at the GitHub Pages service, in a simple step-by-step guide. All the information in this article can be found online, and it is recommended that you follow the links in the "More Information" section. This post is essentially a translation of my article that I posted on TechNet Wiki on February 28, 2019.

לפני כמה שנים הקים ארגון GitHub שירות חדש אשר פועל תחת דומיין github.io הנצא בבעלות GitHub. שירות זה מאפשר לנו לארח אתרי אינטרנט בחינם. קבצי האתר מאוחסנים במאגר (repository) של פרוייקט תחת חשבון GitHub. מאמר זה משלב מידע ממספר תיעודים רשמיים ופוסטים ברשת ומספק הצצה ראשונה לשירות GitHub Pages, במדריך פשוט בסגנון צעד-אחרי-צעד. את כל המידע במאמר זה ניתן למצוא באינטרנט, ומומלץ לעקוב אחר הקישורים בפיסקה של "מידע נוסף". פוסט זה הוא בעיקרו תרגום של מאמר שלי אשר פרסמתי ב-TechNet Wiki ב-28 בפברואר 2019.

By ronen ariely on 18/05/2021 21:00
his post follows the discussion or actually my answer in this old MSDN forum thread. It present a few strange behavior of SQL Server when dealing with Hijri dates, explain the issues and end with a short recommendation for these working with Hijri dates.
By ronen ariely on 23/04/2021 11:39
This post come to answer the question how to learn SQL, which raise in the forum from time to time. It brings my personal insights about self-learning and a few sentences on how to start learning SQL. It is not exactly a regular post like I usually write but since the question was asked once more yesterday then I simply copy the answer which I gave in the forum so I will have it documented for the next time it will be asked
By ronen ariely on 11/04/2021 08:02

Azure Files (שירות קבצים משותף) הוא שירות בענן של מיקרוסופט, אשר משתמש בקבצים השמורים Azure File Storage. הקבצים משמשים כתיקיות שיתופיות וירטואליות. כל קובץ מוגבל ל-5TB אך אתה יכול להפעיל מספר שיתופים באמצעות קבצים מרובים. ניתן להשתמש ב-Azure Files כדי להחליף אחסון מחובר ברשת (NAS) או מערכות קבצים מקומיות. השירות מבוסס על פרוטוקול Server Message Block (SMB) ומאפשר להעביר יישומים ונתונים "כפי שהם" באמצעות פרוטוקול זה. ניתן לגשת לקבצים באמצעות API תוך שימוש בתקשורת מוצפנת, או לנהל את הקבצים באמצעות ממשק המשתמש המובנה בפורטל, דרך Azure CLI או PowerShell.

על ידי שימוש בשירות של Azure File Sync, אתה יכול לסנכרן ו/או לשכפל את הקבצים בין מספר שירותים בענן ואחסון מקומי לטובת גיבוי או שיפור ביצועים. השירות מאפשר ליצור מאגר מרכזי עבור קוד או כלי עזר המשמשים במהלך בדיקות ופיתוח.

שירות קבצים משותף מאפשר לרכז מדדים וקובצי יומן להטמעה על ידי כלי ניטור וניתוח. השירות מספק יתירות לניטור ופתרון תקלות נתונים.

By ronen ariely on 25/03/2021 21:04
Structured Query Language (SQL) commands can be categorize into DCL (Data Control Language), TCL (Transaction Control Language), DDL (Data Definition Language), DML (Data Manipulation Language), DQL (Data Query Language). So, what these terms actually means and how this is related to the way you should raise questions when you ask for help?
By ronen ariely on 17/03/2021 21:29
This issue was reported in the Microsoft QnA forum! Using nested inline User-defined Scalar Functions in SQL Server version 15.0.4102.2 returns an error. I confirmed this issue in several machines.
By ronen ariely on 10/03/2021 09:33
Once more, I got the request to help in cleaning the database from unused tables, and once more I find it important to clarify that we should not be reckless to drop a table or any other entity if you are not familiar with your database structure and you have no understanding what this object is used for or why it was created. It can come back to you to bite you in the ass in a month or maybe a few years. This post is a reply to specific request and present my general point-of-view and approach. It is NOT a full solution.
By ronen ariely on 05/01/2021 10:24
Just for me - shortcuts (will fill it in time)
By ronen ariely on 23/12/2020 11:41
SQL Server Configuration Manager and SQL Server Management Studio display the status of services by using the different icons. In this post I will summarize the different options. Part of the information in  this post was taken from the official documentation while several scenario are not documented and the information is based on my tests and research.
By ronen ariely on 07/11/2020 16:56

There is a great support for Teams in PowerShell, which allow you to get all a list of all the users in a specific team. Here is the procedure in steps

By ronen ariely on 28/10/2020 18:07
Always Encrypted - demo in a nutshell (using PowerShell and C# as clients). This is pure demo code from start to end
By ronen ariely on 25/10/2020 08:53

 Before the release of SQL Server 2016, one of the most required solution was for a function which split string. There are many solutions you can find online. Most of these are based on loops that provide poor performance, using XML, or using SQLCLR which in my test provides the best option.

After many years of requests for such a function, Microsoft has finally provided us with a new function called STRING_SPLIT in SQL Server 2016, which should solve our need to split a string. Unfortunately this solution is a half solution at best!

In most cases we need a solution which guarantee the order of the result set. The STRING_SPLIT does not provide this. Thus from 2016, the requests to provide a function for splitting string, has been replaced by shouts to improve the existing function by adding column which present the order of the text.

With that being said, there is a perfect solution using a different approach using another function which was presented in SQL Server 2016. Let's show how simple it is... 

By ronen ariely on 14/09/2020 10:20
In order to open a support ticket we need to login to Microsoft services. This is true in the Azure as well as in other services like Visual Studio Subscriptions(formally called MSDN subscriptions). So this leads direct to the chicken or the egg dilemma in the case our issue is with login to the account. We cannot login so we cannot open a support ticket. This VERY SHORT post simply provides the way to bypass this issue regarding the Azure (in other services you might find other solutions fit better)
By ronen ariely on 31/08/2020 10:57
Convert Azure Virtual Machine with SQL Server installed into Azure SQL Server Virtual Machine