asked on

powershell sql server connection script

I am new to PS. Hopefully someone can help me out. I can execute the following script without an error using the PSISE, however it does not output what is in the Write-Verbose statements. Can someone please tell me why?

FUNCTION Test-SQLConnection {
[CmdletBinding()]

Param ([Parameter(Mandatory=$true)] [string[]] $Servers, [ValidateSet("String")] [string] $OutputTo)
 
BEGIN {}
 
PROCESS {
 
    # loop through servers

    foreach ($s in $servers){

# build connection String to test connection based on server name and Windows Authentication
# domain credentials are present in the process making the connection
# the account using in the process (or if using the NETWORK ONLY option in RUNAS) will be used to make the connection to SQL Server using integrated security

$connectionString = "Data Source=$s;Integrated Security=true;Initial Catalog=master;Connect Timeout=3;"

# Try and connect to server
  $sqlConn = new-object ("Data.SqlClient.SqlConnection") $connectionString
  $sqlConn.Open()

# Test Parameter -OutputTo If its String run

IF ($OutputTo -eq "String")
{
# If connection was made to the server
 IF ($sqlConn.State -eq 'Open')
  {
# write to output and verbose
  $sqlConn.Close();
  Write-Output $s
  Write-Verbose "Available Server: $s"
  }
}

ELSE {
# If connection failed write to verbose
Write-verbose "Not available Server: $s"
     }

	}

     } #EOP

} #EOF

Open in new window

Thanks,

pae2

Qlemo

Verbose output needs to be enabled, e.g. by setting the preference vars (see https://technet.microsoft.com/en-us/library/hh847796.aspx), or by adding -verbose while calling the function.

pae2

ASKER

I tried adding -verbose while calling the function and it didn't work, e.g.: .\check.ps1 -Servers -verbose SERVERNAME did not show anything, but it did execute. I read here http://blogs.technet.com/b/heyscriptingguy/archive/2014/07/30/use-powershell-to-write-verbose-output.aspx that if "all I need to do is add the [cmdletbinding()] attribute over the Param() statement, and I automatically gain support for Write-Verbose". I have both of those and it didn't work; any other ideas Qlemo?

Thanks,

pae2

pae2

ASKER

I also tried the following and it didn't work:

IF ($OutputTo -eq "String")
{
$VerbosePreference="Continue"
# If connection was made to the server
 IF ($sqlConn.State -eq 'Open')
  {
# write to output and verbose
  $sqlConn.Close();
  Write-Output $s
  Write-Verbose "Available Server: $s"
  }
}

Open in new window

DBAduck - Ben Miller

You cannot use the statement like this
.\check.ps1 -Servers -verbose SERVERNAME

It would have to be like this
.\check.ps1 -Servers SERVERNAME -Verbose

PowerShell was thinking that nothing was in the -Servers variable because there was a -verbose right after it. When putting the SERVERNAME in the spot right after the parameter name then the -verbose it should fix it.

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pae2

ASKER

DBAduck - Ben Miller,

I tried .\Check.ps1 -Servers "SERVERNAME" -Verbose and it didn't work. The script runs and the PS ISE says Completed, but it's not showing any output, i.e. Available or Not Available. Do you have any other ideas?

Thanks,

pae2

pae2

ASKER

Qlemo,

Were you able to get output? I am unable to. All it says when trying to run it in the PS ISE is Completed and there are no errors. This is how I called the script which contains the Test-SQLConnection function in the PS ISE: .\Check.ps1 -Servers "SERVERNAME" -Verbose and Thanks,

pae2

SOLUTION

DBAduck - Ben Miller

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Qlemo

Well spotted, Ben. Of course calling the script does not do anything visible, you need to call the function defined after doing so.

Qlemo

All info needed is contained in this thread - complete and correct.
I suggest to accept http:#a40708046 and http:#a40714151