Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SS7
Expertise: Intermediate
Jan 1, 2003

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.Connection
Dim rs As New ADODB.Recordset
Dim 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 info
For Each fld In rs.Fields
    Debug.Print fld.Name & " = " & fld.Value
Next
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", cn
For Each fld In rs.Fields
    Debug.Print fld.Name & " = " & fld.Value
Next

Francesco Balena
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date