Blog

See below for the complete list of blog posts to make it easy to find a specific post.

I abandoned my blog ???

Hey guys !!! I hope everything is fine with you! I believe that whoever follows me here on the blog must have noticed that I have been missing a lot in the last few months here on the blog. For those who have maintained an average of 80 posts per year since 2016 (1 post every 4,5 days) and have 18 posts in 2020, which is already close to ...
Read More

Connect-PowerBIServiceAccount: Failed to populate environments in settings

Introduction Speak personal !! In this post, I would like to demonstrate to you the solution to a problem that happened to me a few days ago, when the routine of automatic updating of the Power BI data set (How to use PowerShell and the API with Agent or SSIS to automate the updating a Power BI dataset) has stopped ...
Read More

Windows 10 - How to solve Bluetooth headset problem with lag, crashing and cutting audio

Hey guys! Yes, you are on my blog and you are seeing Windows content .. rs Today's post aims to change the lives of many people, just as my life improved a lot after I discovered how to solve a problem with a bluetooth headset failing and cutting the audio, which is something that has bothered me for ...
Read More

SQL Server - How to send the contents of a table or query in the body of the email as HTML

Hey guys !! In this post I come to bring a solution to a problem that almost every day I see in Whatsapp and Telegram groups: Send the content of one or more tables or queries in the body of an email as HTML. The procedure we are going to use in this post to take the contents of the table and transform it to HTML I ...
Read More

Microsoft Certified Trainer (MCT) FOR FREE until September 2020!

Personal Faala !! I would like to use this post to bring news to those who are STILL unaware: From April 10, 2020 to September 30, 2020, the enrollment and renewal of the $ 350 Microsoft Certified Trainer is DE GRACE!! That's right, free of charge! With that, Microsoft is democratizing the ...
Read More

SQL Server and Power BI: How to load Stored Procedure data into SQL Server with DirectQuery

Introduction Speak up! In this post I would like to share with you a situation that happened to me during a consultation with a client, where he had a need to have a Power BI report connected to a SQL Server database, but the data source was a Stored Procedure that did several data transformations internally and returned ...
Read More

80% DISCOUNT on the new DP-900 certification BETA test: Microsoft Azure Data Fundamentals

Hey guys! In this post I would like to share with you the fact that the NEW BETA DP-900 certification test: Microsoft Azure Data Fundamentals is 80% off for the first 300 people using this voucher. We will take advantage of this discount and the fact that we are in quarantine to study, improve our skills and the curriculum. Remembering that the evidence ...
Read More

My impressions and study material for the DA-100 certification exam - Analyzing Data with Microsoft Power BI (beta)

Hey guys! On Saturday, May 09th, I took the beta test "DA-05 - Analyzing Data with Microsoft Power BI (beta)", which is focused for business analysts and report / dashboard creators in Power BI, replacing the 100- 70 and 778-70 (yes, it is now only 779 test) and I wanted to share my impressions about the test and some study materials. Before ...
Read More

6 more lives I participated for you to watch on Power BI, Big Data, Data Careers, MySQL, Postgres and much more (25/04/2020 to 13/05/2020)

Hey guys! These last 3 weeks were a lot of work and several events where I participated and I believe that I managed to generate a lot of free and high quality content for you. I participated in six lives and would like to share with you, so you can watch if they are of interest to you, where it was covered about Power BI, QlikSense, Tableau, Big Data, ...
Read More

Microsoft Certification - Tips on how to take the test at home without a headache

Hey guys! On the holiday of 01/05, I took the beta test "DP-300 - Administering Relational Databases on Microsoft Azure", which is focused on DBA's who work or want to work with Azure and wanted to share tips to do a quiet test at home, especially because of COVID-19. The first challenge for this test was to perform the exam at home by ...
Read More

My impressions and DP-300 exam study material - Administering Relational Databases on Microsoft Azure (beta)

Hey guys! On the holiday of 01/05, I took the beta test "DP-300 - Administering Relational Databases on Microsoft Azure", which is focused on DBA's who work or want to work with Azure and wanted to share my impressions about the test and some study materials . First of all, if you want tips on how to take certification exams at home ...
Read More

Three FREE and Online Events that I will participate this week (20/04/2020 to 24/04/2020)

Hey guys! This week has already started with several appointments and a lot of free content for you. As I will participate in 3 lives this week, I decided to create a post just to publicize the three. 20/04 at 18:30 pm - PowerLive on Career Ready for a Power Live? ⚡ The MVP's, Fabrício Lima, CEO of Power Tuning, and Dirceu Resende, BI Consultant, will make a ...
Read More

AZ-900 certification test FOR FREE and beta tests of Azure, SQL Server and Power BI with 80% discount

Hey guys! In this post I would like to share with you the AZ-900 test, which is FREE, and some of the data area are in the Beta phase (80% discount). We will take advantage of these discounts and the fact that we are in quarantine to study, improve our skills and the curriculum. Remembering that the tests can be done ONLINE and ...
Read More

SQL Server - How to export the contents of a column to XML files

Hey guys!! On Thursday (09/04), I saw a very cool question in the Telegram SQL Server group - DBA, the largest group of DBA's and SQL Server Developers in the world, where the person had a table in the bank, where a column was from type XML, and he would like to export to the disk, each row of that table as an XML file ...
Read More

Online and Free Events I attended so far in 2020

Hey guys! After a long time without posting anything here, I am slowly returning, adapting to the various changes that are occurring in my life, including a change of country, and that is why it has been a little complicated to maintain the pace that I usually have here in posts, but I'll be right back "I'll take this article to ...
Read More

How was my lecture “I want to work in the data area. Where to start? ”At the 15th SQL Server ES Meetup

Hey guys! On 06/02/2019, I participated in the 15th SQL Server ES Meetup as the event organizer and also speaking with the theme "I want to work in the data area. Where to start?", Together with my friend Elisa Torres, who made her first lecture in the "SQL Server ES" community and it debuted in style: It made an excellent presentation and we hit ...
Read More

SQL Server - User only connects to the instance with sysadmin permission - Login failed for user 'teste'. Reason: Login-based server access validation failed with an infrastructure error

Hey guys! In this post, I would like to share an error that was reported in a Whatsapp group and that I personally had never seen before, which was the error message below and the report that users who used SQL authentication could only connect to the instance if they had sysadmin privileges. Login failed for user 'test'. Reason: ...
Read More

SQL Server - Using STRING_SPLIT to transform strings from a row into columns

Hey guys !! In this article I would like to bring a scenario that I am asked from time to time about how to solve, which is to transform strings from one row to columns, widely used for accounting accounts or cost center, for example, which was exactly the situation I was asked for. help for about 10 days and I decided to take some time to write ...
Read More

Interview for Live University (20/01) and Big Data Live on the Coding Night channel (24/01)

Hey guys! I hope everything is fine with you. Interview for Live University In this post, I would like to publicize an interview, conducted by Raffael Bilinski and that I had the privilege of participating on 20/01, for the Live University blog on "SQL Banks, because they are still one of the most used" , I could explain a little more about the language ...
Read More

Retrospective and Numbers 2019

Hey guys!! Last post of 2019 and can be no different: Retrospective. This year was an EXCELLENT year from a professional and technical point of view, although it was not good at a personal level. So I'll make a summary of everything that happened this year and come on! Looking coldly at just the numbers, I can say that 2019 was a year ...
Read More

TOP 10 2019 technical articles you liked best

Speak guys !! New Year's Eve coming up, everyone getting ready for New Year's Eve and so I would like to share with you the "TOP 10 Technical Articles of 2019 that you liked best", that is, the articles I published in 2019 and which you most viewed . I hope you enjoy this short list and that some article can be helpful ...
Read More

SQL Server - Encrypting passwords with the symmetric encryption functions ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE

Hey guys!! In this article, I would like to share with you a very interesting solution to protect and encrypt passwords in SQL Server that have the possibility of recovering the original password (as long as you know the salt used), which are the ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE functions available since SQL Server 2008. Do you like to study password security and ...
Read More

Is SQL Server Configuration Manager gone? Learn how to recover the shortcut

Hey guys!! In this very quick, objective and short post, I'll share a tip to open SQL Server Configuration Manager when you can't find its shortcut at all. From time to time, I will go into some client environment and when I try to find SQL Server Configuration Manager (that tool for configuring SQL services) and not ...
Read More

How SQL Server ES # 14 - SQL Server 2019 Was and Analyzing Big Data with Power BI

Hey guys! Last Thursday, 19/12, we held the 14th SQL Server ES Meetup (which is no longer an exclusive SQL Server event for a long time .. rs), held at Brooder - Innovation Hub, where Fabricio Lima and Luiz Lima showed the news of SQL Server 2019. Also, the people could watch my talk with Leandro ...
Read More

SQL Server - I changed Max Server Memory to 0 and now I can't connect to the instance

Hey guys! In this post I would like to share with you the solution of a problem that is even common to happen and from time to time someone comes up in the Whatsapp and Telegram groups with this problem, which is incorrect setting of the Max Server Memory parameter, setting a value too low and as a result you can no longer get ...
Read More

SQL Server - TCP Dynamic Ports vs TCP Port (Dynamic vs. Static Port)

Introduction Speak guys !! In this article I would like to share with you what I explained in a Telegram group, which is how SQL Server dynamic ports work, which many people have the misconception of how it works. While some people find that SQL Server has assigned a new port each time the service is ...
Read More

How was Data Tech Day 4, held in Belém do Pará by SQL Norte

Speak guys !! Last Saturday (07 / 12) I was in Belém do Pará to speak at the Data Tech Day 4 event, the largest data event in northern Brazil. The event was organized by Wiluey Sousa, Renata Cascaes, Sandro Cascaes, Sérgio Passos and Cássia Queiroz. Data Tech Day 4 has entered the history of Northern Brazil, bringing innovation and technologies ...
Read More

SQL Server - String or binary data would be truncated: What is it, how to identify the root cause and how to fix it

Hey guys! In this article, I want to explain to you exactly what the message "String or binary data would be truncated" means, how we can identify which string is causing the error, how to hide this error message (if you want), what the The change in sys.messages has impacted this theme from SQL Server 2016 + and more! So ...
Read More

SQL Server - How to send messages to Whatsapp contacts, groups and mailing lists via API

Hey guys! In this article, I'll share with you a solution I've been looking for a long time ago, which is how to send messages to Whatsapp contacts, groups and broadcast lists using WebRequests. I had done something similar using other tools, but never Whatsapp, which does not have an official API and is completely closed on that. Although ...
Read More

How was the Power Platform World Tour Sao Paulo (18 and 19 November 2019)

Hey guys! For those who follow me on social networks know that last week I attended the Power Platform World Tour São Paulo event, one of the largest Power BI events in the world (in addition to Power Automate and Power Apps). It was really an honor to have attended an event so well organized by MVP Pablo Peralta and other members ...
Read More

Home Office without Guesswork - Experiences of remote workers for over 1 year

Introduction Speak guys! For those who follow me on social networks, I should have seen a short post talking about MY view of the advantages and disadvantages of working on the 100% home office (remote) scheme. As this is a very relevant subject and I believe to be the future of various professions, I decided to deepen my analysis and spread this ...
Read More

SQL Server - Charindex: A Different Function to Break Split Strings

Hey guys! A little over 5 years after sharing with you the Split function code, which allows you to retrieve a part of the string broken by a delimiter, this time I come to share a new function, called charindexada, written by Brunno Araújo and kindly gave me the " copyright "to share it here on the blog. Interested in learning more about ...
Read More

Using PowerShell and the API with Agent or SSIS to Automate Updating a Power BI Data Set

Introduction Speak guys! In this article I am sharing with you, I would like to help you with a very common BI problem and need to update Power BI reporting data automatically more than once a day. Usually I see many scenarios where the BI team creates the automatic routine to update the data in the database.
Read More

SQL Server - Power BI Gateway issue using external IP or hostname to access database

Hey guys! In this post, I would like to share with you a situation that I have witnessed in some 4 clients in the last 3 months and always have to explain the same thing when users access the bank using an external IP or hostname, created so that users can connect to these data sources being outside the corporate network ...
Read More

SQL Saturday #906 - Sao Paulo (28 / 09) - The biggest event in Brazil on Microsoft technologies in the data area!

Speak guys !! This week is starting very busy and on Friday I will be leaving for Sao Paulo, because on Saturday of 28 / 09 / 2019 I will be speaking at SQL Saturday #906 - Sao Paulo 2019, Brazil's largest Microsoft technologies event in the area of Dice! There will be 42 lectures, divided into 6 trails, with most of the best known experts in the field ...
Read More

I spoke at the 3º Petrobras congress on productivity with Power BI!

Hey guys! In this post I would like to share with you my happiness to have the privilege of lecturing at the 3º Petrobras Power BI productivity congress, held yesterday (17 / 09) at EDICIN (Petrobras University), in Rio de Janeiro. It was a unique experience to be able to talk about Power BI Report Server to these great professionals, from the most diverse areas and in ...
Read More

SQL Saturday #900 - Victoria / ES: A dream that impacted more than 300 professionals and students

Speak guys !! Sorry for the rush of the last months, but it's hard to find time to post ... lol On 24 / 08 / 2019 we had another historic event in Espírito Santo. After SQL Day ES, which we organized last year and had 450 subscribers and 250 present, we were finally able to bring a global technology event to ES, SQL Saturday Vitória, ...
Read More

SQL Server - How to identify and replace “strange things” (hidden characters) in strings and tables

Hey guys! In this article I would like to share a situation that happens from time to time in my day to day consultancy, serving a number of different clients and environments, and that on Friday came back, that is when there are "weird things" in strings and tables (an allusion to the series "Stranger Things"), which is the occurrence of non ...
Read More

How I Got 1 MILLION with SQL Server

Hey guys! Today I would like to dedicate all this post to thank each of you who have visited, liked, commented and / or shared any of my articles. It's thanks to you that a lot has changed in my life over the last 2 years, especially on my blog, where I've been writing mostly about SQL Server since April of 2015. For me it is ...
Read More

Using Whatsapp and Telegram Safely and Avoiding Chat Intrusion or Leakage

Hey guys! In this article, which has nothing to do with SQL Server, I would like to share with you some VERY SIMPLE ways of using Whatsapp and Telegram SAFE and not being a victim of intrusions, as we are observing all the time in every newspaper. and news sites in Brazil, as several authorities ...
Read More

How was the 13 SQL Server ES meeting?

Hey guys! On 18 / 07, I had the honor of participating in the organization of the 13 SQL Server ES Meeting. At this event, we met for the first time at Pag !, which could be the beginning of a great partner of our technical community of Espírito Santo. At this event, we enjoyed the visit of SQL Server expert, Rodrigo Ribeiro Gomes, who lives in ...
Read More

SQL Server - Useful DBA Queries You Always Have to Look for on the Internet

Hey guys! In this article I would like to share with you a number of useful DBA day-to-day scripts that you always have to look for on the Internet when you need to make a particular query. My idea here is to make your life easier and have a multi-purpose article for you to bookmark in your browser and ...
Read More

SQL Server - Avoiding Queries on Certain Columns with Column Level Security (CLS)

Hello people! In this article I would like to demonstrate a very old feature (available since SQL Server 2005 - or earlier) that few people use or know exists, which is Column Level Security (CLS) or Column Level Security. Unlike Row Level Security, where the user views all columns but only a few rows, in the ...
Read More

SQL Server - Statement (s) could not be prepared. Case expressions may only be nested to level 10

Hey guys! In this article I would like to share with you a question that was sent to a Whatsapp group, which referred to the error message below when an analyst was trying to build a CASE with more than 10 conditions on a SELECT: Msg 8180, Level 16, State 1, Line 1 Statement (s) could not be prepared. Msg 125, Level 15, ...
Read More

SQL Server Tools Every DBA Should Know - Live in SQL North

Hey guys! Today I had the honor of participating in an edition of the well-known Quintas with Acai and Data, organized by the people of SQL North, with the theme "Tools for SQL Server that every DBA should know". help DBA in her daily life and I was able to test unusual tools or ...
Read More

How was the Power BI Roadshow - Brasilia 2019

Hey guys! In this post I would like to share with you a great joy, which was having the pleasure of speaking again at PowerBI Roadshow, this time in Brasilia - DF, with the theme "PowerBI: Cloud vs On-Premisses". I would like to thank Ruy Lacerda and Fernando Garcia for the invitation, as well as Fabricio Lima, for releasing me to participate in this ...
Read More

stpSecurity_Checklist - Best practices and security checklist for SQL Server

Hello guys! This is a special post for me, because it's my first post in English here in my blog. The main goal of this post is to share and show how to use the stpSecurity_Checklist Stored Procedure to perform a complete security checklist on your SQL Server instances. After a big change in my original SP (SQL Server - ...
Read More

SQL Server - What is bitwise operation and how to store multiple values ​​in the same column

