Script to Check SQL Server Connectivity

Written by VidhyaSagar. Posted in Scripts

Use this VB script to check SQL Server connectivity. You can add N number of SQL Server instances to verify its status.

Usage:

  • Download the sqlserverconnectivity.zip file and extract all the files to a folder
  • Make sure connectivity.vbs, serverlist.txt and model.xls files exists in the same folder
  • Open serverlist.txt file and add server name in each line.
  • Execute connectivity.vbs file, it will generate a new excel file with the status
  • In the excel created you can get the status in ServerStatus sheet and connectivity error details in Error sheet.

Success Report

server_connectivity_1

Failure Report

server_connectivity_2

VN:F [1.9.17_1161]
Rating: 0.0/5 (0 votes cast)
VN:F [1.9.17_1161]
Rating: 0 (from 0 votes)

Tags: , ,

Trackback from your site.

Comments (1)

  • VidhyaSagar

    |

    There is one bug with the connectivity vb script. I have used SQL Native client provider 10 version in connection string, if you don’t have that provider then the script will loop infinetly in the first servername. This has been found by Suresh. I have now fixed the connection string by using SQL OLEDB provider and below is the connection string used in that vb script

    Conn.Open “Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=” & inlist(0) & “;”

    Thanks Suresh for pointing this out!!

    Reply

Leave a comment

CAPTCHA Image
*

Recent Comments

VidhyaSagar

|

@Manu – I just created the procedure to explain how to find uncontained objects in that databases that’s why I didnt granted permission to that object.

manu

|

It seems you got execute error as you didn’t granted user “sagar” execute access on [dbo].[uncontained_entity] . I granted it execute access and it worked all fine.

AM I missing something here?

Thanks,

RA

|

Thanks for the script. I had database with space between the names.

I modified to use square brackets:

FROM [?].sys.database_principals a
LEFT OUTER JOIN [?].sys.database_role_members

and

from [?].sys.database_permissions join [?].sys.sysusers U
on grantee_principal_id = uid join [?].sys.sysobjects

Gangadhar NG

|

This tip is helpful enough for me.
thanks.

Zukunftsmusik

|

Thanks! That helped my a lot. But there’s a little mistake:
SQL_Latin1_General_CP1_CI_AS stands for “Latin1-General, case-insensitive, accent-sensitive [...]” therefore sort order 52.

If you want to set the sort order to 54 the correct collation would be “SQL_Latin1_General_CP1_CI_AI” ;)

Rating