Simple method to test system DSN's?

Question

Is there a simple way to test that a System DSN setup on Windows Server 2000/3 is configured and connects properly? ODBC Data Source Administrator doesn't appear to have anything for this.

Answer 1

As crude as it is, I always just used the ODBC Connection in the Control Panel. On the final page of the setup wizard there is a "test Connection" button.

EDIT: Just re-read your question and realized we are referring to the same control panel applet. Yes, there is the ability to test a system DSN connection using the ODBC Data Source Administrator. Edit Connection, next through all the windows and the final screen will have a Test Connection option.

Answer 2

As Serapth suggested there is a test button in the last wizard step of ODBC data source administrator that will help you test the connectivity to the database. If you are looking for an easy way & would like to eliminate going through all those wizard steps, you may use the following vb script. However, be warned, this script requires the DSN to be configured with trusted connection. Also, this script was tested only with MSSQL server.

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

objConnection.Open "DSN=YOUR_DSN;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT count(*) FROM YOUR_TABLE_NAME" , objConnection, _
    adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Wscript.Echo objRecordSet.RecordCount
objRecordset.Close
objConnection.Close

Please refer to Microsoft's technet site for more scripting samples.