Solved

Powershell Help

Posted on 2014-11-13
5
174 Views
Last Modified: 2014-11-16
Hi Guys,

I have created the following powershell script for getting the count of DB's and getting that all these Db's are online or not:

foreach ($svr in get-content "C:\Servers.txt")

{

  $con = "server=$svr;database=master;Integrated Security=sspi" 

  $cmd = "select state_desc as DB_STATUS,COUNT(name) as [TOTAL DB'S] from sys.databases group by state_desc"

  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

  $dt = new-object System.Data.DataTable

  $da.fill($dt) |out-null

  $svr

  $dt | Format-Table -autosize

}

Open in new window



The problem that I'm facing with the above code is, if I execute this code for the server in which I'm logged in, it executes fine, but if I provide other server names, I'm getting the following error:

Exception calling "Fill" with "1" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was n
ot 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 c
onnection to SQL Server)"
At C:\Users\C201578-db\Documents\Untitled1.ps1:13 char:11
+   $da.fill <<<< ($dt) |out-null
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Kindly look into this and please give your suggestions.

Thank You
0
Comment
Question by:Abhinav Singh
5 Comments
 
LVL 77

Expert Comment

by:arnold
ID: 40441966
It seems your client connect mode is named pipes versus tcp.
Run the sql config setting the client portion to use tcp/ip.
0
 

Author Comment

by:Abhinav Singh
ID: 40441984
Hello Arnold,

I just checked the sql config setting, the client on which I'm trying to query has been configured to use TCP/IP.

Thanks for replying.
0
 
LVL 19

Expert Comment

by:Peter Hutchison
ID: 40442095
Shouldn't you provide a username and password for connections to other servers?
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40442123
You are sure that all names in servers.txt has SQL Server installed on it?
Would help if you can post here an example of names that can be found in servers.txt (do not post real names).
0
 
LVL 77

Accepted Solution

by:
arnold earned 500 total points
ID: 40443422
The error referencing named pipes as well instances, the servers list may have to be of the format
servername\instance when non default instance is used.
The other issue deals with whether sql ports are open if windows firewall is in use.

The client config on the server where you are running this power shell script is where it needs to be configured to include tcp/ip

On the servers they need to have tcp/IP available.  Are you able from your workstation using ssms GUI to connect to every server in the list including their instance name?

Using the integrated security for SQL authentication presumes that you can the ssms access is using windows or sql credentials?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Utilizing an array to gracefully append to a list of EmailAddresses
"Migrate" an SMTP relay receive connector to a new server using info from an old server.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question