Hey guys! In this article, I will show you what bitwise operation is available since SQL Server 2005, and how to store multiple distinct values ​​in the same column, which may even have value matching without the need to create a N: N table. to store the combinations. I've seen on some systems this approach of using bitwise operations ...
Read More

How was GPDI Data and Tech 2019 in Fortaleza / Ceará

Hey guys! In this post I would like to share with you the photos and materials of my presentation at GPDI Data and Tech 2019 in Fortaleza / Ceará, organized by Rafael Mendonça. And it was a pleasure to meet this angry guys from GPDI and Fortaleza. It is always a pleasure to share knowledge about what we like and try to bring some ...
Read More

How was DevelopersBR Live - SQL Server Security - Are you leaving the key under the rug?

Hey guys! Yesterday I had the honor and pleasure of attending a DevelopersBR group live where I was able to share my presentation "SQL Server Security - Are you leaving the key under the rug?" SQL Server ES #11, MVPConf LATAM 2019 and SQL Saturday #844 - Belo Horizonte ...
Read More

SQL Saturday #844 - Belo Horizonte

Hey guys! Today, 18 / 05 / 2019, I'm very happy to have attended SQL Saturday #844 - Belo Horizonte (MG), where I had the opportunity to talk about "Security in SQL Server: Are you leaving the key under the rug?" , where I demonstrated the various risks and security holes that we may have on a daily basis and we don't even notice, with some examples ...
Read More

Power BI - Now I am a Planilheiro !!

Faala guys! Today's post is nothing technical, but it is about a big change in my professional life and career, because through a partnership of #TeamFabricioLima, now I am a Planilheiro !! If you live on Mars or Saturn, and still don't know about the Channel of the Planilheiros, we are talking about the WORLD'S BIGGEST POWER BI CHANNEL, ...
Read More

SQL Server Security Checklist - An SP with more than 70 security items to validate your database.

Hey guys! In this article I would like to share with you a project that I have been developing since November of 2018 and today has more than 4.500 lines of code, which is a very complete Security Checklist (probably the most complete and comprehensive you will find on the Internet) , counting on more than 70 Security items to validate your bank ...
Read More

How Was the SQL Server ES 12 Meetup

Hey guys! On 11 / 05, I had the honor of participating in the organization of the 12 SQL Server ES Meeting. In this event, we met at the Catholic University of Vitória (UCV), which has been a great partner since the beginning of the technical community. Speaking of technical content, we are preparing two very interesting lectures. Ruy Lacerda, from Planilheiros, gave the lecture "Treatment of ...
Read More

SQL Server - How to Connect Using Dedicated Admin Connection (DAC) Connection Without SQL Browser

Speak guys !! In this article I would like to share with you a little study on how to connect using the Dedicated Admin Connection (DAC) connection without SQL Browser. This idea came from a question sent in my SQL Server Security - Module 1 course, where Fabiano Ferreira sent the following question: "in the stpchecklist_security script, there is a validation about ...
Read More

SQL Server - Permissions for Using OLE Automation (Yes, Documentation is Wrong)

Speak peopleALL !! In this very simple and quick article, I will share with you what permissions to use OLE Automation, a feature of SQL Server that lets you use Windows DLLs and APIs through Stored Procedures calls to perform various tasks within the database, such as Operations. with Files, use regular expressions (RegExp) and even make requests ...
Read More

How was Global Azure Bootcamp 2019 - Vitória / ES

Hey guys! In this post I would like to share with you how was the Global Azure Bootcamp 2019 - Vitória, again, along with great IT professionals here at ES and had the honor and pleasure to support the event in the organization and also lecturing. For those who do not know, this event is focused on Cloud solutions ...
Read More

How was MVPConf LATAM 2019

Faala guys! How long huh .. rs These last few weeks I've been very busy finishing my Safety Training - Module 1, which has been ANY WRONG and also been present at MVPConf LATAM 2019, where I had the pleasure and honor of speaking next to the myth. , legend, Junior Galvao, professional for several years and with a ...
Read More

How was SQL Server ES #11

Hey guys! On 23 / 03, I had the honor of participating in the organization of the 11 SQL Server ES Meeting, the first of 2019 after the "vacation" period. At this event, we met at TOTVS ES headquarters, in Vila Velha for the first time, in a very nice structure that they prepared specially to receive them. Speaking of technical content, we have prepared two ...
Read More

[Live] - How was the webcast “Seven Zombies and a LAW” - LGPD in the eyes of IT pros

Hey guys! In this post, I would like to share with you the recording of the live Seven "Zomis" and a LEI, which I invited at the invitation of Arthur Luz, together with reference professionals in the IT market, from various areas, to discuss LGPD, the Law General Data Protection, which aims to increase the privacy of personal data and prevent ...
Read More

General Personal Data Protection Act (LGPDP or LGPD) applied to SQL Server databases

Hey guys! In this article, I would like to address a very hot topic in the field of technology in general, which is the General Personal Data Protection Act (LGPDP or LGPD), a GDPR "cousin" that is in force in Europe. , and should become a reality in Brazil from August 2020, bringing several ...
Read More

#TeamFabricioLima SQL Server, Power BI, and Azure ONLINE Courses and Training: Boost Your Career!

Hey guys! ATTENTION!!! New in the area !!! As you know, I work at #TeamFabricioLima and after a few months of hard work, we have lovingly prepared a new ONLINE Training platform for you. Our specialists and partners will provide you with differentiated courses with a lot of experience and everyday cases. To open with a golden key, we already have the first available ...
Read More

[ONLINE & FREE Event] - On-premises vs Cloud: Advantages and Disadvantages (14 / 03 / 2019 at 20: 30)

Hey guys! All right with you ? TODAY, 14 March 2019, at 20: 30, we will be bringing together big beasts of the data community for a roundtable on the subject of On-premises vs. Cloud: Advantages and Disadvantages, openly debating this big dilemma by setting up a data architecture. IT, whether for an application, a company infrastructure, database, ...
Read More

SQL Server - When you should use ORDER BY in the query and when you should not use it at all!

Hey guys! In this article I am going to demonstrate to you when to use ORDER BY and when we should not use it at all, because it has no effect on practice and just makes our query longer and more resource consuming. The main purpose of this article is to break the myth that data is physically ordered in the table when ...
Read More

SQL Server - Understanding the Risks of TRUSTWORTHY Property Enabled in a Database

Hey guys! In another security article, which is the subject of my MVPConf LATAM 2019 talk, I will share with you the risks of TRUSTWORTHY ownership of a SQL Server database, which is widely used in environments that use EXTERNAL_ACCESS permission-level SQLCLR libraries or UNRESTRICTED. If you have a SQLCLR library and have enabled the ...
Read More

SQL Server - How to Avoid SQL Injection? Stop using Dynamic Query as EXEC (@Query). Now.

Hey guys! In this article I would like to share with you the risks of using Dynamic Query in SQL Server through the EXECUTE command and demonstrating safe ways to use dynamic query that are not susceptible to SQL Injection attacks. What is Dynamic Query? Feature widely used in system and database routines, ...
Read More

[Live] - Power BI Service vs Power BI Report Server - 27 / 02 / 2019 at 20: 30

Faaaala Guys! All right with you? Going to invite you to another pre-Carnival live (FREE), where we will hold a round table, with open debate about the main differences between Power BI Service x Report Server, as well as when to use one another, licensing, advantages and disadvantages of each solution and more! And at this round table, we will have the ...
Read More

SQL Server - How to identify and collect time consuming query information using Extended Events (XE)

Hey guys! In this article I would like to share with you how to identify and collect long query information using Extended Events (XE), in a very similar article to SQL Server - How to identify and collect long query information using Trace (SQL Server Profiler), which uses Profiler technology (Trace). What motivated me to write this article was ...
Read More

SQL Server - Using Calculated Columns (or Computed Columns) for Performance Tuning

Hey guys!!! In this article, I would like to share with you something I see a lot in everyday life when I am performing Tuning consulting, which are time consuming, I / O and CPU consuming queries that use WHERE or JOIN functions in tables with many records and how we can use a very simple column indexing technique ...
Read More

SQL Server - How to Avoid and Protect Against Ransomware Attacks Like WannaCry on Your Database Server

Hey guys! In this blog article number 350, I would like to share with you my experience during several tests I did about Ransomwares on SQL Server database servers, such as WannaCry, which I downloaded and "infected" my VM just to perform these tests, understand how it works and how we can protect ourselves against this kind ...
Read More

VMware Player - Using independent nonpersistent disk mode

Hey guys! In this article today I will talk about a very different topic from what I usually address here on the site, which is VMware Player, a free virtualization software from VMware that I use to create my study VMs, labs and where I create content for this blog. . A long time ago, when I started in this world of virtualization, I ...
Read More

SQL Server - How to Avoid Brute Force Attacks on Your Database

Hey guys! In this article today I will demonstrate how brute force attacks occur on SQL Server and how to try to defend against such attacks. What is Brute force attack Brute force attack is the simplest and most time-consuming technique to break into systems and databases. It consists of using bases of ...
Read More

SQL Server - Performance Tuning Tips: What is the Difference Between Seek Predicate and Predicate?

Hey guys! One more Performance Tuning tip for you, where I will explain and comment on the difference between Seek Predicate and Predicate, where they may look the same, but make a big difference in the performance of your queries. I believe this article should answer a very common question of who is starting in the area right now: The order of the columns in ...
Read More

[FREE ONLINE Event] - 07 / 02 / 2019 at 20: 00 - Data Architecture & Governance in BI Projects with Power BI

Hey guys! In this post I would like to publicize the event that I will participate on 07 / 02 / 2019 (tomorrow .. lol) at 20: 00, on Rafael Mendonça's channel, alongside Rafael himself and also Claudio Bonel and Arthur Luz, where we will talk about Data Architecture and Governance in BI Projects with Power BI, showing that a project ...
Read More

SQL Server - Performance Tuning Tips: Implicit Conversion? NEVER!

Hey guys! All in peace, right ?! In this article I would like to comment on a query performance problem that we found a lot here in our daily life at Fabrício Lima - BD Solutions, one of the best and most recognized Performance Tuning companies in Brazil. We're talking about something that is often terribly simple to solve ...
Read More

SQL Server - NOLOCK vs READPAST: Do you know the difference between the two?

Hey guys! In this article I would like to demonstrate in practice the use of 2 query hints commonly used by developers to avoid data reading locks, which are NOLOCK and READPAST, and effectively demonstrate the effect of these hints on a query. The idea of ​​writing this article came from a question sent in the group "SQL ...
Read More

SQL Server 2014 - How to Estimate Progress and How Much Time to Create an Index

Hey guys! In this article, I'd like to share with you a very simple, but very interesting script that helps you estimate the progress of creating an index through the sys.dm_exec_query_profiles DMV, available from SQL Server 2014. If you want to estimate the tempo and how much time is left to finish your BACKUP, RESTORE, or DBCC command, give ...
Read More

SQL Server - How to identify how to change the default schema for database users

Faaala guys !! In this post I would like to address a very common subject in DBA's everyday life, which is the default schema of users in the database, which may or may not differ from the standard (dbo), as defined by TI. What is schema in SQL Server? What is it for? Before proceeding with the main theme of this ...
Read More

[FREE ONLINE Event] - Power BI Web Scraping - Capturing MVP Information Brazil - 24 / 01 / 2019 at 20: 30

Hey guys! In this post I would like to disclose the event that I will participate on 24 / 01 / 2019 (tomorrow .. lol) at 20: 30, on Rafael Mendonça's channel, next to Rafael himself and also Orlando Gomes, where we will talk a little bit about the Microsoft MVP program in Brazil and of course demonstrating live how we 3 participate in the development of ...
Read More

SQL Server - How to use auditing to map actual required permissions on a user

Hey guys! In this article, I will demonstrate how to use auditing to map a user's actual required permissions, identifying everything that that user actually did in the database over the observed time, to generate a script granting only the permissions he really needs, thus eliminating need to have integration users and users other than the main ...
Read More

SQL Server - How to Identify Instance Initialization Parameters Using T-SQL

Hey guys! In this post today I will give a very quick tip, which is how to identify the instance initialization parameters using T-SQL, very useful scenario when you have access to connects in the instance, but do not have access to the server operating system. Using SQL Server Configuring Manager For those who don't know, the SQL Server service has ...
Read More

[Onsite Event] - MVPConf LATAM 2019 - 12 and 13 April (Sao Paulo)

Hey guys! Here comes MVPConf LATAM 2019, the biggest technology event created by MVPs in the world! For those who do not know, MVPConf LATAM is created and maintained by the largest influencers of technical communities in Brazil and aims to bring together all people who have a passion for knowledge and want to expand their networking. MVPConf LATAM brings together the largest number ...
Read More

[Online Event] - Roundtable #17 - Relational Banks x NoSQL - Coding Night

Speak guys !! Next Friday (11 / 01), at 21: 30, I will be attending the online event "Round Table #17 - Relational Banks x NoSQL - Coding Night", together with great and recognized professionals from the technical community. Attendance confirmed: - André Secco (Microsoft MVP, MTAC) - Luiz Carlos Faria (Microsoft MVP, MTAC) - Renato Groffe (Microsoft MVP, MTAC) - Thiago Adriano (Microsoft MVP) - ...
Read More

SQL Server - What is the difference between @@ ERROR and ERROR_NUMBER ()?

Hey guys! 2019's first post! In this article, I will explain the difference between @@ ERROR and the ERROR_NUMBER () function, which in theory bring the same result, that is, the error number caused by some operation in the current session. The idea of ​​writing about it came from a doubt of a Nigerian in the SQL Server - DBA group of Telegram and ...
Read More

Retrospective 2018 - What a year !!

Hey guys! In the last post from 2018, I would like to thank you all very much, who comment, like, rate my articles, visit and share on their social networks. It is a great privilege to have a space to share the things I study and test with so many people. Reading your comments makes me very excited to continue ...
Read More

SQL Server Express - CHALLENGE: Is it possible to exceed the 10 GB limit of data on a basis?

Hey guys! In the last 2018 technical post, I would like to ask you a challenging question: Is it possible to exceed the 10 GB data limit on a basis? As I commented in the article SQL Server Express and Developer - How far can we use the free versions of SQL Server ?, the Express version has a number of technical limitations, ...
Read More

What has changed in SQL Server 2012 compared to T-SQL - In Developers' view

Hey guys! All right ? In today's post, I will share with you some research I have been doing for some time about the new features of SQL Server with each release, focusing on query developers and database routines. In the environments I work in, I see that many end up "reinventing the wheel" or creating UDF functions ...
Read More

SQL Server - How to Identify Columns and Parameters Data Type for Tables, Views, Stored Procedures, and Functions

Hey guys! In this quick post today, I would like to share with you how to use DMV's to identify the data type of columns in tables, views, and return tables in Table Valued Functions (TVF) functions and also the data type in parameters of Functions and Stored Procedures in SQL Server, both custom and primitive types ...
Read More

Power BI - Who are the Microsoft MVP's in Brazil?

Hey guys! In one of the latest posts from 2018, I would like to share with you a Power BI dashboard with information from all MVP's in Brazil, which is available on the Find a MVP portal. What is MVP? For those unfamiliar with the Microsoft Most Valuable Professional (MVP) program, it recognizes community leaders who have already demonstrated a commitment ...
Read More

SQL Server - How to disable “sa” login minimizing impacts

Speak, my dear readers! In this article, I would like to extend the security issue a bit and share with you how to disable "sa" login minimizing impacts. As I already commented in the SQL Server article - How to enable / enable sa user, it is a good security practice to keep user "sa" deactivated and renamed, since this user ...
Read More

SQL Server Reporting Services (SSRS) - Error Querying Data: There was a problem retrieving data from the Report Server Web service

Hey guys! While Creating My SQL Server Reporting Services (SSRS) Article - Which Reports Are Most Accessed? And which are not being used? (which I just published) I found some errors while testing database connectivity through the Power BI Report Server, which I had installed a few minutes ago for this article, where ...
Read More

SQL Server Reporting Services (SSRS) - Which reports are most accessed? And which are not being used?

Hello guys! In this article, I'd like to share with you how to use SQL Server Reporting Services (SSRS) catalog views and tables to identify which reports are most accessed and which are not being used. This need eventually arose for me while serving a customer, who is in the process of migrating Reporting Services RDL reports to ...
Read More

How was Microsoft Ignite The Tour (Sao Paulo) and MVP Get Together 2018

Hello guys! In this post, I would like to share with you how my experience was on Microsoft Ignite The Tour (Sao Paulo) and MVP Get Together 2018, held in the 2 days following Ignite at Microsoft, as I announced I would participate in this post on here. Held in various locations around the world, São Paulo was the 2º on the list ...
Read More

SQL Server - How to Hide Databases from Unauthorized Users

Hey guys!!! In this article I would like to demonstrate how to improve the security of your SQL Server instances very simply and using a combination of Concealment and Access Restriction techniques (remembering that in Security we have 3 main techniques: Concealment, Access Restriction and Encryption) . What I would like to discuss in this article is about the privilege ...
Read More

