Establishing number of times an application connects to SQL Server 1

[StevenK]

We have an application (written in Delphi 5) that works against a SQL Server 7.0 database. I have noticed that when the application is running on a client machine (with a number of forms open) that I am seeing what I interpret to be multiple connections from the same machine to the database ‘DATABASE1’ that contains the data for the application. These connections come from the same network address – as viewed through SQL Enterprise Manager – Management – Current Activity – Process Info. This has led me to believe that we have multiple connections being opened through the application. There should only be one TDatabase component in the Delphi application through which all database access is done – it is my belief that this may not be the case. We need to establish first if we are making multiple connections then can look at the code to determine the issues.
When running other applications I have developed I only see one open connection.
Is there a way I can monitor when a database is connected to from an external application ?
Does one of the system tables in the ‘master’ database store the currently open connections and in which case can I set a trigger against it to insert an entry into a new table that we can later review to determine as and when connections are being made (and from which network address) ?
How wise is it setting triggers against tables in the ‘master’ database ? Are they best left untouched and only used for data retrieval ?

Thanks in advance for any pointers with this.
Regards,
Steve

 

[tlbroadbent]

1) You can't create triggers on system tables.

2) Depending on the method of connection and whether the application updates the database or not, your application may open the database once but that may result in two or three SQL server connections.

3) Perhaps the easiest way to to track connections is to create a trace in SQL Profiler that tracks login and logout events. You can even filter on the application. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[StevenK]

Thanks for the pointers - we may well put the SQL Profiler to use later today.
My understanding of the database connection through applications was that we could get away with only one connection. This application under review is making 3 (as seen through the Current Activity - Process Info) connections whereas a further application of mine, that retrieves data from, updates data and inserts data into another database only ever has one connection listed.
The method of connection is through an ODBC DSN set in a standard Borland Delphi 5 TDatabase component.
Is there any way of indicating to the user (at the client machine) as and when they are making a connection to the database - or is it a case of watching the activity through SQL Profiler.
You say that the database may only be connected to the once, but then may result in two or three connections - I can't understand this. Surely connecting once means one connection ??
I'm obviously missing something with this theory.
Regards,
Steve

 

[tlbroadbent]

I'm not familiar with Delphi. I know that applications using Microsoft Jet and ODBC could open up to 3 connections with one database open command.

A quick look in the Delphi News Groups indicated that TTables open creates separate connections whereas TQuerys does not. I don't know if that's even applicable in your case.

I do know that SQL 7 differentiates between client licenses and connections. We set the number of connections on our SQL Server to 3 times the number of licenses because 1) an application can open multiple connections and 2) a client can open multiple applications.

It might be worth your time to ask some questions in a Delphi forum. (forum102?) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[StevenK]

Thanks for this information Terry. We've just run a test application that proves the point. Working through a single TDatabase component, regardless of the number of TQuery components used we only ever see one visible connection. When using multiple TTable components (working through the same TDatabase component) we see additional connections as more tables are opened.
Your information has proved very useful.
Thanks again.
Steve