-
Notifications
You must be signed in to change notification settings - Fork 43
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.
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
Download version 1.0.1199.0 and unzip into a folder. It is ready to run from the command-line.
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.
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