devxlogo

Retreive information about SQL Server

Retreive information about SQL Server

You may retrieve a few important information about a connection to SQL Server by querying the server through appropriate @@ global variables. For example, the following routine takes an ADO Connection object that points to SQL Server and prints a few information about the connection:

Dim cn As New ADODB.ConnectionDim rs As New ADODB.RecordsetDim fld As ADODB.Field    cn.Open CONN_STRING   ' cn points to a SQL Server connection    rs.Open "SELECT 'Server Name'=@@servername, 'Service Name'=@@servicename, " _    & "'Language'=@@language, 'Language ID'=@@langid, 'Connections opened so " _    & "far'=@@connections, 'Max connections'=@@max_connections, 'Process " _    & "ID'=@@spid", cn' display connection infoFor Each fld In rs.Fields    Debug.Print fld.Name & " = " & fld.ValueNext

There are many other @@ global variables that can turn to be useful, especially for debugging and profiling reasons. For example, run this code to print diagnostic information about a SQL Server connection, including number of packets sent and received, disk read and write operations, and CPU ticks dedicated to SQL Server, to disk I/O and not used by SQL Server since when SQL Server itself was booted:

rs.Open "SELECT 'Packets sent'=@@Pack_Sent, 'Packets received'=@@Pack_received, " _    & "'Packet errors'=@@Packet_Errors, 'Disk Reads'=@@total_read, 'Disk " _    & "Writes'=@@total_write, 'Disk Errors'=@@total_errors, 'CPU " _    & "Ticks'=@@cpu_busy, 'I/O Ticks'=@@io_busy, 'Idle Ticks'=@@idle", cnFor Each fld In rs.Fields    Debug.Print fld.Name & " = " & fld.ValueNext

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist