The use of a subquery (sub-select) statement in T-SQL queries is common but can cause slow performance as the “subquery” needs to to be evaluated first. We can use a common table expressions (CTE) in place as a temporary result set to work with. The code can also be reused… Read more »
Sending emails using DBMail is one of the most common requested tasks for the DBA. This could be alerts, reminders, results from a job or reports for users. As a DBA you should be familiar with querying a table and sending results as a CSV or Excel file as an… Read more »
Inventory tracking and reporting is important for any Administrator position in IT. The DBA needs to be able to be able to report on the Database size requirements for all supported systems. The following script is not the only way this information can be collected but it is simple. We… Read more »
When dealing with database queries we use the for-loop to access each record and deal with it one at a time. In some cases it’s better to deal with the database results in a variable so we do not need to query again. The Python List object allows us to… Read more »
Often there is a requirement to add, subtract or calculate the difference between dates using Python. We will run through some examples of how to find and generate dates moving forward and backward through time using the datetime module. Here are some quick examples on how to pull specific values… Read more »
Powershell offers a few cmdlets targeted to managing a remote server and setting the service state as well as the startup. We will run through a few examples of getting the service information and than setting their state as well as the startup option. These scripts requires Administrator access to… Read more »
Powershell has a nice set of Cluster cmdlets that make life a little easier when attempting to inventory your clustered environment. Many of these cmdlets can be executed remotely from a server or workstation and use a virtual name but some cmdlets require you to execute them on the cluster… Read more »
While working with Powershell I have begun to move away from using the SSMS GUI to get basic details on SQL instances. Management Studio is an excellent tool but I find myself keeping Powershell open and using it for quick queries to answer the basics. One of the basics is… Read more »
Working in Powershell with dates, time and time zones is critical if you have data or automated processes that reference different geographical areas. Understanding how to compare these dates with and without a time value can mean the difference between an incorrect report or failing processes. We have written before… Read more »
The python dictionary is similar to an associative array but an array uses a numeric index. The Dictionary is an unordered key-value pair and the values can be any type of Python data. In our example, we create a Dictionary and unique keys to assign values. Simple Dictionary Example We… Read more »
We can use a commandlet called get-sqlinstance to quickly query an existing SQL instance on a remote server for more information. The administrator often needs to inventory the server environment and that includes identifying the SQL servers as well as all of the instances. Another useful commandlet for SQL Server… Read more »
Another method to query a remote SQL instance and collect DB details is the get-sqldatabase commandlet. Using the “-ServerInstance” parameter you can easily summarize the databases on that instance. This commandlet may require you to import a new module if you have not already. To just get a list of… Read more »
Working with strings is important when handling automation processes especially regarding server names and the SQL Server instances. Administrators define their own naming conventions and usually stay away from unique characters in the string. With SQL Server we have a slash “\” that is required to define the SQL instance… Read more »
We are looking at the Lookup Transformation in SSIS and we will try to clarify the Match and No Match options. The Lookup object allows you to “look” at the destination table and decide where to route records based on whether they are present or not present. Lookup Transformation The… Read more »
For this example we will be connecting to a SQL Server database using the Visual Studio Code (VS CODE), Windows Authentication and the pyodbc module. This is also similar to our recent post on connecting to MySQL using Visual Studio code in Linux and this post has more info on… Read more »
Random strings, characters and ID’s are used all the time in development and especially in Database work where some data needs to be unique or uniquely identified. This example code was written many years ago to generate passwords in SQL 2008 with certain complexities for automation purposes using the RAND()… Read more »
We touched on using parameters in recent post so I wanted to expand on that and show an example for including variables in the SQL statements. If your not familiar with setting up a SQL connection in python, see our post called How to Connect SQL Server in Python. Or… Read more »
Using Active Directory Groups (AD) for SQL Server authentication makes managing access permissions easy. Instead of managing individual login permissions the DBA can recommend using some AD groups that can contain many users and the login and permissions are assigned only one time. I have limited knowledge of Active Directory… Read more »
One of the more common approaches to ETL is using comma separated (csv) files. It’s easy to understand copying files and the file contents too. In this example we are using a few cmdlets to import a CSV file into a Datatable. We will grab the header info from the… Read more »
The more often you work with datatables in Powershell the more opportunities appear where you can utilize more features. We will look at the Merge method as well as updating data and adding new columns to an existing datatable. For this example we will be using the “WideWorldImporters” (WWI) database… Read more »