SQL Server - How to identify queries that use a particular index through plan cache

Hey guys! In this article I would like to share with you a query that allows you to identify which queries are using a particular index. This type of analysis can be especially useful for identifying which queries that are indexing have the most scan operations you are looking for, for example. The idea of ​​this post came from a doubt to ...
Read More

SQL Server - The activated proc '[dbo]. [Sp_syspolicy_events_reader]' running on queue 'msdb.dbo.syspolicy_event_queue'

Hey guys! In this article I would like to share a situation that occurred during a customer service where I work, that is, another real case of daily life. We had a database instance that went into suspect mode (nothing to do with this post) and when I went to parse the SQL Server log for more information ...
Read More

SQL Server - How to identify locks, blocks, and blocking sessions

Hey guys! In this article, I would like to demonstrate how to use SQL Server DMV's to identify locks, blocks, and blocking sessions in your environment. I had the idea of ​​writing this post, when I was serving a client in person on a BI demand and from my side I saw some users complaining about "slow" problems in their reports, when what ...
Read More

Microsoft Ignite 2018 - The Tour (Sao Paulo) - See You There!

Hey guys! From 11 to 14, I will be in São Paulo for Microsoft Ignite 2018 - The Tour, together with Fabrício Lima, where we will update with the news that Microsoft is making available in several areas, such as AI, Big Data, Azure SQL Database, Security and more! Who is there at the event and wants to talk ...
Read More

How was Data Tuesdays #38 - The Modern and Automated DBA - DBA Brasil

Hey guys! In this post I would like to share with you my excellent experience participating in the Tuesdays of Data #38, event held almost every Tuesday by Fabio Cotrim, where I could bring to the participants, my view on the Modern and Automated DBA, as it is this professional's new profile, some new tasks and activities he has ...
Read More

FREE Database Check-Up + Security Analysis: Do You Need It?

Hello guys! That's fine with you, right? In this post I would like to present to you something really different, which is a FREE Check-up of your database + security analysis, processes developed and performed by the consulting team Fabrício Lima - Database Solutions, specialists in the area of database using best practices ...
Read More

Is your SQL Server up to date? Returning the latest version of Service Pack (SP) or Cumulative Update (CU) for your instance

Hey guys! In this article I want to bring you a solution I created yesterday to include in a checklist I'm developing, which allows me to identify the latest version of Service Pack (SP) and Cumulative Update (CU) for the version of SQL Server where I am running this code. To identify the latest updates for each version, I will use the ...
Read More

SQL Server + AD Authentication - Kerberos + NTLM = Login failed for user 'NT AUTHORITY \ ANONYMOUS LOGON'

Hey guys! In this article, I would like to document and share an experience I had TODAY, in the consulting where I work, in which we had a problem with a client that caused all Linked Servers pointing to a particular instance to start showing the error below, for both trying to query data and trying to alter objects (like Stored Procedures) ...
Read More

How was the 10 SQL Server ES Meeting - From Zero to Everything with Tableau and SQL Server

Hey guys! All right with you ? Last Saturday, 24 / 11, I had the pleasure of participating once again in organizing the monthly SQL Server ES meeting, this time, our 10th meeting (in addition to SQL Day). In this event, we brought from São Paulo, BI specialist Nilton Kazuyuki Ueda, who held a workshop "From Zero to Everything ...
Read More

How was SQL Server 2019 What's New Live on the dotNET Channel?

Hey guys! As I had already announced in the post Live on the .NET Channel - SQL Server 2019 News (22 / 11 / 2018 at 21: 30), yesterday I participated in a dotNET Channel live, invited by MVP Renato Groffe. The live was very well organized by Groffe (aka BOT) and many very interesting questions were asked by the almost 550 people who ...
Read More

How was the Power BI RoadShow #5 - Vila Velha / ES

Hey guys! That's fine with you, right ?! Today I had a golden opportunity to speak at the Power BI RoadShow #5 - Vila Velha / ES, at the invitation of Fernando Garcia and Ruy Lacerda, creators of Planilheiros, Latin America's largest Power BI channel on Youtube and soon, The largest in the world. 08 Event Schedule: 00 - ...
Read More

SQL Server - How to use EXECUTE AS to execute commands as another user (Impersonate login and user)

Hey guys! In this article I would like to share with you a feature available since SQL Server 2005, which allows the execution of T-SQL codes on behalf of another user. I'm talking about the EXECUTE AS command. With this high demand for security that companies are looking for, driven by demands for GDPR compliance, organizations are often leveraging ...
Read More

SQL Server 2016 - How to Archive Historical Tables in Azure with Stretch Database

Introduction In today's post, I would like to demonstrate to you how to archive historical tables in Azure with Stretch Database, available from SQL Server 2016. Stretch Database is a feature of the DBMS that migrates your cold data (data that is not changed or consulted frequently) automatically, transparently and securely to the data.
Read More

How was the Microsoft Certification Data Platform Webinar (Data Platform)

Hey guys! I would like to share with you how it was the experience of hosting the Microsoft Certification Webinar in the Data Platform, an online event that I attended with MVP and MCSE Caio Amante, MCSE Danilo Cardoso, MCSE Maruan Aawar and MCSE Meirieli Ribeiro. It was really a really cool experience to share some of my experience ...
Read More

.NET Channel Live - SQL Server 2019 What's New (22 / 11 / 2018 at 21: 30)

Hey guys! On 22 / 11 / 2018, at 21: 30, I will participate in a live held by the .NET Channel where I will be able to comment and demonstrate the main news of SQL Server 2019. To make your registration, click this link here. Be sure to attend the event and stay on top of SQL Server 2019 news! The event is ONLINE and FREE! ...
Read More

Video - Power BI vs. Reporting Services: Who's Better?

Hey guys! In this post, I would like to share with you the video of the online webcast that we held on 20 / 10, at the Info TI Seminar event, which I had already shared with you that would participate in this post here. I would like to thank the invitation of the event organizer, Gilberto Breyer Júnior, who, by the way, did a great job in organizing ...
Read More

SQL Server 2008 - How to Encrypt Your Data Using Transparent Data Encryption (TDE)

Introduction With the advent of GDPR, data security concerns for businesses are growing, and an area that was once overlooked is in evidence more than ever now. As a result, IT professionals, particularly DBAs, have been looking for ways to reduce the risk of data exposure ...
Read More

SQL Server 2016 - How to Encrypt Your Data Using Always Encrypted

Hey guys! Ready for one more article? Introduction With the advent of GDPR, data security concerns for businesses are growing, and an area that was once overlooked is in evidence more than ever now. As a result, IT professionals, especially DBAs, have been looking for ways ...
Read More

New - Calendar with ALL Data Platform Community Events

Hey guys! Today I bring you one more news, which is a Data Platform Event Agenda, where everyone will have access to the online and in-person events that occur in the Microsoft data community (Power BI, SQL Server, SSIS, SSRS, SSAS, Azure, CosmosDB, etc). This agenda is maintained by all community leaders and speakers from ...
Read More

How Was 1º SQL Server Meetup at Microsoft - Slides and Material

Hey guys! On 16 / 10, I had the pleasure of speaking at the 1º SQL Server Meetup, held at Microsoft headquarters, in São Paulo, at the invitation of the event organizer, Caio Amante. No words to describe the satisfaction and happiness in being able to share a little about my vision of the role of dba these days, to know personally several ...
Read More

SQL Server - How to Change Owner of All Jobs for a User in SQL Agent

Hello guys! Let's go for another article. Introduction Working as a DBA in environments where bank BI developers and analysts are not sysadmin (amen), but create jobs in the bank using their own users (SQL Server - Understanding SQL Agent Permissions and Roles (SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole) )), do you know how common are demands of change of ...
Read More

SQL Server - How to identify data conversion errors using TRY_CAST, TRY_CONVERT, TRY_PARSE, ISNUMERIC, and ISDATE

Hey guys! In this article, I would like to share with you a question you posted a few minutes ago in a Whatsapp group about a very common SQL Server problem, especially in the areas of BI and / or development, which are errors. data type conversion, that is, trying to convert a ...
Read More

SQL Server 2019 - Knowing the Changes in Row Estimation in Table Variables

Hey guys! In today's post, I'd like to share this video I just made, demonstrating in practice the changes we've had in SQL Server 2019 regarding row estimation in table-type variables, where the query optimizer always estimated 1 row , independent of data volume, when using table-like variables, making it possible to ...
Read More

SSRS - Reporting Services in your company without paying anything? Meet SQL Server Express with Advanced Services

Hey guys! In this article, I would like to comment on some very interesting information that Vithor Silva shared in a Whatsapp group in which I participate (he has given me the "rights" to this post .. kkkkkkk) and that many people (including myself) do not have / were aware that is the use of SQL Server Reporting Services in corporate environments for free, ...
Read More

1º SQL Server Meetup at Microsoft - 16 / 10 / 2018 - 19h (On-Site Event in São Paulo)

Hey guys! On 16 / 10 / 2018, at 19h, I will attend the 1º Meetup SQL Server, in São Paulo (capital), a face-to-face event at Microsoft headquarters where I, Fabiano Amorim and Thiago Alencar (two monster guys in SQL Server) ) We will be discussing issues that are currently hot, such as DBA Tools and SQL Server 2019. This is a ...
Read More

Webcast - 300 Celebration posts + SQL Server 2019 What's New (04 / 10 / 2018 - 21h)

Hey guys! All right with you right? In today's post, I would first like to thank everyone who came here and enjoyed it, commented, showed it to a colleague, who is a regular or shared one of my articles on some social network. It is these feedbacks that motivate me to continue writing, studying and seeking content ...
Read More

SQL Server 2019 - News and Features List

Hey guys! In this mega expected post, I will comment on the news we can expect from SQL Server 2019, which has long since gone from being a Database Management System (DBMS) to becoming a true Microsoft data platform, joining database. data, BI, Machine Learning and Big Data / Analytics. I would like to see news about the ...
Read More

What has changed in SQL Server 2008 compared to T-SQL - In Developers' view

Hey guys! All right ? In today's post, I will share with you some research I have been doing for some time about the new features of SQL Server with each release, focusing on query developers and database routines. In the environments I work in, I see that many end up "reinventing the wheel" or creating UDF functions ...
Read More

SQL Server 2012 - How to Create Data Pagination in Query Results with OFFSET and FETCH

Hey guys! In this post, I would like to demonstrate to you how to create data pagination in SQL Server, so that only a limited amount of records are returned by queries, so less data is processed on output and queries have a response time. smaller. This scenario, which is quite common in applications, which usually ...
Read More

SQL Server - How to use MERGE command to enter, update and delete data with 1 command only

Hello guys! In this post, I would like to demonstrate to you how to use the MERGE command, available since SQL Server 2008, to perform INSERT and UPDATE commands between two tables in a single statement. For the examples in this post, I will use a base that I will create using the script below: IF (OBJECT_ID ('dbo.Sale') IS NOT NULL) DROP TABLE dbo.Sale CREATE TABLE ...
Read More

SQL Server - Using the 3226 traceflag to prevent writing backup informational messages to the SQL Server log

Hey guys! In this post, I would like to share with you the solution of a question sent to me today on Telegram, where they asked how to hide the informational messages that the BACKUP DATABASE command produces while it is running and also writes them to the SQL Server log. . Introduction If you have never backed up SQL ...
Read More

SQL Server - How to Pass a Table as a Parameter for Stored Procedures and Functions

Hey guys! In this post today I would like to demonstrate a feature available since SQL Server 2008 and that I see very few day-to-day use cases in the companies I worked with, which is the use of "tables" as parameters for Stored Procedures. Introduction If you are a guy who likes gambiarras, I'm sure you thought I was ...
Read More

FREE and ONLINE Event: IT Info Seminar - 20 / 10 / 2018

Hey guys! Today I had the pleasure of speaking at the 9 SQL Server ES Meeting: Power BI, Reporting Services and Performance Tuning - 15 / 09 / 2018, where I could share how the event was on this link here, but I don't want to stop there. On 20 / 10 / 2018, I will attend the Info TI Seminar, a free and ONLINE event, which aims to bring together professionals ...
Read More

How was the 9 SQL Server ES (Power BI x Reporting Services and Performance Tuning) meeting?

Hey guys! All quiet? On 15 / 09, I had the pleasure of participating in the organization of the 9 meeting of PASS Local Group SQL Server ES and lecturing on the theme "Power BI x Reporting Services: Who's Better?", Along with MVP Fabrício Lima, who introduced us the lecture "Step by step of how I made a Tuning in 7 real queries of ...
Read More

SQL Server - How to use Management Studio debug

Hey guys! In this video that I will share with you, I would like to demonstrate a very interesting feature in the development of T-SQL queries and code, especially functions and Stored Procedures, which is DEBUG. A very common and popular tool for developers (and almost unused when context is programming in the database), Debug allows you to ...
Read More

9 SQL Server ES Meeting: Power BI, Reporting Services, and Performance Tuning - 15 / 09 / 2018

Hey everybody, all beauty with you? The 9th Meeting of the Local Group SQL Server ES is coming and it's very cool! This time, we will have as speakers Fabrício Lima - MVP and me, Dirceu Resende. This is an excellent opportunity to learn more about Power BI and Reporting Services, two competing Microsoft BI solutions, ...
Read More

SQL Server and Power BI - How to Analyze and Generate Whatsapp Group Conversation Statistics

Hey guys! In this blog post, I'd like to share with you a solution that allows you to analyze and generate Whatsapp group conversation statistics using SQL Server and Power BI. Unlike Telegram, which has an integration API and task automation bots, Whatsapp is fully delayed closed and does not allow any kind of legal integration ...
Read More

SQL Server Reporting Services (SSRS) - How to log the report view and identify which user is accessing

Hey guys! In this post I would like to show you how to log the report view and identify which user is accessing, that is, how to write to a database table, which user is accessing a particular report and when it was done. The idea of ​​this post came from a doubt in a group of Telegram and I found ...
Read More

How was 1º SQL Day - ES?

Hey guys! All quiet? On 04 / 08 / 2018, I had the great pleasure of participating in the organization of 1º SQL Day ES, PASS Local Group SQL Server ES, together with Tiago Neves, Fabrício Lima and Vithor Silva, where we had the presence of a large team of speakers recognized throughout Brazil for their excellent technical skills and contributions to the ...
Read More

[Video] - Introduction to SQLCLR

Hey guys! All in peace? In this post, I'd like to share with you a video I recorded explaining what SQLCLR is, the advantages and disadvantages of using this solution, and demonstrating how to create your first SQL Database Project in Visual Studio. Introduction to SQLCLR I had already lectured on this topic of DEV-ES Conf 2017 and solved ...
Read More

MS SQL Server - A new portal for technical articles and information about events, webcasts and lectures.

Hey guys! I would like to share with you a very cool news that I developed together with the technical community of Data Platform, which is the MS SQL Server portal. This new portal acts as an aggregator of technical articles from various data specialists, separated by technology (SQL Server, Power BI, Reporting Services, Azure, Analysis Services, etc.) ...
Read More

SQL Server - How to create code versioning of your Stored Procedures in HTML and with change comments

Hey guys! In this article I will share with you how to create a code version of your Stored Procedures in HTML and with comments on the change. In the past, I had already created the post How to Create an Audit Trigger to log object manipulation in SQL Server, which shows how to write to a database table, all ...
Read More

SQL Server - How to integrate database with Slack and send messages using Python and sp_execute_external_script

