Click on the banner to learn about and purchase my database training on Azure

Blog

Views: 13.332 views

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

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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 .
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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, ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
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, ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
JQuery has undoubtedly revolutionized the way we write code in the Javascript language. Not only for its ease, but also for the great variety of resources that we can have using this excellent javascript framework, ranging from simple DOM edits to Ajax requests and event handling. Used by most web developers today, this framework comes ...
Learn more
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 ...
Learn more
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, ...
Learn more
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 ...
Learn more
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 ...
Learn more
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 ...
Learn more
Hello readers! All right ? Today we are going to talk about a classic developer question and some DBA's just starting now. After all, what is the difference between DELETE, TRUNCATE TABLE and DROP TABLE? When should we use each of these commands? To illustrate this difference, I will present the table below: DELETE TRUNCATE DROP Deletes data from a table IS ...
Learn more
Hello everyone, all right? In this post I will talk a little about a problem that I have seen a lot in the bases I have been consulting, which are the orphaned users in the base. A little about permissions in SQL Server In SQL Server, there is the concept of Login, which is the user at the bank instance level and is also ...
Learn more
Hello people! All right ? Today I am going to talk about a problem I recently encountered, where a routine used the sys.syscolumns catalog view to get column information from a Fact (BI) table and at some point when trying to execute the SELECT * FROM sys query .syscolumns, SQL Server returned the following message: Msg 220, Level 16, ...
Learn more
protheus erp
Hello people good night! All right? Today I will bring to the blog, content that can help many Protheus professionals and / or consultants on a daily basis, which is the name and description of most of the tables of ERTV of TOTVS (Old Microsiga), Protheus. If a table is missing, leave the name and description here in the comment. SA1 REGISTERS REGISTER ...
Learn more
Hello Guys, All right? Today I got to know Microsoft SQL Server's Service Broker and learned a lot by reading Júnior Galvão's blog, from where I extracted this post. I do not think it would be worthwhile to write much about this topic, if he has already approached it (in a very didactic way) extensively. So let's get to know this important MS feature ...
Learn more
Guys, good night. After having several problems setting up a Windows PHP + Firebird environment, I decided to create this post to help other colleagues with the same problem and eliminate the error message once and for all: Fatal error: Call to undefined function ibase_connect () configuration Download XAMPP 1.8.2 VC9 and extract the files ...
Learn more
Guys, good night. Today I come with another cool news for you that I am using in a project where I need to create an integration between two systems, one local and another on the web: exporting data to a CSV file. This class is very similar to the one I showed in this post, except that we are now exporting ...
Learn more
Readers, Good afternoon! In this post I will talk more about the SQL Server SET statement, which besides being used for setting variables, can also be used to change current session settings. They are: Category Statement Description Date and Time SET DATEFIRST Sets the first day of the week as a number from 1 to 7, where: 1 = ...
Learn more
Hello readers, Good morning! I recently had a need to create a MySQL ranking similar to SQL Server ROW_NUMBER (). Unfortunately, Oracle has not yet implemented this feature as useful, but as if everything works out, I will show you how we can implement this. A very simple way to do this is to create a table and include a field ...
Learn more
Good night! In this post I will talk a little more about SQLCMD, the SQL Server command line utility. As an Oracle DBA, I only used SQL * Plus, which is Oracle's command-line utility, to do my activities and found it very practical, lightweight, able to create highly customizable and easy-to-execute scripts (@wait, @locks). , ...
Learn more
Hello readers, Good morning! Today I will show you how to install Microsoft SQL Server 2014 on Windows Server 2012 R2 in a virtual machine. The process for installing on a physical machine is the same, without differences. If you are looking for the updated version of this post, with the most current versions of SQL Server and Windows Server, learn more by visiting ...
Learn more
Hi! Good night! I recently did a tutorial teaching how to create a virtual machine using VMware Player. Now, I will show you how to do the same thing, using another well-known and rival VMware freeware tool, Oracle Virtualbox (Formerly Innotek VirtualBox). Oracle VirtualBox Download Oracle VirtualBox and install it on your operating system. Click the "New" button Different from VMware Player, here ...
Learn more
Good night! All right ? Today I'm going to write this post teaching you how to create a virtual machine (VM) using one of the most popular freeware tools for this: VMware Player. If you want to know how to create a virtual machine with Oracle VirtualBox, I was comfortable clicking here and find out. VMware Player Download VMware Player and install it on your operating system ...
Learn more
Guys, good afternoon. Today I will demonstrate how to increase the session timeout (cookie) and the maximum execution time of a script in PHP using the Apache HTTP Server configuration file, .htacess, remembering that the parameter values ​​entered must be in seconds and you can use this same directive to set various other parameters of the ...
Learn more
Guys, good night. To help you identify what each HTTP status code is, here is a list I have summarized from Wikipedia: Category 1xx - Informative This status code class indicates a tentative response, consisting only of Status-Line and optional headers, and is terminated by an empty line. Since HTTP / 1.0 does not define all the codes of ...
Learn more
Guys, good night. Today I will show you how to redirect your applications in case of a URL typo, for example, where the Apache web server typically displays an error message and the visitor may have difficulty returning to your site. If he found your site through a search engine (aka Google), he ...
Learn more
Guys, good night. Today I am going to talk about how to prevent unauthorized people from viewing the files and folders of your Apache web server by entering a directory name, such as www.yourdomain.com/images/. By default, the apache web server will open the default file defined by the DirectoryIndex directive (Learn more here). If this file does not exist or does not have a directive ...
Learn more
Dear readers, Good night! This post will be quick, but very useful for those just starting to learn more about the Apache web server and its .htaccess configuration file. Today I am going to show you how to set the default Apache page load order, that is, when you simply enter the site domain, which file Apache will open ...
Learn more
Guys, good afternoon. This feature of PHP is already known to many people, but if not, I will try to help those who do not know this powerful feature of PHP, which is the integration with servers using the FTP protocol, which allows us to transfer files between servers. Connecting to server public function connectFTP () {$ server = 'ftp.server.com'; // Address ...
Learn more
Guys, good afternoon. After a long time without posting, I will come back with a nice news for you that I am using in a project where I need to create an integration between two systems, a local and another on the web: exporting data to a CSV file. For that, I created a class containing the function we will use, available here. Example ...
Learn more
Hello guys! Good Morning! Today I am very excited about this stored procedure that I will present to you, because it really gave me a little work to develop. Of course, you have heard of CSV (Comma-separated values) files, those text files where information is organized using a delimiter, usually comma (,) or semicolon (;) ...
Learn more
Hello dear readers. Good Morning! Today I want to talk about a need in SQL Server where I needed to look in which columns, from which tables, a record was written. To solve this problem, I created a Stored Procedure that performs this search: stpBusca_String_Table: USE [dirceuresende] GO CREATE PROCEDURE [dbo].[stpBusca_String_Table]( @Ds_Texto VARCHAR(100), @Ds_Banco AS VARCHAR(100), @Ds_Filter_Table AS VARCHAR(100) = NULL, @Ds_Filter_Column ...
Learn more
Hello people good night! Remember the post I made about Enabling OLE Automation via T-SQL in SQL Server where I said I would post about some cool functions exemplifying the use of OLE Automation in SQL Server? So this is the post 🙂 To not put too many Stored Procedures and Functions here in the post and make it too long, I created ...
Learn more
Guys, good night. Today I will teach you how to enable OLE Automation features in SQL Server. For those unfamiliar with this feature, it allows the DBA or Developer to perform a series of actions on the database using OLE DB, such as read / write / move / copy / delete files, Excel spreadsheet creation and more things. The syntax is a ...
Learn more
Guys, good night. Today I will bring you a very cool Stored Procedure, which uses database catalog views to look for a particular string contained in the job title or command line of some job step. I tried to format the output to provide very complete information about the returned results. I even used it once ...
Learn more
Hello guys! Good night! Today I am going to present a function created by my friend Murilo Mielke, which lets you break a string delimited by some (or some) characters in substrings. For web developers, this is what makes the explode function of PHP or Java Split, Javascript, C #, etc .. Basically, you have a string like the example below: ...
Learn more
Good morning, dear readers! All right with you? Again, I'm bringing a function that I use in my projects, and I use it whenever I need to write the value of a number in full, which is not such an unusual situation, is it true? Added 2 optional boolean parameters $ bolDisplayCurrency: Defines whether the function will add a reference to ...
Learn more
Guys, good morning. I am bringing you another useful function on a daily basis, which removes number formatting, causing "R $ 1.487.257,55" to become a float-shaped string, that is, "1487257.55" . For this, I created the clsTexto class and the function removeFormatacaoNumero:
Learn more
Guys, Good afternoon! Today I will show you how to remove HTML tags from a string in SQL Server using only T-SQL. This function was very useful for me, as there was a need to include a column in a report that was exported to XLS (Excel), but this column was the HTML description of system-generated calls and Excel ...
Learn more
Good afternoon people! Today I came to bring you a script that I used a lot and made my life a lot faster whenever I had to migrate a system from one instance to another or dump structure and data from one environment to another. The script prompts for the owner name and a directory where it will ...
Learn more
Hi guys good morning! I come here to bring some more scripts that speed up some routine activities of Oracle DBAs, such as generate reverse engineering script (DDL Backup) of system objects, such as users, profiles, roles, etc. To download the scripts I will talk about in this post, go to this link: Oracle Database - System Object Backup As there was ...
Learn more
Guys, Good morning! Today I would like to show you, a very Oracle Database feature called Automatic Workload Repository (AWR), which allows a complete view of the database, as I / O-consuming queries, CPU, instance information, server , fragmentation, index usage, wait events, resource-intensive events and a wider range of ...
Learn more
Guys, good night! Today I will show you how to reverse engineer objects in Oracle Database, also known as DDL backup for creating objects. To download the scripts that I will demonstrate, go to this link. To use them, you must be using Oracle SQL * Plus, preferably the GUI, which is more convenient to run ...
Learn more
Guys, good night. I'm stopping by to talk about a site I saw on the web and found it very interesting. Therefore, I will disclose. It's colorzilla.com/gradient-editor/ With it, you can create gradient effects to use on your websites and web systems. It already has some really cool presets, allows customization, has option to generate CSS code ...
Learn more
Friends good night! This post is intended for you, PHP programmers, who are finding it difficult to add and subtract dates and times in PHP. I prepared a small class to help them overcome this problem. Come on:
Learn more
Guys, good night! Another post that can help some PHP programmers, especially beginners. In many situations we need to convert dates in the format dd / mm / yyyy to yyyy-mm-dd and vice versa. For this, I will create a small class with 2 functions for this:
Learn more
Guys, good night! This time I come to comment on a problem that bothered me a little in PHP whenever I needed to delete a directory and it contained files. As you know, the rmdir function does not delete directories if there are files. Well, let's work on a solution for that:
Learn more
Guys, good night. I'm going to show you how to list files in a directory in PHP using 2 functions and then, natively. I'm going to create a class with static methods so that I don't need to instantiate the object.
Learn more
Guys, good night! Today we will see how to read and write data to a text file using the PHP web programming language. To do this, we will create two functions to assist us in this task: WriteTextFile: public function writeTextFile ($ strFile, $ strText, $ bolPageFileExists = false, $ bolUTF8 = true) {if (! Is_dir (dirname ($ strFile))) {mkdir (dirname ($ strFile ...
Learn more
Loading...;