SQL with Powershell

In this blog I will show you how you can connect to all your instances of sql within powershell.

Powershell is nice and seems to be popular these days. So I wrote a handy little tool to connect to instances through it. I think it is much quicker and easier to use this tool rather than remote desktop or open enterprise manager or sql server manager if you are just checking a server.

here is the code

$file = get-content "file.txt"
# checks file if not found then terminates
if (-not($file)) { write-error($file+ " not found. Terminating .... "); exit}# matches the string ie type in any string for yout instance
$match = $file Select-String (Read-Host "search string") # if the match is found then this will run the select if ($match.count -gt 0) { $inst = @() for ($i = 0; $i -lt $match.count; $i++) { $temp = "" $line = $match[$i].tostring() for ($j = $line.length-1; $j -ge 0; $j--) { if ($line[$j] -eq ":") { break } $temp = $line[$j] + $temp } $inst = $inst + $temp write-output ("Press "+($i+1)+" for : " + $inst[$i]) } $inp = "" $flag = "true" do { $inp = read-host("Enter choice : 1 - "+$match.count) if ($inp -lt 1 -or $inp -gt $match.count) { $flag = "false"; write-error ("Invalid entry. Enter between 1 - "+$match.count) } else { $flag = "true"; } } while ($flag -eq "false") write-output ("Logging on to the SQLSERVER you requested: "+$inst[$inp-1])      if ($inst[$inp-1] -ne "") {        $inst[$inp-1] + ""; sqlcmd -S $inst[$inp-1] -E    } }

Instructions

This Powershell script well allow you to search for any instance or sql server you have or have enterned in to the file. once you run this powershell script it will ask you for a string type in what ever instance you want it mssql then it will give you a number of instances you can then press 1 to what ever number the server is on and it will log you in.

PS C:\CheckSQLServer\sql> ./loginsql.ps1search string: IBTKYPSP32Press 1 for : IBTKYPSP32A006Press 2 for : IBTKYPSP32A017Press 3 for : IBTKYPSP32A021Press 4 for : IBTKYPSP32A026Press 5 for : IBTKYPSP32A028Press 6 for : IBTKYPSP32A031Press 7 for : IBTKYPSP32A100Press 8 for : IBTKYPSP32B017Press 9 for : IBTKYPSP32B026Press 10 for : IBTKYPSP32B031Press 11 for : IBTKYPSP32A034Press 12 for : IBTKYPSP32B034\HOSTED_P1Press 13 for : IBTKYPSP32A036\HOSTED_P1Press 14 for : IBTKYPSP32B037\HOSTED_P1Enter choice : 1 - 14: 14Logging on to the SQLSERVER you requested: IBTKYPSP32B037\HOSTED_P1IBTKYPSP32B037\HOSTED_P11> sp_who2> gospid ecid status loginamehostnamecmd requ------ ------ ------------------------------ -------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------- ----1 0 background sa
RESOURCE MONITOR2 0 background sa
RESOURCE MONITOR3 0 background sa
RESOURCE MONITOR4 0 background sa
RESOURCE MONITOR5 0 background sa