Faaaaala guys !! Today I am very excited about this post. My first using Python on SQL Server 2017, and you can expect a lot more. On February 04 of 2017, I had released the post SQL Server - How to Integrate Database with Slack and Send Messages Using CLR (C #) and this ...
Read More

SQL Server - Using CROSS JOIN (Cartesian Product) to Create Rows in Query Result

Hey guys! In this post I would like to share with you the operation of an operator that I rarely see anyone using, which is CROSS JOIN, and which in certain scenarios may be useful. What is CROSS JOIN? CROSS JOIN returns the combination of each row between the tables involved, also known as Product ...
Read More

SQL Server - How to Identify a Slow or “Heavy” Query in Your Database

Hello guys! In this post, I would like to demonstrate some ways to identify slow or heavy queries that end up consuming a lot of machine resources and take a long time to return results, whether due to excessive CPU, memory or disk usage. The purpose of this article is to help you identify queries that have potential performance issues. An ...
Read More

SQL Server - How to retrieve source code for deleted objects (View, Stored Procedure, Function, and Trigger)

Hey guys! All right ? In this article I will share with you a life-saving solution in desperation when someone deletes a database object (View, Stored Procedure, View or Function), you don't have a database object versioning routine and you need to try to retrieve this object quickly without having to ...
Read More

SQL Server - How to enable / enable sa user

Hey guys! All in peace right? Through this article, I am going to open a new category on my blog called "What not to do", which are some database management bad practice articles that I am going to start gathering here and so, show you all that you shouldn't Be done. To debut this category, I will write ...
Read More

Save the date: Here comes 1º SQL Day ES - A Whole Day of Learning (04 / 08 / 2018)

Hey guys !!!! On 04 / 08, we are organizing the first SQL Day of ES, where we will hold a full day of various lectures, bringing in professional specialists from various specialties (DBA's, Data Engineers, Data Scientists, BI Analysts, etc.), recognized all over Brazil. At this event we had the idea of ​​asking (it will not be mandatory) that each participant bring some food ...
Read More

Congratulations 2018-2019 Microsoft MVP!

Hey guys! Today is a very special and happy day for me, because I was watching a series on NetFlix and when I get my phone, I see this email: It is a great satisfaction and honor to receive such a cool and important prize, and with this, I join a team of excellent data professionals by ...
Read More

SQL Server - Knowing Durable vs Non-durable OLTP (Hekaton) In-Memory Tables

What's up guys! Ready for one more tip? Introduction In this article I would like to demonstrate to you some ways to load data quickly and efficiently into the database using as little logging as possible. This is especially useful for staging scenarios in BI / Data warehouse processes where data must be loaded quickly and a possible ...
Read More

SQL Server - How to calculate the distance between two locations using latitude and longitude (without API)

Hey guys! In this post, I will show you how to calculate the distance between two locations using latitude and longitude (without API). In March 2017, I demonstrated how to do this using the Google Maps API in the post SQL Server - How to calculate the distance between two points using the Google API (zip code, address or latitute and longitude), but ...
Read More

How was the 8th PASS Local Group SQL Server ES (Azure CosmosDB) meeting?

Hey guys! All quiet? On 16 / 06, I had the pleasure of participating in the 8 PASS Local Group SQL Server ES meeting, next to Tiago Neves, where we had the presence of MVP Igor Santos and Daniel Braga as speakers. Igor is DevSQL Local Group Leader in Rio de Janeiro, SQL Saturday speaker in Brazil, currently works as a ...
Read More

SQL Server - Rounding Numbers Following ABNT NBR 5891

Hey guys! In this article, I will share with you some tips and explanations about number rounding in SQL Server using ROUND, FLOOR, CEILING and also using a custom function to meet ABNT NBR 5891 definitions. Introduction Present in virtually all information systems, rounding functions are widely used to deal with fractional numbers and ...
Read More

SQL Server - How to Compress and Unzip Files and Directories Using 7-zip and xp_cmdshell or SQLCLR (C #)

Hey guys! In this post I would like to share some solutions on how to compress and unzip files and directories through SQL Server. This is especially useful for creating ETL routines where you need to utilize these features using T-SQL scripts or even for compressing backup files generated in the Express version (which does not support compressed backups). If you have ...
Read More

8 Chapter Meeting SQL Server ES (PASS Local Group of Espírito Santo) - 16 / 06 / 2018

Hey everybody, all beauty with you? The 8th Meeting of the Local Group SQL Server ES is coming and is a must see! This time, we will have as speaker Igor Santos - MVP, directly from Rio de Janeiro, bringing a very cool lecture for those who would not have the opportunity to travel to RJ or SP. Igor is Local Group Leader of ...
Read More

SQL Server Express and Developer - How far can we use free versions of SQL Server?

Hello guys! After much reading and answering questions in Whatsapp and Telegram groups about limitations of SQL Server Express and Developer, I decided to write this article explaining about these two free versions *** of SQL Server and trying to answer any questions. SQL Server Developer Edition Is SQL Server Developer Edition free? On 31 / 03 / 2016, it was announced ...
Read More

SQL Server - How to identify deprecated features being used in instance (Extended Events - XE)

Hey guys! One issue I don't often see much in everyday life is DBA's and developers' concern about the use of features marked "deprecated" by Microsoft, that is, they are set to be removed in some future version of SQL Server according to Microsoft roadmap. Although we can observe that some features are from the ...
Read More

SQL Server - How to capture data from a webpage (Webscraping with HttpRequest) and insert data into database with SQLCLR (C #)

Hey guys! All right with you ? In this post I would like to share a solution that I needed to develop in my company to do a web scraping and extract agency data from the Post Office website (http://www2.correios.com.br/sistemas/agencias/). As you might imagine, to achieve this solution and bring data into my database quickly, I used my old ...
Read More

SQL Server - How to use regular expressions (RegExp) in your database

Hello guys! Excited for another post ??? In this article I would like to show you how we can use regular expressions (RegExp) in your database through queries, whether to create some monitoring or even to create BI reports and indicators or some system. The regular expression (or the regex or regexp foreigners, short for regular English ...
Read More

HALF A MILLION

Faaala guys! All in peace? Hope so! In this non technical post, I would just like to thank everyone who visits or has visited my blog. Know that I do this work here with great affection and willingness to share the things I learn in my daily life, so that it can help other people who may have ...
Read More

SQL Server - How to find out how long the instance has been online or when the instance was started

Hey guys! All in peace?? With this article, I would like to show you how to find out how long the instance has been online or when the instance was started. This information can be used in a variety of ways, such as monitoring when the SQL service was restarted and then starting an audit of who restarted or why ...
Read More

How was the 7th PASS Local Group SQL Server ES meeting?

Hey guys! All quiet? On 05 / 05, I had the pleasure of attending the 7 PASS Local Group SQL Server ES meeting, along with Tiago Neves and Fabrício Lima where we had the presence of MVP Vitor Fava as a speaker. Vitor is Local Group Leader of SQLManiacs SP, one of the largest Local Groups in Brazil. He is a speaker of ...
Read More

SQL Server - How to view all return message from Job execution (even when it exceeds 4000 characters)

Hey guys! Is everything great with you? In this post, I would like to share with you how to view the entire Job execution return message when the job output is longer than 4.000 characters. Understanding the Scenario and the Problem If we look at the structure of the msdb.dbo.sysjobhistory table, which is where job log messages are written, ...
Read More

SQL Server - Study Material for the 70-764 Administering a SQL Database Certification Exam

Hey guys! All quiet? In this post I would like to share with you the work of Brunno Araújo, who took the reference links I posted in the Microsoft Certifications post - Tips, links, and study materials for SQL Server 2016 MCSA and MCSE exams and created separate PDF files by category and subject, greatly facilitating the ...
Read More

How was Global Azure Bootcamp 2018 - Vitória / ES

Speak guys !! All in peace?? Last Saturday (21 / 04), I was pleased to be able to speak at Global Azure Bootcamp 2018 - Victoria / ES and talk about Database as a Service, especially about the non-Microsoft DBMS that are supported by Azure as PaaS (or DBaaS). ), which are MySQL and PostgreSQL, as well as several other DBMSs that are supported as ...
Read More

SQL Server - How to Standardize Collation of All Database Columns

Hello guys! All quiet? In this article, I would like to share with you a problem I had recently, which several columns from various tables in a given database used a different collation from the DB standard, causing joins and WHERE conditions between VARCHAR / CHAR / columns. NVARCHAR with different collations, the bank returns us the following error message: Cannot ...
Read More

Windows 10 - Rescuing Windows XP / 7 / 8 / 8.1 Photo Viewer

Hey guys! All right ? In this very short and quick post, which has nothing to do with development or database, I'll talk about solving a problem I had when upgrading Windows 8.1 from my personal computer (which I use to make my posts) to Windows. 10 (finally) and I had a great resistance to using ...
Read More

SQL Server - How to identify version and edit of all server instances using xp_regread and Powershell

Hello guys! In this post, I would like to share with you some ways to identify the version and edition of all server instances using xp_regread and Powershell. I saw a user submitting this question in a Whatsapp group and didn't find many solutions on the internet, which motivated me to write this article. How to identify the version and edition of ...
Read More

SQL Server 2017 - How to Pause Rebuild an Index Using Resumable Online Index Rebuilds

Hey guys! All quiet with you? In this post, I would like to demonstrate to you an innovative feature of SQL Server 2017 (Enterprise, Trial and Developer editions only), which is Resumable Online Index Rebuilds, which allows you to start an index rebuild process and be able to pause this operation in middle of processing and then continue where you left off, ...
Read More

SQL Server - Using Resource Governor for Greater Control of Server Resources

Hey guys!! This time, I am bringing a very useful feature in administering SQL Server instances on shared servers (which have more than 1 instance), which is Resource Governor. What is Resource Governor Resource Governor is nothing more than a feature available from SQL Server 2008 Enterprise that gives us the ability to ...
Read More

SQL Server 2016 - How to Protect Your Data Using Row Level Security (RLS)

Speak guys !! In this article, I would like to introduce to you a very interesting security feature that Microsoft SQL Server has gained from the 2016 release, which is Row Level Security (RLS) or Row Level Security. How Row Level Security (RLS) Works Row Level Security lets you control access to rows in a table ...
Read More

SQL Server 2016 - How to “Time Travel” Using the Temporal Tables Feature

Hey guys!! Through this post, I would like to show you a very interesting and useful feature, which is the Temporal Tables. As I already commented on them in the post SQL Server 2016 - List of news and new features, where you can retrieve data from a table at any point of time. That means you can do ...
Read More

SQL Server - How to Monitor and Audit Data Changes in Tables Using Change Data Capture (CDC)

Hello guys! In this post, I will demonstrate to you how to monitor and audit data changes in tables using Change Data Capture (CDC) in SQL Server. I had already written the post SQL Server - How to create a history of data changes for your tables (logs for audit), which uses the table triggers feature to accomplish this task, ...
Read More

SQL Server - Trigger to prevent and prevent table changes

Hello guys! A common need for DBA's is to avoid improper or misleading access and operations, which even the DBA itself may end up with being careless and performing the wrong operation. My idea in this post is to demonstrate some solutions using triggers. Creation of IF test table (OBJECT_ID ('dbo.Test_Trigger') IS NOT NULL) DROP TABLE dbo.Test_Trigger CREATE TABLE dbo.Test_Trigger (Id ...
Read More

Microsoft Certifications - Portuguese Study Material for Your First Certification (MTA)

Hello everybody, How are you ? Continuing My Post Microsoft Certifications - Tips, links, and study materials for the SQL Server 2016 MCSA and MCSE exams, where I covered broadly Microsoft certifications at all levels (MTA, MCSA, and MCSE), In this post I would like to focus especially on certification ...
Read More

6 Chapter Meeting SQL Server ES (PASS Local Group of Espírito Santo) - 24 / 02 / 2018

Hey everybody, all beauty with you? Once again, we are preparing and organizing the 6th Meeting of the Local Group SQL Server ES. This time, we will have as speaker Rafael dos Santos - MVP and a round table with some Data Plataform specialists, talking, asking questions and discussing about various technical subjects to choose from the event participants ...
Read More

SQL Server - How to improve formatting of combined query output with SET STATISTICS IO and TIME

Hello guys! All quiet with you? In this post, I would like to present to you a very useful tool in the performance tuning routine of the people who constantly needs to activate the STATISTICS TIME and / or IO options to view some information about these queries that can help in the activity of Query Optimization (Want some tips on Performance ...
Read More

SQL Server - How to Create Recursive Queries with Common Table Expressions (CTE)

Hello guys! How are you ? In this article I would like to demonstrate to you How to Create Recursive Queries with Common Table Expressions (CTE) in SQL Server. The CTE feature has been available since the 2005 release, and even today, many people are unaware of this interesting and useful feature on a daily basis. Introduction ...
Read More

SQL Server 2016 - Data Masking with Dynamic Data Masking (DDM)

Hello everyone, all right? Introduction In this post I would like to demonstrate a very interesting feature of SQL Server, available from the 2016 release, which is Dynamic Data Masking (DDM) and allows us to quickly and conveniently mask and hide sensitive information from certain users. Unlike the encryption features of SQL Server Transparent Data Encryption (TDE) and ...
Read More

Dev-ES Conference 2017 - 02 / 12 / 2017

Hello guys! All in peace? Today I would like to invite you all to participate in the Dev-ES Conference 2017 event, a regional event, which is spread across social networks, universities, technical schools and technology companies. The purpose of the conference is to foster the technology market in Espírito Santo and provide the community of technology professionals with contact with other ...
Read More

SQL Operations Studio - Management Studio 64 bits running on Windows, Linux and Mac

Hey guys! On November 15, Microsoft publicly released a preview (beta) version of SQL Operations Studio, a modern 64-bit tool that has been revamped for managing SQL Server instances, whether they are Windows, Linux, Mac, running on Virtual Machines. , Docker, Azure, or physical machines. Developed from Visual Studio Code and packaged through Electron, the ...
Read More

How was the 5th PASS Local Group SQL Server ES meeting?

Hey guys! All quiet? On 11 / 11 I had the pleasure of participating in the 5 Meeting of PASS Local Group SQL Server ES, with Tiago Neves, Vithor Silva and Fabrício Lima. If you did not see the event post, check it out by clicking this link. As always, the structure of the Catholic University Center of Victoria was sensational, serving us ...
Read More

How to connect to SQL Server using PHP (Xampp) and PDO driver in Windows

Hello guys! All right? In this post, I would like to demonstrate how to connect to SQL Server using PHP (Xampp) and PDO driver on Windows. Many people end up having trouble installing and configuring the drivers due to small technical details that eventually go unnoticed and make it impossible to connect PHP to the database.
Read More

5 Chapter Meeting SQL Server ES (PASS Local Group of Espírito Santo) - 11 / 11 / 2017

Hey everybody, all beauty with you? Once again, we are preparing and organizing the 5th Meeting of the Local Group SQL Server ES. This time we will have as speakers Fabricio Lima - MVP and Vithor Silva - MCSE and we will be back at the Catholic University Center of Vitória. The lectures If you are a Developer, you work with BI, it is ...
Read More

MCP Professional - How to Share Your Certifications and How to Find Certified Professionals

Hello everybody, How are you ? In this non-technical post, I would like to talk to Microsoft Certified Professional (MCP) professionals who visit and follow my articles about a very cool resource that is made available to MCP's and which few use. The MCP professional is one who has passed at least one certification exam from the ...
Read More

SQL Server - How to Document the Database and Its Objects (Tables, Procedures, Columns) Using Extended Property

Hello guys! All right with you ? In this post I would like to comment on something very important in the everyday life of DBA's and Query Developers, which is the database documentation. I hardly see environments where columns or tables have a clear description of what this bank object is all about. Introduction For who ...
Read More

SQL Server - How to Practically Share Your Web Execution Plans

Speak guys !! All quiet? In this post I will share with you a very nice tool to practically share your execution plans on the Web, whether in forums, Whatsapp groups, Telegram groups, etc. Often when someone asks for help in groups or comments from blogs / forums, there is no option to attach the execution plan or ...
Read More

SQL Server - How to create an email alert of locks and sessions locked on instance using DMV's

Hey guys, how are you? In this post, I want to share with you a solution (many possible) to create a SQL Server instance lock monitoring and alerting that can let you know when one or more sessions have been in lock (waiting for some feature) for more than X minutes . Anyone who works in critical environments knows how impactful ...
Read More

SQL Server - How to Create Error and Exception Tracking in Your Database Using Extended Events (XE)

Hello everybody, How are you ? In this post, I'd like to share with you a way to create error and exception monitoring in your database using Extended Events (XE), allowing you to capture and generate a history of errors that occur in your SQL Server instance, these errors, which may have been generated by ...
Read More

SQL Server 2012 - Using the FORMAT Function to Apply Masks and Formatting to Numbers and Dates

Hello everyone, everything good ? In this post today I would like to show you the T-SQL FORMAT function, available since SQL Server 2012, which until today few people use in day to day formatting of dates and numbers. When I look at Queries, Functions and Stored Procedures, I see that even today, many developers insist on using CAST, ...
Read More

How was the 4th PASS Local Group SQL Server ES meeting?

Hello guys! All right with you ? On 07 / 10 I had the pleasure of participating in the 4 Meeting of PASS Local Group SQL Server ES, with Tiago Neves, Vithor Silva and Fabrício Lima. If you did not see the event post, check it out by clicking this link. With a phenomenal structure, ISH Tecnologia welcomed us ...
Read More

SQL Server Groups on Telegram, Whatsapp and Slack: Let's talk about SQL Server?

Hello guys! All right with you ? Today we had another SQL Server ES meeting and I was able to talk to a lot of people about the IT market, have a nice networking and I realized that a lot of people have very interesting questions or knowledge to share, but I'm not joining any Telegram group, Slack or Whatsapp, ie no ...
Read More

4 SQL Server ES Meeting (PASS Local Group of Espírito Santo) - 07 / 10 / 2017

Hello guys! All right with you ? Once again, we are promoting the 4 Chapter SQL Server ES Meeting, which will be held on 07 October 2017, in order to prepare and form a community able to host major events such as SQL Saturday, which has already occurs in several other cities around the ...
Read More

SQL Server - Introduction to Performance Tuning Study

Hello everyone, everything good ? In this post I would like to start talking about a subject I really like, which is Performance Tuning, which I have already lectured on at the 2 Chapter SQL Server ES Meeting - 10 / 06 / 2017. This theme is always among the most sought after by database professionals, developers and companies looking for database consultants.
Read More

SQL Server - How to identify and collect time consuming query information using Trace (SQL Server Profiler)

Hello guys! How are you ? In this post I will demonstrate to you how to identify and collect information from time consuming queries using Trace (SQL Server Profiler). This is very useful to assist you in performance tuning analysis, making it easy to identify routines that have high response time, both procedure execution and ad-hoc queries. In the day to ...
Read More

SQL Server - How to generate a deadlock history for failure analysis in routines

Hello everybody, How are you ? Hope so! In this post I would like to share with you a situation where a deadlock occurred at dawn, disrupting a critical routine, and I needed to identify which session caused the deadlock to plan actions to avoid this situation. To help identify these situations and have a history of deadlocks, I will ...
Read More

SQL Server - How to fetch a code string from SP's that are called by SQL Agent jobs

Hello everyone, everything good ? In this post I will share with you a need I had on Friday, where a file server would be down for a few hours for maintenance and needed to identify which jobs were accessing that server and therefore would be impacted by this maintenance, ie search for a string in the code of SP's that are called by ...
Read More

How was the 3 SQL Server ES Meeting

Hello people! All right ? On 26 / 08 I had the pleasure of participating in the 3 SQL Server ES Meeting with Tiago Neves and Fabrício Lima. If you did not see the event post, check it out by clicking this link. Once again, we got some really cool giveaways to raffle to the event attendees. The speaker Alexandre Paiva has drawn ...
Read More

SQL Server - Write T-SQL as a Ninja Using Redgate SQL Prompt

Hello people!! How are you? Ready to reach a new level when it comes to writing queries quickly and with quality? In this post I would like to comment and give my opinions on a tool that has been in the market for a long time and that maybe many of you already know or have heard, but never got to ...
Read More

SQL Server - How to read and write events in Windows Event Viewer using CLR (C #)

Hello everyone, everything good? In this post, I would like to show you how to read and write events in the Windows Event Viewer using CLR (C #). To learn more about CLR, this powerful feature that lets you create and execute code written using the C # programming language and the .NET Framework to extend the functionality of ...
Read More

SQL Server - File Operations Using xp_cmdshell (List, Read, Write, Copy, Delete, and Move Files)

Hello people! In this post, I will demonstrate how to perform file operations using the xp_cmdshell procedure, how to copy files, delete, rename, move, create directories, etc. This post is a complement to other file operations using other solutions: File Operations Using OLE Automation in SQL Server SQL Server - How to List, Read, Write, Copy, Delete and Move Files with ...
Read More

SQL Server - How to Query Active Directory (AD) Information Using Linked Server (ADSI)

Hey guys! All right with you ? In this post I will show you how to query Active Directory (AD) information through SQL Server using a simple Linked Server and Active Directory Service Interfaces (ADSI) interface. Once again, Microsoft has been providing capabilities for SQL Server to integrate with more and more tools and interfaces ...
Read More

SQL Server - How to retrieve source code from an encrypted object (WITH ENCRYPTION)

Hello everybody, How are you ? In this post I would like to demonstrate to you How to retrieve the source code of an encrypted object (WITH ENCRYPTION) in SQL Server. How many times have I seen programmers encrypting objects in SQL Server in the false hope that this code will really be protected from changes and previews by other users. Introduction To ...
Read More

SQL Server - How to identify occurrences of a specific character in a string or table

Hey guys! All right with you ? In this quick post I will show you how to identify the occurrences of a specific character in a string or table ie count how many times the character "X" appears in each row of a table. For the examples below, I will use 2 SP's from the CLR that I demonstrated how to create (besides other alternatives, ...
Read More

3 Chapter SQL Server ES Meeting - 26 / 08 / 2017

Hello people! All right with you ? Once again, we are preparing and organizing the 3 Chapter SQL Server ES Meeting, which will be held on 26 August 2017. The community is being formed with the goal of becoming a Chapter of Pass in Victoria. This will enable us to organize major events like SQL Saturday, ...
Read More

SQL Server - How to Convert an RTF String to Text (Remove RTF Tags) Using CLR (C #) or Powershell

Hello everyone, everything good? In this article, I will show you how to convert an RTF string to text (Remove RTF tags) using CLR (C #) or Powershell, which was a necessity I had last week, where a system would write the information to a table and the data would be Rich Text Format (RTF). I searched the internet a lot to find solutions ...
Read More

SQL Server - How to Convert an HTML String to Text (Remove HTML Tags) Using CLR (C #)

Hey guys! All right with you? I hope so, huh! In this post, I will demonstrate to you how to convert an HTML string to text (Remove HTML tags) using CLR (C #). If you are new to the blog or have never heard of CLR or don't know how to create your first project using this powerful SQL Server tool, which allows you to ...
Read More

Analysis Services - How to Use XLMA or Powershell to Process Cubes and Dimensions via Command Line (T-SQL) or SQL Agent Job

Hello everyone, how are you doing today? In this post, I will demonstrate how to use the XML for Analysis (XLMA) language to be able to process Analysis Services cubes and dimensions through Transact-SQL (T-SQL) queries, which can be used in a SQL Agent job to automate this processing. , which, together with ETL loads for dimension data generation and ...
Read More

Microsoft MCSA Certification Analyzes and Visualizing Data with Power BI (70-778) and Analyzing and Visualizing Data with Microsoft Excel (70) for free (beta) through 779 / 15 / 09

Hello everyone, everything good ? In this post today, I will share with you two more promo codes for taking the Microsoft MCSA 70-778 (Analyzing and Visualizing Data with Power BI) and 70-779 (Analyzing and Visualizing Data with Microsoft Excel) certification exams for free ( beta), which can be scheduled up to 15 / 09 / 2017. Profile for the 70-778 test You consume and ...
Read More

SQL Server - Query to return running queries (sp_WhoIsActive without consuming TempDB)

Hello guys! How are you? In today's post, I will show you how to return active SQL Server sessions, showing CPU usage, read and write quantity, which user is performing the query, what is being performed by this session, which software is used , what is the hostname and a lot of other session information ...
Read More

SQL Server - How to Track Orders and Mail Objects (After WEBSRO Deactivated)

Hello guys! All right ? In this post today I will bring something very cool, which is a Transact-SQL stored procedure where you can query and track order information and objects using data from the Post Office site in SQL Server. For this, I will use the MSXML2.ServerXMLHTTP object and OLE Automation procedures for a solution and the CLR for ...
Read More

SQL Server - table-valued function to wrap a string into lines up to N characters long

Hello everybody, How are you ? In this post today, I will share with you a Table-valued UDF function that lets you break strings into lines, forcing the maximum length of each line to be N characters separated by a separator character defined in the function call. This function arose out of a need in a critical project where I work, ...
Read More

SQL Server - Database and Instance Level Permission and Audit Audit Trigger (GRANT and REVOKE)

Hello everybody, How are you ? After A LOT of time without posting any technical articles, I am starting to have time again to bring in more cool scripts and articles that I would like to share with you. In this post, I will demonstrate how to create a trigger to audit grant and remove events (GRANT and REVOKE) on objects, databases, ...
Read More

Microsoft MCSE 70-776 (Performing Big Data Engineering on Microsoft Cloud Services) certification test for free (beta) through 08 / 09 / 2017

Hello everyone, everything good ? In this post today, I will share with you one more promo code for the free Microsoft Performing Big Data Engineering on Microsoft Cloud Services (Microsoft) certification test that can be scheduled by 70 / 776 / 08. Candidates for this exam are for professionals implementing analytic solutions in Azure, ...
Read More

How was the 2 SQL Server ES Meeting

Hello guys! All right ? After making my debut as a speaker at SQL Server ES, the PASS Chapter of Vitória / ES, I will comment a little about how the event was, in which I also participated in the organization, along with Tiago Neves, Fabrício Lima and Vithor Silva . If you didn't see the event post, check it out by clicking ...
Read More

Microsoft Certifications - Tips, Links, and Study Materials for SQL Server 2016 MCSA and MCSE Tests

Hello everybody, How are you ? I'm a little absent here on the blog, but soon I'll be posting more often as soon as I finish some projects and consulting I'm doing. In the meantime, I couldn't help but share with you some tips, materials and links that can help you prepare for exams and certification exams ...
Read More

Microsoft certification exams: 50% discount for students (Undergraduate, Graduate, Master, Doctorate)

Hello guys! All right with you ? In this post I would like to share great news for you, who is a student (Undergraduate, Graduate, Master, Doctorate, etc.), is looking to take Microsoft certifications, but doesn't want to spend much. Since 2009, Microsoft has had an incentive program for students who want to take certification exams, giving them a discount of 50% ...
Read More

2 Chapter SQL Server ES Meeting - 10 / 06 / 2017

Hello guys! All right with you ? After a long time of planning and organization, MTAC Tiago Neves, together with MVP Fabricio Lima, managed to organize the first meeting of the SQL Server Community - ES. The community is being formed with the goal of becoming a Chapter of Pass in Victoria. This will enable us to organize ...
Read More

Microsoft Certification Exams - 25% Discount for MCP and MCSA Exams

Guys, Good afternoon! I will make this post very quickly, to spread the word soon: Microsoft decided to distribute a voucher of 25% discount on MCP and MCSA certification exams! Do not waste time and schedule your certification test. This discount is only valid for those who make the appointment between 03 / 05 / 2017 and 06 / 05 / 2017 and ...
Read More

SQL Server 2017 and Azure SQL Managed Instance

Hello everybody, How are you? SQL Server 2017 Once again, Microsoft surprises us with its innovations in the SQL Server product. Less than a year after the release of SQL Server 2016, Microsoft is already preparing to release a new version of the product, which is SQL Server 2017. If we think about the previous versions ...
Read More

SQL Server - How to calculate the difference in years (age) between two dates using T-SQL or CLR

Hello everyone, everything good ? In this post I would like to show how to calculate the difference in years (age) between two dates in SQL Server, showing some solutions and a performance analysis between these options in order to identify the most performative way to perform this calculation. As you know, to solve this question is not enough to perform a simple DATEDIFF (YEAR, ...
Read More

SQL Server - Knowing and Disabling AutoCommit

Hi guys how are you ? In this post I will talk about a session-level configuration that many people are unaware of and in many cases can be of great help in preventing accidental data loss, which is Autocommit. In some other DBMS's (Oracle, Postgree, etc) this is not enabled by default, which ends up generating a ...
Read More

Analysis Services - Querying Catalog Views from SQL Server

Hello guys, how are you doing? In this post, I would like to write about querying Analysis Services catalog views, either with queries through SQL Server or using the SQL Server Management Studio MDX or DMX query interface. My purpose in this post is just to demonstrate SQL Server Analysis Services (SSAS) catalog views (DMV). Case ...
Read More

Analysis Services (SSAS) - How to query information and process command line (XLMA) commands through SQL Server

Hello everyone, how are you doing today? In this post, I will demonstrate how to use the XML for Analysis (XLMA) language to be able to query and send commands to Analysis Services through Transact-SQL (T-SQL) queries, which can be used in a SQL Agent job to automate this process, allowing you to automate backup / restore routines, for example, from ...
Read More

1 SQL Server Community Meeting - ES

Hey guys from ES, all right? After a long time of planning and organization, MTAC Tiago Neves, together with Fabrício Lima, managed to organize the first meeting of the SQL Server Community - ES. The community is being formed with the goal of becoming a Chapter of Pass in Victoria. This will enable us to organize major events ...
Read More

SQL Server - Performance Comparison between Scalar Function and CLR Scalar Function

Hello everybody, How are you ?? In this post, which will be the blog's 200 number, I would like to talk about two subjects that I particularly like about SQL Server, which is the creation of .NET (CLR) routines within the SQL Server database and performance tuning. Coincidentally or not, my post number 100 was the Introduction ...
Read More

SQL Server - Is your instance constantly having “Starting up database XXX” and “In Recovery” databases? Meet Auto Close

Hello guys! All beauty? In this post I would like to write about a feature of SQL Server that even today, we see being implemented in instances of various companies and which causes all resources (CPU, Disk, Memory ..) to be released after the last user. get rid of the databases. I'm talking about the Auto-Close property, which is considered ...
Read More

SQL Server - How to export and import tabular data files (Ex: CSV) using CLR (C #)

Hello people. Hope all is well with you. In this post, I would like to demonstrate a very interesting and commonly used feature of those who create cross-system integration routines using tabular data files, ie text files that use a delimiter to separate information into "columns", such as for example, the CSV file type ...
Read More

SQL Server - How to query and consume WordPress RSS feeds using CLR or xp_cmdshell (cURL)

Hello everybody, How are you ? I was watching Fabricio Lima's latest video, where he wrote about the blogs he usually follows by feedly, an RSS reader. I found the idea cool and decided to create a small RSS reader running on SQL Server using CLR or cURL running with xp_cmdshell. In this case, as the return ...
Read More

SQL Server Management Studio (SSMS) - How to enable Dark Theme (Dark Theme Support)

Hello everybody, How are you ? Today I was checking the news of blogs that I recommend and ended up seeing a super cool tip on Leka Blog that I decided to share here with you too, which is to enable the Dark theme in the interface of SQL Server Management Studio (SSMS), as well as already exists in Visual Studio already ...
Read More

SQL Server - How to calculate the distance between two points using the Google API (zip code, address or latitute and longitude)

Hi guys how are you? In this post, I will demonstrate how to calculate the distance and time between two points, either by entering the zip code, address or latitude and longitude. To meet this need, I will use the Google Maps API, which will perform the calculations and the CLR (C #) or OLE Automation to perform web requests to query and ...
Read More

SQL Server - How to create an AutoSum (same as Excel) using Window functions (Running totals)

Hi guys how are you ? Today I am going to introduce an interesting feature of SQL Server that most people don't know about, which is the use of Window functions to perform cumulative calculations on a SQL Server result set, similar to Excel's Auto Sum feature. This need came to me through a request similar to the one I am going to ...
Read More

Analysis Services - Using XLMA for Cube Backup and Restore via T-SQL Command Line

Hello everyone, how are you doing today? In this post, I will demonstrate how to use the XML for Analysis (XLMA) language to be able to back up and restore Analysis Services cubes and dimensions through Transact-SQL (T-SQL) queries, which can be used in a SQL Agent job. to automate this process by letting you automate the backup / restore routines of ...
Read More

SQL Server - How to identify and monitor total free disk space used by database datafiles

Hi guys how are you ? In this post, I will demonstrate how to identify and monitor the total, free, and used disk space of your instance databases. This post is a complement to my SQL Server post - How to identify and monitor disks, free and used total disk space, where I demonstrated how to identify, analyze and monitor disk space ...
Read More

SQL Server 2016 - How to Query Zip Code Information Using the Bemean API and JSON_VALUE Function

Hello guys! All right with you ? In this quick post, I will once again demonstrate the use of OLE Automation and CLR procedures to consume information on the Web and bring it to our SQL Server database so that we can work with this information as needed. This time, I will show you how to query the city, state, neighborhood, and ...
Read More

Analysis Services - Creating Your First Multidimensional Cube in the Star Model (Star Schema)

Hello guys! How are you ? In this post I will demonstrate how to create your first multidimensional cube in the star schema. This post ended up being done without the idealization that I usually do, because I was writing another blog post and I needed to create a simple Analysis Services cube for the demo and I understood it as an opportunity to talk a ...
Read More

SQL Server - Understanding SQL Agent Permissions and Roles (SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole)

Hi guys how are you ? Lately, I've been seeing a considerable number of DBA's with questions about SQL Server Agent role permissions, and a lot of questions come up about this topic in Whatsapp groups, including: How to make a non-sysadmin user view jobs? How to make a user other than ...
Read More

SQL Server - Implementing Access Control and Audit Logs Using CLR (C #)

Hello guys! How are you ? In this post I would like to demonstrate how to implement access control and audit logs in using CLR (C #). This has been found to be very useful during auditing processes or when there was a problem due to misuse of the CLR, especially where the SQL Server service user is domain admin or has ...
Read More

SQL Server - How to transfer logins between instances by backing up users, logins, and permissions

Hello everybody, How are you ? In this post I will demonstrate a very cool script that I developed that aims to generate SQL scripts to back up (reverse engineer) permissions, users, logins and roles of an instance in SQL Server. This script is designed to be used for instance migrations, either for a new server or for ...
Read More

SQL Server - How to identify and monitor disks, free and used total disk space

Hello everybody, How are you ? In this post I will demonstrate a very interesting and common feature in the everyday life of a DBA, which is monitoring disk space on the server. As a DBA, you should always have control over server disk space, so you don't let that space reach critical levels ...
Read More

SQL Server - How to export assembly from a CLR as DLL and reverse engineer to C # source

Hello everybody, How are you ? In this post I will demonstrate how to export a CLR assembly in SQL Server as a DLL and reverse engineer it to C # source code. The first time I needed to use this feature was when there was an assembly already created and compiled in the database and I needed to change the source code of a procedure, but the version ...
Read More

SQL Server - How to back up all SQL Agent jobs via command line (CLR C # or Powershell)

Hello guys! All right? In this post I will demonstrate how to back up all SQL Server Agent jobs via command line (CLR C # or Powershell) and export the results to SQL scripts. You can choose to generate 1 script for each job or 1 single script with all jobs in your instance. During SQL Migration ...
Read More

SQL Server - How to Delete a User from an Instance

Hello everyone, everything good? In this post I will demonstrate how to delete a user from a SQL Server instance, which involves deleting users from all databases and then deleting the user's login to the instance. If the user is owner of any database, change the owner of that database (s) and if the user is owner of any job, do the ...
Read More

SQL Server - How to copy / replicate a user's permissions

Hello guys, In this post I will demonstrate a script that I developed and that has been very useful in my day to day and has the function of copying permissions from one user to another, with the option to just generate the SQL script or actually copy the permissions, which can be at the level of a specific database or in the ...
Read More

SQL Server 2016 - Using Native JSON Support (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY)

Hello everybody, How are you ? In this post I will demonstrate how to use native SQL Server database support for JSON, a document standard widely used in integrations and Webservices today. This feature is available from the 2016 version of SQL Server and is intended to allow database data to be exported to a string ...
Read More

SQL Server 2016 SP1 - CREATE OR ALTER in procedures, functions, triggers and views

Hello everyone, Good afternoon! In this post I will demonstrate a new Transact-SQL feature available from SQL Server 2016 SP1, which is the CREATE OR ALTER command, which can be applied to procedures, functions, triggers and views. For those who work or have worked with Oracle, you know that this is a copy of CREATE OR REPLACE, existing in this DBMS for ...
Read More

SQL Server - Grouping Data Using ROLLUP, CUBE, and GROUPING SETS

Hi guys good morning! In this post I will talk about something that is not new to SQL Server, is present since SQL Server 2008, but I do not see many people using in their queries, which is the grouping (summarization) using ROLLUP, CUBE and GROUPING SETS . This type of feature is especially useful for generating totals and subtotals ...
Read More

SQL Server - How to identify, monitor, and prevent overflow in IDENTITY and SEQUENCE columns from tables with many records

Hello everyone, Good afternoon! In this post I will show you how to prevent a type of SQL Server problem that can cause a lot of disruption to a DBA's life, takes a long time to resolve, and can be easily avoided and monitored, which is when an IDENTITY column reaches the limit value of your data type and ...
Read More

SQL Server - How to Integrate Database with Telegram and Send Messages Using CLR (C #)

Hi guys good morning! In this post I will demonstrate how to do a database integration with Telegram and send messages using CLR (C #), which is Whatsapp's main competitor. Earlier, I demonstrated how to do this same integration in posts: SQL Server - How to do a database integration with Slack and send messages using ...
Read More

SQL Server - How to Integrate Database with Slack and Send Messages Using CLR (C #)

Hi guys good morning! In this post I will demonstrate how to do a database integration with Slack and send messages using CLR (C #), which is Ryver's main competitor, in which I already demonstrated how to do this same integration in the post SQL Server - How to do an integration from the database with Ryver and send messages ...
Read More

SQL Server - How to Integrate Database with Ryver and Send Messages Using CLR (C #)

Hi guys good morning! In this post I will demonstrate how to do a database integration with Ryver and send messages using CLR (C #), which is Slack's main competitor, in which I already demonstrated how to do this same integration in the post SQL Server - How to do an integration from the database with Slack and send messages ...
Read More

SQL Server - How to Send SMS Messages Using the CLR (C #) and the More Result API (PG Solutions)

Hi guys good morning! In this post, I will demonstrate how to send SMS messages using the CLR (C #) and the More Result API (PG Solutions) through the SQL Server database itself. This is especially useful when you need to create alerts and monitoring of your critical routines, which work at dawn or on weekends and require immediate action. Why ...
Read More

Microsoft MCSE Certification Exam 70-774 (Perform Cloud Data Science with Azure Machine Learning) for free (beta) through 31 / 03 / 2017

Hello everyone, Good afternoon! In this post today, I will share with you another promo code for taking the Microsoft MCSE 70-774 (Perform Cloud Data Science with Azure Machine Learning) certification exam for free (Beta) through 31 / 03 / 2017. Candidates for this exam are data scientists or analysts who use Azure cloud services to build and deploy ...
Read More

Microsoft MCSE 70-775 (Perform Data Engineering on Microsoft Azure HDInsight) certification test for free (beta) through 31 / 03 / 2017

Hi guys good morning! In this post today, I will share with you one more promo code for the Microsoft MCSE 70-775 (Perform Data Engineering on Microsoft Azure HDInsight) certification test for free (Beta) through 31 / 03 / 2017. You are a data engineer, architect, data scientist, and developer who implements data engineering streams using Big Data in the ...
Read More

SQL Server - How to rename server / instance in database when machine name is changed in OS

Hi guys good morning! In this post, I will demonstrate how to rename the server / instance in the database when the machine name is changed in the operating system, which is relatively common in everyday life. When this happens, the machine name in the operating system is different from the machine name in the database, and ...
Read More

SQL Server - How to Email the Database Using CLR (C #)

Hello everyone, Good afternoon! All right with you ? In this post I will demonstrate how to send emails through the database using CLR (C #). In the post SQL Server - How to enable and configure Database mail to send and monitor database emails (sp_send_dbmail) I had already demonstrated how to send emails through SQL Server Database Mail, ...
Read More

SQL Server - How to find out when instance was installed (date of installation)

Hello everyone, Good afternoon! In this post I will show you how to find out when the SQL Server instance was installed (date of installation), information that is very useful for system inventory or to let you know when the trial version you have installed will expire. There are several ways to identify the date of installation of SQL Server, but I will ...
Read More

SQL Server - How to Email a Query Result in HTML Format Using CLR (C #)

Hello guys! Good Morning. In this post I will demonstrate how to export the result of a query to a string in HTML format, so that you can email the query result in a way that is visually cool. I had already done something similar in the post How to export data from a SQL Server table to HTML, but ...
Read More

SQL Server - How to monitor your favorite series by consuming a JSON Webservice with JSON_VALUE function and using CLR (C #)

Hello people good night! In this post, I will demonstrate to you how to use the new SQL Server 2016 JSON handling feature, which is returning by a CLR (C #) procedure to return information from your favorite TV series. This is a cool and fun way to learn a little more about these two powerful tools ...
Read More

Analyzing Big Data with Microsoft R MCSE 70-773 Certification Exam for Free (Beta) through 28 / 02 / 2017

Hi guys good morning! In this post today, I will share with you one more promo code for the MCSE BETA 70-773 (Analyzing Big Data with Microsoft R) certification test for free (Beta) through 28 / 02 / 2017. I had already made a post about it, with other 3 promo codes for free beta exams, where I passed the exams ...
Read More

Free SQL Server 70 MCSA (764-70 and 767-2016) Certification Tests (Beta) through 31 / 01 / 2017

Hi guys good morning! In this post today, I will share with you some more promo codes for taking the free SQL Server 2016 MCSA BETA certification exams, specifically the 70-764 Administering SQL Database Infrastructure and 70-767 Implementing the SQL Data Warehouse tests. I had already made a post about it, with other 3 promo codes for ...
Read More

SQL Server - How to Create a Hidden Friend Draw Using Transact-SQL

Hi guys good morning! All right with you ? In this post, I will demonstrate a script to perform various sweepstakes as a hidden friend, which is so common among Brazilians at the end of the year. This script was created by BI analyst, T-SQL expert Lucas Arrigoni. The script has some checks, such as validating if the number of participants allows a ...
Read More

SQL Server - Msg 443 Invalid use of a side-effecting operator 'rand' within a function

Hello everyone, good afternoon. In this post, I will demonstrate how to work around a limitation of SQL Server, which is the use of the RAND () function in UDF functions, so that you can generate random values ​​and apply them to a table. If you try to do so, SQL Server will return this error message: Msg 443, Level 16, State 1, Procedure fncGera_Sword, Line ...
Read More

SQL Server - How to use the SQLQueryStress tool

Hi guys good morning! In this post, I will introduce and demonstrate the use of Adam Machanic's open-source SQLQueryStress tool (creator of sp_WhoIsActive), which serves to perform stress tests by running a given query, allowing you to perform a series of tests on your SQL instance. Since this project is no longer maintained and updated by Adam, Erik Ejlskov Jensen ...
Read More

SQL Server - Why Not Use SET ANSI_WARNINGS OFF

Hello people good night! All right ? In this post I will comment on a feature used by many developers to quickly "fix" some common issues in developing Transact-SQL queries. This feature is SET ANSI_WARNINGS OFF. What is the SET ANSI_WARNINGS OFF command for? The ANSI_WARNINGS session control option changes the behavior of the SQL Server engine to ...
Read More

Reporting Services (SSRS) - Error deleting mobile reports (Error invoking authorization extension)

Hello people good night. In this post, I will demonstrate how to solve a very strange problem in SQL Server Reporting Services 2016, which occurs while trying to delete some mobile reports and getting this error message: Error invoking authorization extension. Since this message is not at all explanatory, I decided to try parsing Reporting log files ...
Read More

How to install and configure Microsoft SQL Server Reporting Services (SSRS) 2016 on Windows Server 2016

Hi guys good morning. In this post, I will demonstrate how to configure Microsoft SQL Server Reporting Services (SSRS) 2016 on Windows Server 2016. As you know, Reporting Services is installed together with SQL Server, and at the time of SQL Server installation, if you install Reporting Services as well, you can set whether to configure it at this time or to ...
Read More

SQL Server - How to encrypt and decrypt passwords (with Salt) using CLR (C #)

Hello people good night! In this post, I will demonstrate how to encrypt and decrypt passwords with CLR (C #). To do this, I will use the .NET Framework MD5CryptoServiceProvider and TripleDESCryptoServiceProvider encryption algorithms, which allow you to use a (Salt) keyword to ensure that this key will be used in data encryption and can only be decrypted using that secret keyword. If you do not ...
Read More

SQL Server - How to query the national, state, municipal, and optional holidays of an API using OLE Automation and CLR (C #)

Hello everyone, Good afternoon! In this post, I will follow a reader's tip and use the excellent free API of Calendario.com.br to return to the list of national, state, municipal and facultative holidays in Brazil, allowing to filter by year, state and / or city. I had already commented on this subject in the post How to create a table with the holidays (national, state ...
Read More

Utilities of the .htaccess file

Hi guys good morning! In this post, I will share useful information related to the .htaccess file, which is the Apache HTTP server configuration file, in which I have already made some posts about this link. These tips I found on the blog http://www.deuzebranaweb.com.br/ and found the content excellent and decided to share with you these little precious tips. To create the rules ...
Read More

SQL Server - How to identify fragile passwords, empty or equal to username

Hello people good night! In this post, I will demonstrate how to identify fragile passwords, empty or equal to the username in SQL Server. This is especially useful for database administrators to avoid attacks due to carelessness of users in choosing their passwords. Introduction To perform this check, we will use the PWDCOMPARE function, present in SQL ...
Read More

Login error failed for user 'user' when trying to connect to SQL Server through a .NET (C #) application

Hello everyone, good afternoon. In this post I will comment on a problem I recently encountered in a company, where no application was able to connect to the production database, displaying the message "Login failed for user 'user'." Introduction and problem description In the company where this connection error occurred, there are several applications that connect to ...
Read More

SQL Server - How to integrate with FTP and list, upload, and download files using CLR (C #)

Hello everyone, Good afternoon! In this post, I will talk again about CLR (C #), demonstrating yet another usefulness of this powerful feature of SQL Server, which is the integration between database and FTP servers, either to download or upload files between a local or network folder and the FTP server. This feature is especially useful ...
Read More

SQL Server: Session has been running for a long time with the sp_readrequest (DatabaseMail) command

Hello everyone, Good afternoon! In this post, I will share with you a learning that I had this week. In the company where I work, the version of RedGate SQL Monitor was recently updated and with this, several alerts for instance monitoring were created. One such monitoring is long running queries, which identifies sessions that have been executing an instruction for more ...
Read More

How to install and configure Microsoft SQL Server 2016 on Windows Server 2016

Hi guys good morning! In this post, I will demonstrate to you how to install SQL Server on Windows Server 2016. This post is an update from my other post Installing and configuring SQL Server 2014 on Windows Server 2012 R2 and if you are a SQL Server DBA and have not installed SQL Server 2016 yet, you will end up facing ...
Read More

How to create a random password generator written in PHP, C # (CSharp) or Transact-SQL (T-SQL)

Hi guys good morning! In this quick post, I will show you how to create a small random password generator to be used in many different ways. I will make this script available using C # languages ​​(for use on SQL Server, with CLR), PHP and Transact-SQL. These scripts are very simple, but the idea is to demonstrate how to use this feature in ...
Read More

Using the Pushbullet API to send SMS messages in C #, PHP, Java, or SQL Server (with CLR)

Hello everyone, Good afternoon !! In this post, I want to show you something new that I was able to develop this week and I found very cool, which is sending SMS messages from virtually any current programming language (C #, VB.NET, Java, PHP, etc.) and even , by the SQL Server database itself using SQL CLR (can also be done using ...
Read More

SQL Server - How to List and Drop Windows Processes Using CLR (C #)

Hi guys good morning! In this post, I will demonstrate how to list and eliminate processes that are running on the SQL Server server using CLR (C #). These features can be especially useful for quickly identifying which user processes are running, which processes are using the most memory or CPU usage, for example. While developing a PowerShell script, ...
Read More

SQL Server - How to Run PowerShell and Prompt-DOS (MS-DOS) Scripts Using CLR (C #)

Hello people good night! CLR or xp_cmdshell: What is the best way to run scripts? In this post I will demonstrate how to run PowerShell and Prompt-DOS (MS-DOS) scripts through the SQL Server database using SQL CLR (C #), a feature of SQL Server that allows the database to execute code written in the C # programming language. , from the Microsoft .NET Framework, for ...
Read More

SQL Server - How to read, list, create, change and delete Windows registry keys (Regedit) by CLR (C #)

Hello everyone, Good afternoon! In today's post, I will demonstrate how to manage (read, list, create, change, and delete) Windows registry keys through the database using SQL CLR and the C # programming language (CSharp), which allows us to greatly extend the capabilities and functionality of the SQL Server database. This kind of ...
Read More

SQL Server - How to audit permission errors on objects using SQL Profiler (Trace)

Hi guys good morning! In today's post I will demonstrate how to create a trace in SQL Server using SQL Profiler to audit object accesses, whether by a specific user, object or specific database. This is especially useful for identifying which users have access to a particular object or also finding out which users do not have access to an object ...
Read More

Free SQL Server 2016 MCSA Certification Tests (Beta)

Hello everyone, Good afternoon! In this post today, I will share with you some promotional codes for taking the free SQL Server 2016 MCSA BETA certification exams. When Microsoft is preparing to release certification exams, it makes some code available to IT professionals around the world, so they can take this certification exam.
Read More

SQL Server - How to export source code for all Stored Procedures, Functions, Views, and Triggers from a database to .sql files

Hello guys! Good Morning. Introduction In this post, I will show you how to generate SQL Server programming object creation scripts (Functions, Trigger, Stored Procedures, and Views) and export them to .SQL files using query to catalog views. This way we can automate the generation of these scripts. Another need that led me to create this script was ...
Read More

How to validate state registration for all states using C # (CSharp) and SQL Server CLR

Hello people good night! In this post, I will bring back a function for state registration validation from all states of Brazil, as I had already shown in my post How to validate state registration using T-SQL function in SQL Server, but this time, I bring you the written function in C #, to be used in your CLR projects in the database.
Read More

SQL Server Reporting Services - Error Saving Report: System.InvalidOperationException This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms.

Hello people good night! In this post I will comment on some of the difficulties I faced in a BI industry project in the company I work for, where we decided to implement the recently launched Microsoft Reporting Services 2016 (which incorporated Datazen, acquired by Microsoft) for the provision of reports, dashboards, KPIs. and Sales and Billing dashboards via the internet, without the need for ...
Read More

SQL Server - How to Stop, Start, and List Windows Services Remotely

Hello everyone, Good afternoon! In this post I will demonstrate how to manage Windows Services from the SQL Server database using SQLCLR to perform these operations. This can be especially useful when creating monitoring for certain services. Currently where I work, I created a service monitoring configuration table, where I determine the data of the ...
Read More

SQL Server - How to send warnings and error messages to the bank through CLR (C #)

Hi guys good morning! In this post I would like to demonstrate to you as I am (and intend to continue) posting a number of cool things about CLR, such as sending warnings (PRINT) and error messages (RAISEERROR) to SQL Server when your CLR compiled Stored Procedures are executed. Although the post is small, I decided to create a post with just that, because ...
Read More

SQL Server - How to Verify a Server Is Responding Using PING and CLR (C #)

Hello people good night! In this post I will demonstrate how to validate by SQL Server if a server is responding to the network using CLR (C #) and the PING class, which simulates a ping request that you make at the DOS prompt in Windows or in the Unix Shell. In daily life, I use this function a lot to check if a ...
Read More

How to compress directories in ZIP archives using 7zip and CMD

Hello people good night. In this quick post I will demonstrate how to compress directories and sub directories by creating 1 zip file with all content or 1 ZIP file per directory, using 7-Zip, the best file compressor, in my opinion, and the DOS prompt (CMD ). I use this little script a lot in my daily life, mainly to store the files.
Read More

100 thousand views

Hello people good night! All right with you? In this commemorative post, I would like to thank you immensely, who follow my posts, comment, share, enjoy .. It was thanks to you that I managed to reach the number of 100 MIL VIEWS (considering only the home and the posts). It may not seem like great things, but coming up with a technical blog of ...
Read More

SQL Server - How to Create a Data Change History for Your Tables (Audit Logs)

Hi guys good morning! In today's post, I will demonstrate how to create a history of data changes, whether by INSERT, DELETE, or UPDATE in a table, so that you can know which user and when information was changed and what was the information of the time. This is very useful for auditing or BI reporting that ...
Read More

SQL Server - The data types datetime and time are incompatible in the add operator

Hello guys! Everything is good ? After almost 15 days without posting on various jobs I was working on, I got some time to make this quick post for you about a mistake I found when adding a DATETIME column with a TIME type. This type of operation typically occurred when bank compatibility mode was ...
Read More

MySQL - How to convert integer stored time to a time column

Hello people good night! In this post today I will demonstrate how to convert stored integer time to a time column. I had the idea of ​​making this post when a colleague DBA told me that he is running a system that uses the MySQL database and has a table where the programmer decides to store time in an entire field, ...
Read More

CSharp (C #) - Sorting Files Returned by DirectoryInfo.GetFiles Using Natural Sort

Hi guys good morning! In this post I will take a very simple approach to something that many .NET developers are looking for on the internet, as I sought this solution myself, but which is a little tricky to find because most of the posted solutions do not work. My problem was that I use a C # function a lot in my CLR to list files ...
Read More

SQL Server - Using SP WhoIsActive to identify Locks, Blocks, Slow Queries, Running Queries, and more

Hello people good night! In this post I will demonstrate a tool used by 99.99% of SQL Server DBA's around the world and you probably already know it, which is Adam Machanic's excellent and famous stored procedure sp_WhoIsActive, which allows us to get a lot of session information SQL Server instances like the query that ...
Read More

SQL Server - How to concatenate rows by grouping data by one column (Grouped concatenation)

Hello everyone, Good afternoon! In this post I will talk about a well-requested feature for Microsoft, and that it has not created natively in SQL Server, which is concatenation of data using collations, already present in other DBMS such as MySQL (GROUP_CONCAT), Oracle (XMLAGG) and PostgreeSQL ( STRING_AGG or ARRAY_TO_STRING (ARRAY_AGG ())). Many people think this feature is the CONCAT () function, introduced in the ...
Read More

SQL Server - How to read, import, and export data from XML files

Hello people good night! In this post I will demonstrate to you how to import, read, handle and export data between a SQL Server table and an XML file. This integration between database and XML files is a great feature and a big differentiator for developers using SQL Server who can easily read and generate files in this format ...
Read More

SQL Server - The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE

Hi guys good morning! Today I am going to talk about a problem that may occasionally occur with CLR assemblies that use unsupported DLL's (eg System.DirectoryServices) and were compiled with the UNRESTRICTED (UNSAFE) or EXTERNAL ACCESS permission. When trying to use an SP or function that needs this type of access, we encounter the following error message: A ...
Read More

SQL Server - Importing and Exporting Excel Spreadsheet Data

Hello people good night! In this post, I will talk about a very interesting feature of SQL Server that works as a great differential of the database, which is the ability to natively integrate with Excel, allowing you to query and manipulate spreadsheets through the database without having to from any other external resources. To make this possible, you ...
Read More

SQL Server - How to Batch All .sql Scripts in a Folder or Directory by SQLCMD

Hello people good night! In this post I will demonstrate how to run multiple batch scripts that are in a given folder, generating execution log, with just one line of code. This instruction is very simple, and allows a series of customizations to meet your need. I believe every DBA and most developers have had to run several scripts ...
Read More

SQL Server - How to Install the Microsoft.ACE.OLEDB.12.0 and Microsoft.Jet.OLEDB.4.0 Drivers

Hello people good night! In this post, I'll talk a little about the two most commonly used OLEDB drivers in SQL Server for file integrations, especially Excel, which are Microsoft.ACE.OLEDB.12.0 and Microsoft.Jet.OLEDB.4.0. Once installed on the server, they allow you to enter, query, update and delete data from Excel spreadsheets and data files via the database.
Read More

SQL Server - How to export database data to text file (CLR, OLE, BCP)

Hello people good night! After writing my previous post, where I talked about how to import text files into the database (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET), I just got excited to write the 2 part, which is the reverse process. export the data from SQL Server to a text file, which is exactly this post. Many ...
Read More

SQL Server - How to import text files into database (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET)

Hi guys good morning! In this post I would like to show you different ways to import content into a text file for a variable in SQL Server, so you can use this information in a way that meets your needs. I already posted some ways to do this here on the blog, but on different topics in com ...
Read More

SQL Server - Error Using OPENROWSET: Access to remote server is denied because no login-mapping exists

Hello people good night! In this post I will talk about an error that occurs when trying to use the OPENROWSET or OPENQUERY command to access data from a remote server where the linked server used does not have a fixed user and the connection user does not belong to role sysadmin, which represents the majority. of the cases. (one more bug reported by ...
Read More

SQL Server - Cannot resolve the collation conflict between… in the same as operation.

Hello everyone, Good afternoon! In this post I will show you how to quickly and easily solve a problem that although it is simple and the message is very clear, I have seen many analysts not knowing how to solve. Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CS_AS" in the same to operation. What is a COLLATION? Collation swims ...
Read More

SQL Server - How to resolve Warning Null value is eliminated by an aggregate or other SET operation

Hello people good night! In this post I will talk about something that is very common to find when developing queries using Transact-SQL (T-SQL) in SQL Server, which are aggregate or grouping functions (Ex: SUM, MAX, MIN, AVG) in columns. which have NULL values ​​in their records. When this happens, an alert is generated with this message: Warning: Null ...
Read More

SQL Server - Heterogeneous Queries Require the ANSI_NULLS and ANSI_WARNINGS Options to Be Set for the Connection

Hello people good night! In today's post I will talk about a not very common error that occurs in SQL Server when trying to execute queries using Linked Server or cross-server statements (Ex: OPENROWSET, OPENQUERY, etc.) and SQL Server returns the following message: Msg 7405, Level 16, State 1, Line 45 Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS ...
Read More

Analysis Services - Deserialization Failed: 'AllowedRowsExpression' Element in Namespace

Hello everyone, good afternoon. In this post I will explain a very common error for BI analysts using SQL Server 2008 and after migrating to SQL Server 2012 and 2014 come across the error message below when trying to open Visual Studio 2008 (BIDS) Deserialization failed : The 'AllowedRowsExpression' element in namespace 'http://schemas.microsoft.com/analysisservices/2011/engine/300/300' is unexpected ...
Read More

SQL Server - How to list, read, write, copy, delete and move files with CLR (C #)

Hi guys good morning! In this post I will show you how to list, read, write, copy, delete and move files using CLR (C #), which is a powerful tool for increasing the range of features of SQL Server. How do I create many file exchange routines in my work, whether importing data from an external base or exporting the data ...
Read More

SQL Server - How to UPDATE and DELETE with TOP x Records

Hi guys good morning! Today I will show you how to update or delete a fixed number of records from a table, which can be used to manipulate table data without locking it for a long time while executing queries. Generating a simple base I will demonstrate how to create the test base that we will use in this post IF ...
Read More

SQL Server - The Danger of Using JOIN Between Columns of Different Data Types

Hello guys! Good night! Today I came across a critical performance issue in a production environment, where a particular query (which can be executed multiple times per second) was experiencing an already known and old slow-running problem (taking between 21 and 30 seconds per run), but today was the day to solve it all at once ...
Read More

Using sequences in user defined functions in SQL Server

Hello everyone, Good afternoon! In today's post I will demonstrate how to use sequences in user defined functions like scalar, table-valued and aggregate. Don't know what a SEQUENCE is? Learn more about this object by accessing the post Working with Sequences in SQL Server By default, SQL Server does not allow sequence NEXT VALUE FOR properties to be used ...
Read More

SQL Server 2012 - Working with Sequences and IDENTITY Comparisons

Hello guys! Good Morning! Today I came across a situation where the use of the sequences, old known to me from Oracle and that from SQL Server 2012 were introduced in Microsoft DBMS, saw a perfect solution to the problem I was facing. Introduction - Detailing the Problem This problem consisted of a table with dozens of ...
Read More

SQL Server - Querying instance information in Windows Registry using sys.dm_server_registry and xp_instance_regread

Hello everyone, Good afternoon! In this post I will demonstrate how we can access the Windows registry and get useful information about the SQL Server instance. If you would like to know how to query any Windows registry key, as well as make changes to keys and values, visit the post SQL Server - How to read, list, create, change and delete registry keys ...
Read More

Using SQL Server Standard Trace to Audit Events (fn_trace_gettable)

Hello everyone, Good afternoon! In this post, I will demonstrate how to get important information about various events that occur in the SQL Server instance without having to enable any controls or auditing by just reading the information already collected by the standard SQL Server trace. Although I have already talked about this in some other posts, I decided to close this subject and make a post ...
Read More

SQL Server - The database owner SID recorded in the master database differs from the database owner SID recorded in the database

Hello everyone, Good afternoon! In this post I will demonstrate how to solve a common problem developing CLR libraries that occurs when you restore a CLR database from another server. Despite the quite simple solution, when you first encounter this problem, it may take a while to resolve. Identifying the problem and understanding ...
Read More

Monitoring DDL and DCL Operations Using SQL Server fn_trace_gettable

Hello everyone, Good afternoon! In this post I will demonstrate how to identify DDL and DCL events that occur on the SQL Server instance. This allows us to identify which user created / changed / deleted an object, or who added / removed a user to a system role or database role and many other things without having to create any controls or monitoring ...
Read More

Monitoring Autogrowth Events in Databases in SQL Server

Hello readers, Good afternoon! In this post I will demonstrate how to monitor the autogrowth growth of a database in SQL Server so that it is possible to identify the user who caused the growth and when the autogrowth events occurred in the database. These results are possible thanks to the :: fn_trace_gettable system function, which allows you to query the ...
Read More

SQL Server 2016 - News and Features List

Hi guys good morning! Next Thursday, April 7, Microsoft will hold an event in Sao Paulo for the release of SQL Server 2016 in Brazil. With each new release, we are seeing major advances and improvements in DBMS, which is becoming increasingly complete, fast, secure and ahead of other database solutions ...
Read More

How to retrieve the largest value between multiple columns in a SQL Server table

Hello readers, Good night! In this post I will briefly demonstrate how to retrieve the largest value between multiple columns in a SQL Server table. I've seen several solutions to this need, but are you using the most performative of them? Generating a test mass To make it easier to see the results, let's create some test data: IF ...
Read More

How to find out the date of last access to a table or view and execution of a procedure in SQL Server

Hello people good night! In this post I will show you how to identify the last access date of a table or view and the last execution date of a procedure in SQL Server. This is especially useful for identifying unused tables or even understanding how much they are used. For this, SQL Server provides us ...
Read More

CLR - Resolving the System.Security.Permissions.FileIOPermission Problem in SQL Server

Hi guys good morning! In this post I will show you a very common problem when developing SQL CLR projects, which consists of the following error message when trying to perform any file operation: Complete error message: Msg 6522, Level 16, State 1, Line 1 A .NET Framework Error occurred during execution of user-defined routine or aggregate "fncArchive_Ler": ...
Read More

Visual Studio - Resolving the problem between sgen.exe and the .NET Framework SDK (Build Error)

Hello everyone, Good afternoon! In this post I will share with you an issue I had while developing some CLR routines in C # in a new build environment using VMware, Windows 7 x64 and Visual Studio 2015 Community Edition. The problem The problem occurs when I try to build or publish my assembly ...
Read More

Performing POST and GET requests using CLR (C #) in SQL Server

Hello everyone, Good afternoon! In this post I will be demonstrating to you how to perform POST and GET requests (actually it can be any type) on a SQL Server database using SQL CLR. If you don't know what CLR is or have questions about how to use it, how to post it, etc., check out this post here. I had already made some posts ...
Read More

How to list jobs (schedules, commands, steps) via Query in SQL Server

Hi guys good morning! In this post, I will demonstrate to you a query that lets you list various properties of SQL Server Agent Jobs, including schedules, steps, executed commands, categories, and more. This is especially useful for routine auditing and inventory. I tried to create a complete script, listing practically all the properties of Jobs, Steps and Shedules, where the ...
Read More

Introduction to SQL Common Language Runtime (CLR) in SQL Server

Hi guys good morning! In this post, which will be the blog's 100 number, I would like to talk about something I particularly like about SQL Server, which is the creation of .NET routines within the SQL Server database. Yes, we are talking about the common language runtime (CLR). Introduction Present in SQL Server database since version ...
Read More

How to Fix Duplicate MAC Address Problem in VMware

Hi guys good morning! Today I came across a silly and easy-to-solve problem that bothers me a lot when using VMware virtual machines, which is when the following message occurs: Adapter Ethernet0 may not have network connectivity.MAC address 00: 0C: 29: 58: 61: 19 of Ethernet0 adapter is within the reserved address range or is in use by another virtual adapter on ...
Read More

What is the difference between higher education IT courses?

Hello people good night! I come to bring you a very interesting article that I found on the internet, written by Claudio Florenzano for the site techdicas.net.br, commenting on the difference between the IT higher education courses. I myself did not know that there were so many higher education courses in IT, which is still highly valued through certifications. Some friends of mine ...
Read More

Querying SQL Server object tracking

Hello everyone, Good afternoon! In this post today I will bring something very cool, which is a Transact-SQL stored procedure where you can query and track order information and objects using data from the Post Office site in SQL Server. For this, I will use the MSXML2.ServerXMLHTTP object and OLE Automation procedures. This is very useful for e-commerce companies ...
Read More

How to wrap a string in a substring table using a delimiter in SQL Server

Hello everyone, Good afternoon! In this post I will demonstrate a very nice table-valued function that lets you break a string in a substring table using a delimiter in SQL Server. What does that mean? Well, this makes you have a string and the function will turn that string into a table using a separator. In this table, each record ...
Read More

Consuming the Google Maps API to get address or zip code information in SQL Server

Hello everyone, how are you? In the first post of 2016, I decided to bring a legal use of OLE Automation procedures to look up address information through the zip code or return the zip code (and other information) from an address. This I had already demonstrated before in the posts How to query information from a zip code in SQL Server (where used the service ...
Read More

Happy 2016!

Hello people good night!! In the last post of the year, I would like to thank all of you who visit and comment on the posts I publish, because it is you who motivate me to continue writing and studying to create quality content. In July of 2014, I decided to create this blog, following the example of my co-worker Fabricio Lima, where I ...
Read More
/ Uncategorized / 1 comment in Happy 2016!

How to reset and recover SQL Server Administrator (sa) password

Hello everyone, Good afternoon! In this post I will demonstrate how to recover the password of the SQL Server Administrator (sa) user if it has been lost or changed by a malicious user or you simply do not remember the password anymore. Many less experienced DBA's may be desperate with this situation and resort to reinstalling the product to recover this password and ...
Read More

Links to download Microsoft Visual Studio 2015 and SQL Server 2014

Hello guys! Good Morning! I was looking for the links to download Visual Studio 2015 with SQL Server 2014 and I could not find the URLs for it on a single page and this motivated me to make this post to help you. On 20 July 2015, Microsoft hosted an online event and launched Visual Studio 2015 ...
Read More

CPF and CNPJ generator valid for SQL Server environment testing

Hello guys! Good night. In this post I will demonstrate how to create a stored procedure to return a list of valid CPF's or CNPJ's for testing environments and systems. You can capture the result of the procedure and record and a temporary table and test your systems easily now. If you need a function that validates CPF, CNPJ, Email, Phone and ...
Read More

How to convert integers to hexadecimal, octal, and binary in SQL Server

Hello guys! Good night. In this post I will demonstrate how to perform conversions between decimal numbers (base 10) to hexadecimal numbers (base 16) and vice versa using simple SQL Server functions to perform these tasks and in the end I will provide a very interesting function that allows you to convert a number in base 10 to another number from any base (base 2 to 99) ...
Read More

How to convert numbers to roman numerals in SQL Server

Hello guys! Good Morning! In this post I will demonstrate how to convert a normal (base 10) number to a Roman numeral (X, XVII, etc.) using a Transact-SQL function in SQL Server that I found on the Advaldo Paiva blog. Because it is a function, we can apply it for a simple conversion or for updating table data, for example. Function Code: ...
Read More

How to convert milliseconds, seconds, or minutes to TIME in SQL Server

Hello everyone, Good afternoon! These last few days I'm a little out of time, but to keep up with updating here I will do another quick post demonstrating how to convert milliseconds, seconds or minutes to TIME in SQL Server. Often in our routines, especially when we create routine execution logs measuring the time taken for processing, we have to ...
Read More

Identifying tables with IDENTITY columns in SQL Server

Hi guys good morning! In this quick post I will demonstrate how to identify tables that have IDENTITY columns in SQL Server. Often we need to identify which tables are these, what is the current value of identity and what is the value of the table seed and of course we will not look table by table. For this we can quickly identify this information ...
Read More

Mapping Object Dependencies in SQL Server

Hi guys good morning! In this post I will show you how to find dependencies between objects of various levels of hierarchy in SQL Server using Transact-SQL queries, simulating a behavior similar to what is presented in the Management Studio interface. Using SQL Server Management Studio This is the easiest way to do this mapping, as it does not require any ...
Read More

Audit on SQL Server (Server Audit)

Hello people! Good evening! In this post I will demonstrate how to create Server Audit in SQL Server. This feature is very interesting for auditing DDL and / or DML (Database Audit Specification) actions performed by a user or application or even the instance itself (Server Audit Specification). Creating an interface audit (GUI) Open SQL Server Management Studio, select the ...
Read More

Understanding Index Functioning in SQL Server

Hi guys good morning! In this post I will comment on the structure of indexes, which help a lot to optimize queries, reducing IO and CPU and returning information faster. However, be very careful when creating indexes, as they take up a lot of disk space and if not well modeled, may not be as effective. Introduction An index ...
Read More

How to identify, delete, and recreate Foreign Keys (FK) from a table in SQL Server

Hi guys good morning! Introduction In this post I will demonstrate to you how to identify, delete and recreate Foreign Keys (FK) from a table in SQL Server. We often need to delete a table or just change a column, but the bank sends us an error message stating that there is a foreign key constraint for that column, making it impossible for the DBA to ...
Read More

Enabling and using dedicated remote administrator connection (DAC) in SQL Server

Hi guys good morning! In this post I will demonstrate how to enable / disable the remote Dedicated Administrator Connection (DAC) feature of the SQL Server instance, verify that the feature is active, and how to use it. Introduction - What is DAC? Imagine in the following situation: All instance worker threads are in use and no longer allowed to connect to ...
Read More

Troubleshooting .NET Framework DLL Assembly Signing Issues in SQL Server

Hello everyone, Good afternoon! In this post I will show you how to quickly resolve .NET Framework DLL Assembly signing issues in SQL Server. This error occurs when some SQLCLR routine uses some server .NET Framework DLL and after some system update, the CLR user loses Unsafe Assembly permissions or some DLL ...
Read More

SQL Server - How to Learn from Basics to Certification (Courses, Virtual Labs, Virtual Academy)

Hello everyone, Good afternoon! I was reading the blogs of other DBA's and IT professionals I follow and saw a really cool post on Vithor Silva's blog about free Microsoft training and courses and decided to share with you. I am a deep learner, technology aficionado and always looking for more knowledge to improve myself. I believe this ...
Read More

Checking a user's permissions in SQL Server

Hello people! Good Morning. In this post, I will demonstrate how to verify the permissions of a user and / or database in SQL Server, as well as allowing you to easily remove these access or grant again. If you want to replicate a user's permissions between different instances, see if the article SQL Server - How to copy / replicate a user's permissions can help you ...
Read More

How to identify the port used by the instance of SQL Server

Hi guys good morning! In this post I will show how to identify the port used by the instance of SQL Server. As you may know, the default port for TCP / IP connections is 1433 and for UDP connections is 1434. However, if you have more than one installation on the same server, you can only use these ports for one of the instances by doing ...
Read More

The undocumented SQL Server extended procedures

Hello guys! Good night! In this post, I will comment on some SQL Server extended procedures that are very useful. Until this post, I didn't find any official Microsoft documentation about it so I decided to comment on it, besides demonstrating some examples. An extended procedure (XP) is a link to a dynamic library (DLL) programmed using the SQL API ...
Read More

How to query SQL Agent startup history in SQL Server

Hello people! Good afternoon! In this quick post, I will show you how to perform a simple query in the msdb.dbo.syssessions system view to identify the date and time of each instance SQL Agent startup, which is responsible for controlling and monitoring SQL Server Jobs (in addition to of other things). In a normal environment, usually SQL Agent starts ...
Read More

How to calculate shipping amount and lead time using Post Office WebService in SQL Server

Hello people! Good afternoon! In this post, I will once again demonstrate the use of OLE Automation in SQL Server to consume data from a Web service. This time, I will consume the Post Office service to calculate shipping time and value (widely used for e-Commerce) . Knowing the Mail WebService Before we started creating the Stored Procedure ...
Read More

How to configure your VM network in Bridge mode in VMware Player

Hello guys! Good afternoon! I was using my VM to create my previous post (How to query information from a zip code in SQL Server) and had some problems accessing the internet. The Problem I've always used my VMs with the Network Address Translation (NAT) connection mode, which is the simplest and easiest of all to set up, works well ...
Read More

How to query information from a zip code in SQL Server

Hi guys good morning. In this quick post, I will once again demonstrate the use of OLE Automation procedures to consume information on the web and bring it to our SQL Server database so that we can work with this information as needed. If you want to use the Bemean API, which returns the data in JSON format, see more on ...
Read More

How to identify inactive sessions with open transactions in SQL Server

Hello Blog visitors! Good afternoon! In this post I will demonstrate how to identify inactive sessions, that is, that are not running a query and have open transactions. This can cause a number of problems with your database, from crashing other sessions trying to access the same resource to avoiding automatic transaction log backup ...
Read More

How to identify users logged in to SQL Server

Hello everyone, Good afternoon! In this very quick post, I will show you the usefulness of the dm_exec_sessions system view, where we can refer to the connected sessions in our instance of SQL Server. Identifying users logged in to SELECT instance session_id, login_time, host_name, program_name, client_interface_name, login_name, status, cpu_time, memory_usage, last_request_start_time, last_request_end_time, transaction_isolation_level, lock_timeout, deadlock_priority FROM sys.dm_exec_s_name .
Read More

How to identify jobs running via Query in SQL Server

Hello everyone, good afternoon. After a while without posting, today I will demonstrate in this post how to identify jobs running via Query in SQL Server. With this query, you can identify the name and id of the job, which step the job is running, which query it has been running now and how long ago. How to identify running jobs ...
Read More

How to calculate business days in SQL Server (dCalendar table)

Hi guys good morning. In this post I will show how to perform various calculations with weekdays in SQL Server, creating a table with all the information already calculated and remaining just perform some simple SELECTs to get this information. Prerequisite: Holiday Table A prerequisite for this post is that you have already created the holiday table that I commented on ...
Read More

Free SQL Server ebooks to Download

Hello people good night. In this post, I will leave some downloadable ebook links that I found on my colleague Fabrício Lima's blog, also DBA, and which can be very useful for you: Free ebook - Defensive Database Exceptional DBA: Download Free ebook - Mastering SQL Server 2005 ...
Read More

How to create a table of holidays (national, state, and mobile) in SQL Server

Hello everyone, good afternoon. In this quick post, I will demonstrate how to create a table with Brazil's national, state, and mobile holidays. This table is very useful for identifying whether a given date is a holiday or not, and is also a prerequisite for creating the post weekday table How to calculate weekdays in SQL Server. An ...
Read More

How to remove accent and special characters from a string in SQL Server

Hi guys good morning. In this post I will briefly comment on a User Defined Function (UDF) that I used to remove accents and special characters from a string in SQL Server. Removing Accents There are several ways to do this, such as using a UDF to do this work, a SQLCLR function, or the one I prefer, which is using the ...
Read More

Consuming the Google Maps API using OLE Automation in SQL Server

Hello people good night! In this post I will give you a quick tip on how to consume the Google Maps API using OLE Automation in SQL Server to retrieve more information about a city just by its name. Not sure how to enable OLE Automation on your instance? See more in the post Enabling OLE Automation via T-SQL in SQL Server. Implementation of ...
Read More

Using JSON on SQL Server 2008, 2012, and 2014 - Reading JSON Strings, Importing to the Database, and Exporting to XML

Hello everyone, good afternoon. In this post I will demonstrate how to read JSON strings and convert to a table and convert data from a table to JSON and XML using only T-SQL. This tip is very useful when you are using an earlier version of SQL Server 2016 and need to read Json strings. If you are using SQL Server 2016 ...
Read More

How to drop all connections from a database in SQL Server

Hello people good night. Today I bring a quick tip for those who already wanted to do a quick restore, an alter database or any command that requires an exclusive lock on a database, but there were users running queries in the database, and then you get an error message like this SQL Server: Msg 3101, Level 16, State 1, Line 2 ...
Read More

SQL Server - How to Implement Audit and Login Control (Login Trigger)

Hello everyone, Good afternoon! In this post I will demonstrate to you some cool features of logon triggers such as creating an audit log for each user who connects to your database, blocking connections from a user / IP / Hostname and blocking connections at a certain time. WARNING First and foremost, I would like to warn you about DANGER if ...
Read More

How to create an Audit trigger to log object manipulation in SQL Server

Hi guys good morning. Introduction Today I bring to you a very useful feature in the life of SQL Server DBA, both for auditing change / creation / deletion of objects and for "versioning" SP's, Functions, etc., which is the use of triggers to log all DDL level changes to objects, including being able to write the query used for ...
Read More

How to export data from a SQL Server table to HTML

Dear, Good afternoon. In this post I will demonstrate how to export data from a SQL Server table to an HTML file or a variable of type VARCHAR, where two very similar Procedures will be presented, where one generates the HTML as a physical file on disk and the other writes the HTML generated in an OUTPUT variable. I also did ...
Read More

How to Create an Audit to Monitor Job Creation, Modification, and Deletion in SQL Server

Hi guys good morning. Today I come to you, the solution of some questions I asked myself in the environments in which I work, but I could not answer: - Who created / changed / deleted a particular job? - I accidentally deleted a Job .. How do I recreate it quickly, without having to restore backup? - Someone has disabled a certain Job .. Who ...
Read More

How to validate state registration using T-SQL function in SQL Server

Guys, good morning. Today I will make a quick post for you and it is very useful in everyday life, especially for those who create integration routines with Sefaz, Revenue and other government agencies and need a routine to validate the state registration for all states of Brazil , or even for those who want to have this field in ...
Read More

Timeout when running Queries via Linked Server on SQL Server

Hi guys good morning. Today I bring you another quick post of a problem I encountered while trying to run an XMLA script for Microsoft Cube processing (this happens with T-SQL queries too), where the connection timed out (10 minutes) and was terminated by the server. with the following message: The XML for Analysis request timed out before being ...
Read More

How to convert the run_date and run_time columns from job_history to datetime in SQL Server

Hi guys good morning. Today I will briefly show you how to convert the run_date and run_time columns from the msdb.dbo.sysjobhistory database catalog table to datetime. Currently, the run_date column is a varchar in the format yyyymmdd (Ex: 07 / 05 / 2015 = 20150507), and the run_time column is a time in the format hmmss (Ex: 08: 27: 00 = 82700). You can even understand visually the ...
Read More

How to estimate how much time is left to finish the backup on SQL Server?

Hello everyone, Good afternoon! Passing here to leave a quick tip for when you are running that long backup and want to know the% that has been completed and get an idea (estimate) of how much time is left to finish the execution of the command. To accomplish this task, we can query this information in the dm_exec_requests catalog view, as shown in the query ...
Read More

Implementing Apache Friendly URL with .htaccess and PHP

Undoubtedly, SEO (Search Engine Optimization) is one of the main study factors for web developers today as it is directly responsible for how the website will attract new visitors and for positioning it on search engines like Google, Yahoo, Bing and others. Some time ago, web developers had no concern about ...
Read More

Avoiding Duplicate Content on Apache Web Server Using .htaccess

In this post I will demonstrate how to avoid one of the major SEO issues, which is duplicate content, ie two or more URLs from your website contain the same content. For SEO, this is awful, because clicks and views are counted separately according to how your URL was typed. Although it seems just a detail, ...
Read More

Enabling data compression in Apache using .htaccess (DEFLATE and GZip)

In this post I will demonstrate how to compress text content in your web applications or websites, so that your trafficked size is smaller and therefore your application load time as well. For this we will use the Apache web server configuration file, the .htaccess. Remember that files like videos, music and other binary files are probably already compressed ...
Read More

Creating and restoring logical backups (DUMPs) in Oracle Database 11g (exp and imp)

During this post, I will demonstrate how to create and restore Oracle Database backups using the exp and imp tools, which allow you to generate full backups of an owner and restore to another server. Setting Environment Variables in Windows SET ORACLE_SID = ORCLTESTE SET ORACLE_HOME = C: \ oracle \ product \ 11.2.0 \ dbhome_1 SET NLS_LANG = AMERICAN_AMERICA.AL32UTF8 Setting Environment Variables in Linux EXPORT ORACLE_SID = ORCLTES_X / db_11.1.0 / EXPORT NLS_LANG = AMERICAN_AMERICA.AL1UTF32 Parameters ...
Read More

How to register and create server groups in SQL Server Managment Studio (And set the color of each server)

Today I bring a very quick post, but it is a very nice feature of SQL Server Managment Studio that a DBA of the company that I work (Tiago Neves) showed me, and that allows you to change the color of the status bar according to the server that we are connecting to. It may sound silly, but in everyday life ...
Read More

SQL Server - How to enable and configure Database mail to send and monitor database emails (sp_send_dbmail)

Many people ask me, "Is it possible to send email through SQL Server?" or "How to send email through SQL Server?". In this post I will show you how to enable email sending on your SQL Server server and how to send and monitor email sent. One feature that is widely used in most systems is the automatic sending of emails containing notifications, ...
Read More

Compressing all tables in a database in SQL Server

In this post I will talk a little about the SQL Server data compression method and how we can use this feature to compress all objects in a database. Data compression can be configured for the following database objects: For a table that is stored as a heap. For a table that is stored ...
Read More

Installing SQL * Plus and Oracle Client 10g

In this post I will be demonstrating how to install Oracle Client 10g, along with SQL * Plus. I recently made a post about installing Oracle Database 11g, and we are currently in version 12c (some 2 years ago), but the latest version of SQL * Plus as an application was 10g. Starting with the 11g version, the software binary has passed ...
Read More

Running PHP scripts automatically through crontab on Linux

Suppose you have developed a system for a company and at some point that company has asked you to have this system need to send certain emails to its customers every Monday and your system should perform automatic backups every Friday. at 23: 00. And now? How do we do it? What is cron Well, this is the solution to ...
Read More

JQuery Javascript Framework Introduction

JQuery has undoubtedly revolutionized the way we write code in the Javascript language. Not only for ease, but also for the wide range of features we can use by using this excellent javascript framework, ranging from simple DOM editing to Ajax requests and event handling. Used by most web developers nowadays, this framework comes with ...
Read More

Installing Oracle Database 11g R2 on Windows

In this post, I will demonstrate how to install Oracle Database 11g in the Windows environment, which is widely used today for the ease and practicality of the Microsoft operating system. Run setup.exe to start the installation From the first installation screen we can set an email to receive updates from Oracle. This step can be skipped if you wish. In step ...
Read More

Running a command on all instance databases in SQL Server

Hey guys! All quiet, right? Today I will show you how to execute a query on all databases of a SQL Server instance. This is often useful for creating space monitoring routines, running a checkdb on all banks, and many other day-to-day needs. To perform this activity there are several different ways of programming, ...
Read More

Working with cryptographic functions (MD4, MD5, SHA1, SHA2_256, and SHA2_512) using the SQL Server HASHBYTES function

Hello everyone, all in peace? A little about coding In this post I will demonstrate a little more about the SQL Server HASHBYTES function, which allows you to work with HASH-based cryptographic functions, where once encoded, you cannot get the original string again. If you want to know more about encryption using the Base64 algorithm, which allows you to encrypt and ...
Read More

Working with the Base64 Encryption Algorithm in SQL Server

Hello everyone, Good afternoon! Today I will make a quick post, but very interesting for those who need to encrypt and / or decrypt strings using the Base64 algorithm in SQL Server. A lot of people don't know, but SQL Server has the capabilities to work with this encryption natively without having to write all the logic for it. A little about Base64 Base64 ...
Read More

Validating CPF, CNPJ, Email, Phone, and Zip Code in SQL Server

Hello everyone, everything good ? Today I will be introducing some functions that will help them improve validations in their routines, such as validating a CPF from your base is valid or not. Zip Validation This is a simple validation, which only checks if the number of characters is correct and if all the entered string has 8 characters ...
Read More
/ Database,