Solved

Powershell Help

Posted on 2014-11-13
5
181 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 78

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 50

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 78

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

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
This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

734 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