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'[email protected]@servername, 'Service Name'[email protected]@servicename, " _    & "'Language'[email protected]@language, 'Language ID'[email protected]@langid, 'Connections opened so " _    & "far'[email protected]@connections, 'Max connections'[email protected]@max_connections, 'Process " _    & "ID'[email protected]@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'[email protected]@Pack_Sent, 'Packets received'[email protected]@Pack_received, " _    & "'Packet errors'[email protected]@Packet_Errors, 'Disk Reads'[email protected]@total_read, 'Disk " _    & "Writes'[email protected]@total_write, 'Disk Errors'[email protected]@total_errors, 'CPU " _    & "Ticks'[email protected]@cpu_busy, 'I/O Ticks'[email protected]@io_busy, 'Idle Ticks'[email protected]@idle", cnFor Each fld In rs.Fields    Debug.Print fld.Name & " = " & fld.ValueNext

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: