Solved

Powershell Help

Posted on 2014-11-13
5
163 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 76

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 18

Expert Comment

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

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 76

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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 …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now