Category: NON SQL

Zoomit – A tool to boost your presentation skills

If you are community player or even you want to present something to someone, you may need to zoom in and zoom out certain area of your presentations to make the audience clear about your key areas. I always find it difficult if I share a screen with someone on explaining things and showing it to them. Power point presentation has an option to zoom-in and zoom-out, however, this cannot be applied for other software like SSMS etc. Today, let us quickly see some of the options that could help us in similar situations.

There is a default option in SSMS to zoom out, however, its applicable for only Query part as below:

If you need something for results or any other area, then there is no default way of achieving in SSMS. To support in such situation, I would like to introduce a tool called Zoomit by Microsoft. You can download the tool from the below link:

https://docs.microsoft.com/en-us/sysinternals/downloads/zoomit

You can explore this tool and share your thoughts. If you are already using any other tools, please share the details, happy to explore and play with it. Looking forward your comments.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Windows Power Shell Script to run SQL Files in Folder against SQL Server Database

Problem Statement:-

At times, we may need to run SQL files present in a folder against SQL Server Database. Since there are no built in feature available as of now, here is our try with a powershell script.

Pre-requisites:-

  1. Save the (.bat) Batch script & (.ps1) PS Script in same folder in which all the SQL Files are stored.
  2. The Machine should have the SQL Server instance and Power shell tool.
  3. Ensure the parameters values are verified in (.bat) Batch file

Scenarios Covered:-

i) All key values are handled with parameters
ii) If database not exist it will throw error and comes out of PS
iii) If any script has issues/failed, it will throw error and comes out of PS
iv) If we want to run the sql scripts in subfolder as well, then “Includesubfolders” parameter set to 1
v) Batch file automatically retrieve the root path

PS Script:-

$Scriptpath  = $args[0]
$Server =  $args[1]
$database = $args[2]
$user= $args[3]
$pwd= $args[4]
$Includesubfolders=$args[5]

Function IsDBInstalled([string]$Server, [string]$database)
{

 $t=Invoke-Sqlcmd -ServerInstance $Server -Username  $user -Password  $pwd -Database "master" -Query "select 1 from sys.databases where name='$database'" -OutputSqlErrors $true 
  if (!$t) {
            Write-Host "Failed to connect to [$database] database on [$Server]" -BackgroundColor darkred 
            Write-Error "Failed to connect to [$database] database on [$Server]" -ErrorAction Stop
  } else {
              
            write-host "[$database] Database exists in SQL Server [$Server]" -BackgroundColor blue -ForegroundColor black
  }
}

IsDBInstalled $Server $database

if($Includesubfolders -eq 1) {
$subscripts = Get-ChildItem $Scriptpath -recurse | Where-Object {$_.Extension -eq ".sql"}
foreach ($s in $subscripts)
    {   Write-Host "Running Script : " $s.Name -BackgroundColor green -ForegroundColor darkRed
        $tables=Invoke-Sqlcmd -ServerInstance $Server -Username  $user -Password  $pwd -Database  $database -InputFile $s.FullName -ErrorAction 'Stop' -querytimeout ([int]::MaxValue)
        write-host ($tables | Format-List | Out-String) 
        }
} else {
$scripts = Get-ChildItem $Scriptpath | Where-Object {$_.Extension -eq ".sql"}
foreach ($s in $scripts)
    {   Write-Host "Running Script : " $s.Name -BackgroundColor green -ForegroundColor darkRed
        $tables=Invoke-Sqlcmd -ServerInstance $Server -Username  $user -Password  $pwd -Database  $database -InputFile $s.FullName -ErrorAction 'Stop' -querytimeout ([int]::MaxValue)
        write-host ($tables | Format-List | Out-String) 
        }
}

Batch Script:-

@ECHO ON
SET root=%cd%
SET PSScript=%root%\RunSQLFiles.ps1
SET PowerShellDir=C:\Windows\System32\WindowsPowerShell\v1.0
CD /D "%PowerShellDir%"

SET path=%root%
SET "machine=sqlserversample45"
SET "db=sample"
SET "user=username"
SET "pwd=password"
SET "Includesubfolders=0"

Powershell -ExecutionPolicy Bypass -Command "& '%PSScript%' '%path%' '%machine%' '%db%' '%user%' '%pwd%' '%Includesubfolders%'"

Pause
EXIT /B

Output:-

If Database Not Exists:

If Database Exists:

If Any Script has Error:

If “Includesubfolders” parameters set to “1”

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

On-Demand Performance Test Rig with JMeter

These days it has become necessary to execute the performance test at low cost. This blog details about the how to setup  “On-Demand & low cost” Performance test rig on Azure and execute performance test with it.

Primarily, to setup the on-demand performance test rig below are the prerequisites

  1. JMeter scripts needs to be check-in to GIT Repo and keep it updated
  2. Azure Subscriptions with the Resource Group created in which the on-demand test rig will be created.
  3. Azure container Registry (ACR) – The JMeter docker image will be stored here
  4. JMeter plugin Referencehttps://github.com/adrianmo/jmeter-backend-azure

Azure CLI Command must include the following:

  1. Azure container Registry with JMeter Image Path
  2. Region in which the Container need to be spin up
  3. CPU, need for the load test execution
  4. Memory in GB, needed for the test execution
  5. GIT Repo Mount Path

Referencehttps://docs.microsoft.com/en-us/cli/azure/container?view=azure-cli-latest

How does it work?
JMeterTestRig

There will be more posts to come on this topic, until then stay tuned and stay safe!!!

How to get list of filenames in a folder – Command Shell Script

There may be some situation where you want to find the list of all filenames whose patterns are like “*.txt”, “File*.csv”, “*.html”.., etc. In such scenario you can use the below command shell scripts to get the desired results which you are looking for.

1) To get the list of all filenames and folder names present in a folder “C:\testdata\”

cd "C:\testdata\"
dir /b /s

Output:-

2) To get all the filenames of pattern “*.txt” or “*.html” from folder “C:\testdata\”

dir /b /s "*.txt"
dir /b /s "*.html"

Output:-

3) To get all the filenames of pattern “*.txt” from folder “C:\testdata\” and store the corresponding results in the text document

dir /b /s "*.txt" > output.txt
dir /b /s "*.txt" > "c:\testdata\test\outputs.txt"

Output:-

How to identify NetBIOS Name of a computer?

Its not a SQL Server related, but I found it difficult to identify a way to get the NetBIOS name of a computer, hence sharing the same.

Recently, while troubleshooting an issue with MSDTC, I had to use DTCPing tool. I do not cover the DTCPing tool here, but will share more about it in another post. DTCPing tool was expecting NetBIOS name instead of IP address.

So, here are few options to identify the NetBIOS Name of a computer.


Using T-SQL

Select serverproperty('ComputerNamePhysicalNetBIOS')
(Or)
exec master..xp_regread 'HKEY_LOCAL_Machine', 'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\','ComputerName'

Using Windows

nbtstat -n  (-n   -- Lists local NetBIOS names.)

Thats it for now, but if you think there is any other method, post it in the comment section.