Solved

Powershell Help

Posted on 2014-11-13
5
175 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 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 49

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
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…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

685 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