I want to test a connection from Microsoft Visual Basic (VB) 6.0 to SQL Server. I tried to conduct the test through SQL-DMO's VerifyConnection property but was unsuccessful. Listing 1 shows the code I used. Where did I go wrong?
If you need to know whether your VB—SQL Server connection is working, you can use the PingSQLServerVersion method, as Listing 2 shows. This code executes faster and consumes fewer resources than your script. In addition, VerifyConnection verifies only a connection that has already been made. In the call you make to VerifyConnection, the parameter should be a boolean instead of an int. Also, your code line
oSQLServer.StatusInfoRefetchInterval (SQLDMOStatInfo_AutoVerifyConnection) = 10
reduces performance because you're using 10-second intervals instead of 30-second intervals to determine the connection status.