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
pae2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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.
0
pae2Author Commented:
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
0
pae2Author Commented:
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

0
How the Cloud Can Help You as an MSSP

Today, every Managed Security Service Provider (MSSP) needs a platform to deliver effective and efficient security-as-a-service to their customers. Scale, elasticity and profitability are a few of the many features that a Cloud platform offers. Register today to learn more!

DBAduck - Ben MillerPrincipal ConsultantCommented:
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.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Something is wrong on your side. Yes, adding [CmdletBinding()] outside of Param enables -debug and -verbose output options. And it works for me with your code, if called correctly. Your example call throws an error:
>> test-sqlconnection -Servers -verbose "localhost"
Test-SQLConnection : Missing an argument for parameter 'Servers'. Specify a parameter of type 'System.String[]' and try again.
At line:1 char:28
+ test-sqlconnection -Servers <<<<  -verbose "localhost"
    + CategoryInfo          : InvalidArgument: (:) [Test-SQLConnection], ParameterBindingException
    + FullyQualifiedErrorId : MissingArgument,Test-SQLConnection

Open in new window

Correct usage:
>> test-sqlconnection -Servers "localhost" -verbose
Exception calling "Open" with "0" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
At C:\Users\clemo\AppData\Local\Temp\c62fb64f-6bdf-47eb-abd8-fd125fec7d8e.ps1:22 char:16
+   $sqlConn.Open <<<< ()
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException
 
VERBOSE: Not available Server: localhost

Open in new window

Both used your original code, and $VerbosePreference = "SilentlyContinue", which means "do not show". As you can see you also need to suppress error messages on Open(), e.g. by using
  try { $sqlConn.Open() } catch {}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pae2Author Commented:
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
0
pae2Author Commented:
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
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Wait, what is .\check.ps1?  the function is called Test-SqlConnection.  Is it in a file called check.ps1?

If this function is in a file, then you would dot-source it and then execute the function.  I did that and it works fine with -verbose

. .\check.ps1
Test-SqlConnection -Servers "SERVERNAME" -OutputTo "String" -verbose

Open in new window


You should see the output of the Verbose.  That is what I did and it works fine.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Well spotted, Ben. Of course calling the script does not do anything visible, you need to call the function defined after doing so.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
All info needed is contained in this thread - complete and correct.
I suggest to accept http:#a40708046 and http:#a40714151
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.