Another common request for DBA’s is to search for or validate that certain tables or other objects exist and in what database. This post is a follow-up to our “Check If Database Exists Using Powershell” post where we search look for a database remotely using the “invoke-sqlcmd” commandlet. Note: This… Read more »
The AI bot can be used to learn T-SQL for the junior Developer or provide more complex queries to save time on coding. Here we are using the WideWordImporters sample database created by Microsoft. Chat GPT is already aware of the database so we do not need to upload or… Read more »
The OpenAI ChatGPT can be used to design and create a database. We tested this out to see how far we can go with automating the process of creating tables using only natural speaking language and allowing ChatGPT to create the actual code. The results are amazing and any MSSQL… Read more »
The more I work with the SSIS catalog the more I appreciate that it’s design is an improvement over the SSIS in SQL 2008. Were going to avoid the heavy explanation and just walk through creating the SSIS catalog, folders and assigning permission for your developers. This post is more… Read more »
We use simple T-SQL commands to retrieve the PATH from our msdb backup records. T-SQL doesn’t seem to have a method to slice up a folder/file path and return the parent folders so we have to use CHRAINDEX, SUBSTRING and REVERSE to pull out the file name leaving just the… Read more »
We are working on Amazon (AWS) Cloud service and attempting to setup a new MySQL instance that we can connect to remotely. This empowers the DBA to use their local desktop tools (IDE) like MySQL Workbench or DBeaver to create and manage the databases in the RDS Cloud. Setup Summary:… Read more »
The Azure Cosmos DB is a NoSQL database or “nonrelational” database where the data is stored in Documents and these documents are organized as collections. The benefit of using a NoSQL database like Cosmos DB is to handle large amounts of data that changes frequently and needs to be available… Read more »
We generate a random password(s) that can be used in database projects or to automate login password creation. In this example we are using Python 3.8 but I believe anything above Python 3 should work. My challenge was that random generators tend to use all of the characters available including… Read more »
As a DBA we are often tasked to analyze tables, views and procedures that are unfamiliar such as a vendor provided database. This is a simple script working with SQL Sever to find a stored procedure containing a text string. It’s very useful for auditing bad procedures or looking for… Read more »
Developer will create their web.config, populate the SQL Sever connection settings and rarely return to that file again. This is usually after contacting their DBA to get the server, port and SQL version information as well as permissions to access the database. Then they “google it” for the connection string… Read more »
Using Powershell we can connect to and return a simple T-SQL query result set using your existing SQL code. We are using the invoke-sqlcmd commandlet for this example so I included the Powershell command to install. (you may need to start VSCode as Administrator). The $resultdata variable will contain the… Read more »
This is a simple example on converting List data to a standard Comma Separated (CSV) file using Python 3. We are using the Python Standard library and the CSV module to create our CSV file. This module is loaded with options so we are just including the more often used… Read more »
We are looking at the result set for invoke-sqlcmd which normally returns an array of objects. We can re-assign the value to an array or deal with the object and their methods using a different notation. To identify the result set “object” you can execute the following powershell. Notice using… Read more »
Strings tend to be difficult to handle when attempting to locate or modify specific words and characters. Powershell provides quite a few tools to search for special characters, replace words, remove spaces and much more.Here is a quick example to test and find a word within a string, assuming we… Read more »
This script is useful in more complex processes where you want to check if the server is online before attempting to execute something remotely or connecting to the SQL database. Many commands do not handle timeouts well and this test will keep things moving if there is a down server…. Read more »
In a more distributed environment there is a need to use Powershell script to connect to multiple SQL servers and execute queries. The results can include service or Database records that are then combined into an array to be manipulated or displayed. This is very useful to the modern SQL… Read more »
Even in smaller environments the DBA will run into large databases and very large tables. This can be due to neglect, bad table design or just a recent change with unexpected results. When dealing with these large tables we often need the fastest way to get the table row count…. Read more »
Dealing with dates in SQL Server is a critical skill and required when developing reports that depend on the Date, Day and Hour to be accurate. This includes logically moving through the year or bracketing results inside a month in your code. Here are some examples of how to get… Read more »
The Python List() can handle multiple types of data making it very useful for different applications. The list() can cut down on lines of code and the need for casting or converting data types. This is especially convenient if we have database queries that include different types of data, numbers,… Read more »
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 »
