Solved

Powershell Help

Posted on 2014-11-13
5
172 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 47

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

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

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 46
exchange 2013 missing get-OutlookAnywhere PowerShell command 2 26
Linked Server Issue with SQL2012 3 26
write-output without carrier return 1 16
This article will help you understand what HashTables are and how to use them in PowerShell.
A procedure for exporting installed hotfix details of remote computers using powershell
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

822 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