Powershell script MSSQL

This script can be used to automatically import sql files older than 20 minutes into the MSSQL database. It is helpful if you enable SQL Text logging for high-performance configurations. In order to execute this script, you have to save it with the .ps1 extension, and you must enable power shell script execution for windows in the group policy editor (or you must execute the script as Administrator).

Video 1 - How to use powershell script in an MSSQL database (Video tutorial)

Create the following MSSQL table

CREATE TABLE messagereport
(
    logid int IDENTITY(1,1),
    messageid varchar(64),
    fromconnection varchar(64),
    fromaddress varchar(64),
    requestedconnection varchar(64),
    toconnection varchar(64),
    toaddress varchar(64),
    messagetext varchar(1024),
    route varchar(64),
    datecreated datetime,
    datedeliverytoconnectionsuccess datetime,
    datedeliverytoconnectionfailed datetime,
    deliverytoconnectionreference varchar(256),
    deliverytoconnectionstatusmessage varchar(1024),
    datedeliverytorecipientsuccess datetime,
    datedeliverytorecipientfailed datetime,
    deliverytorecipientstatusmessage varchar(1024),
    status varchar(64)   
)

The first step is to create the messagereport table in the database. All outgoing and incoming messages will be stored in this table and updated according to their status. If you have already created this table, you can skip this step. Copy the create table script above and execute it in your database as you can see it on the Figure 1.

create messagereport table
Figure 1 - Create messagereport table

Save the following powershell script as "dbimport.ps1"

The next step is to create the "dbimport.ps1" file in the C:\Ozeki folder as shown in Figure 2. This file will contain the script that periodically runs SQL queries in the database.

create the script file
Figure 2 - Create the script file

$ProcessedDir = "C:\Ozeki\Processed"
New-Item -ItemType Directory -Force -Path $ProcessedDir

#endless loop
while (1) {
	#list files older than 20 minutes
	#make sure the time is greater than the period you used in the reporting config
	Get-ChildItem "C:\Ozeki\Reporting_SQL" -Filter *.sql | 
		Where{$_.LastWriteTime -le (Get-Date).AddMinutes(-20)} |
	
	Foreach-Object {
		#load the contents of the file into the mssql database
		Write-Output $_.FullName
		$Text = Get-Content -Path $_.FullName
		Invoke-Sqlcmd -ServerInstance MyComputer\MainInstance -Database MyDatabase
		-Query "$Text" -Username "MyUser" -Password "MyPass"

		
		#move the processed file into the processed directory
		Move-Item -Path $_.FullName -Destination $ProcessedDir
	}
	
	#sleep for 5 seconds
	Start-Sleep -s 5
}

Copy the power shell script above and paste it in the "dbimport.ps1" file as you can see it on the Figure 3.

paste the script into the file
Figure 3 - Paste the script into the file

Finally, modify the script according to your own MSSQL database. In the Server instance, define the computer and instance name of the MSSQL database. Enter the username and password you want to use, and replace "Mydatabase" with the name of your database (Figure 4).

change mysql command
Figure 4 - Change MySQL command

Execute the script as administrator

powershell -ExecutionPolicy Bypass -File dbimport.ps1

Now execute the script in the power shell by running the above command as you can see it in the Figure 5.

run powershell script
Figure 5 - Run powershell script

You can see that the script executes all the SQL files what is older than 20 minutes in the 'C:\Ozeki\Reporting_SQL' folder one by one (Figure 6).

script execute sql queries
Figure 6 - Script execute SQL queries

Every SQL file that the script has run is moved to the Processed folder so that no queries are lost (Figure 7). From here, you can delete them manually if you no longer need them.

sql files moved to processed folder
Figure 7 - SQL files moved to processed folder

Finally, if you look in the database you can see that all the messages have been inserted in the messsagereport table as the Figure 8 shows.

messages inserted into database
Figure 8 - Messages inserted into database

More information