Database Connection Test (DBTEST)

The Database Connection Test (DBTEST) tool is designed to show connection timings and command execution timings, as well as being able to perform a connection reliability test.

Use this tool to test connections/commands in an ad hoc manner and eliminate/confirm the failing application as being related to the cause of the issue.

Can connect to non-SQL Server databases via ODBC or OLE DB.

Usage

     DBTEST [-type SqlClient|ODBC|OLEDB] -connect "connectionstring"
            [-command "command"] [-timeout seconds] [-cursor Firehose|DataSet|None] [-top rows-to-display]
            [-repeat n] [-delay seconds] [-stopOnError]

     Default type    = SqlClient
     Default timeout = 30 seconds (0 = infinite CommandTimeout)
     Default cursor  = Firehose
     Default top     = 10 (display all rows = -1) - applies to DataSet cursor only
     Default repeat  =  1
     Default delay   =  1

     -stopOnError terminates repeated commands after the first error

EXAMPLES:

     DBTEST -connect "server=SQLProd01;database=NorthWind;integrated security=sspi" -command "select * from customers"
     DBTEST -type ODBC -connect "DSN=Accounting;trusted_connection=yes" -command "select * from customers" -cursor dataset -top 20
     DBTEST -type OLEDB -connect "Provider=SQLNCLI11;Data Source=SQLProd01;Initial Catalog=NorthWind;Integrated Security=SSPI" -repeat 100 -stoponerror

Pipe Results to a File:

     DBTEST -connect "server=SQLProd01;database=NorthWind;integrated security=sspi" -command "select * from customers" > c:\temp\dbtest.log

Installation

Download version 1.0.1200.0 and unzip into a folder. It is ready to run from the command-line.

Sample Output

C:\Tools> dbtest -connect "server=con2016;database=NorthWind;integrated security=sspi" -command "select * from customers" -cursor dataset -top 5 > c:\temp\dbtest.log

Database Test v1.0.1198.0
Run on: 11/04/2021 04:29:41 PM
Run by: MEGACORP\User1

Command Line Arguments: -connect server=con2016;database=NorthWind;integrated security=sspi -command select * from customers -cursor dataset -top 5
Provider Type:          SQLCLIENT
Command Timeout:        30 seconds
Cursor Type:            DATASET
Rows to Display:        5

11/04/2021 04:29:41.8199196 PM  Beginning Test ...
11/04/2021 04:29:41.8668277 PM  Connected successfully in 46 milliseconds.
11/04/2021 04:29:41.8668277 PM  Executed command and filled DataSet successfully in 0 milliseconds. 91 rows read.
11/04/2021 04:29:41.8668277 PM  Approximate memory usage: 475,104 bytes

    CustomerID  CompanyName                         ContactName         ContactTitle          Address                        City         Region  PostalCode  Country  Phone           Fax           
    ----------  ----------------------------------  ------------------  --------------------  -----------------------------  -----------  ------  ----------  -------  --------------  --------------
    ALFKI       Alfreds Futterkiste                 Maria Anders        Sales Representative  Obere Str. 57                  Berlin               12209       Germany  030-0074321     030-0076545   
    ANATR       Ana Trujillo Emparedados y helados  Ana Trujillo        Owner                 Avda. de la Constituci¢n 2222  M‚xico D.F.          05021       Mexico   (5) 555-4729    (5) 555-3745  
    ANTON       Antonio Moreno Taquer¡a             Antonio Moreno      Owner                 Mataderos  2312                M‚xico D.F.          05023       Mexico   (5) 555-3932                  
    AROUT       Around the Horn                     Thomas Hardy        Sales Representative  120 Hanover Sq.                London               WA1 1DP     UK       (171) 555-7788  (171) 555-6750
    BERGS       Berglunds snabbk”p                  Christina Berglund  Order Administrator   Berguvsv„gen  8                Lule†                S-958 22    Sweden   0921-12 34 65   0921-12 34 67 

Test Completed.

All Tests Complete.

Note: if running a connection reliability test, adding Pooling=false to the connection string will force the tool to make a new connection for each test.

Batch File Return Codes

If you run DBtest in a batch file these return codes can help control your execution flow.

0 = Success
-1 = Argument Error
-2 = SQL Exception

Requires the StopOnError command line parameter to be specified.

:begin
DBTEST -connect "Data Source=.;Initial Catalog=master;Integrated Security=SSPI" -stoponerror 
if %errorlevel% == -2 goto endcap
if %errorlevel% == -1 goto endcap 

goto begin

:endcap