SQL Server Profiler

The SQL Server Profiler is a tool with which you can monitor the communications between your applications and the SQL Server database. Earlier in this tutorial we looked at the TIMESTAMP columns and showed the resulting communications between Access and SQL Server when data is updated. When you start the Profiler, it will display the window When you create a new trace, a Run button appears at the lower-left of the window that you use to start the trace. The trace window can result in displaying a large amount of information, and you can stop and then restart the trace by using the Toolbar icons.

With the trace, you can then open various parts of your application and monitor performance; if you take a form bound to a large number of records and perform a search for data (Ctrl+F), you will see the large amount of activity that this causes in the Profiler window. Remember to discontinue the trace once you are finished.

The MSysConf Table

After opening your Access application, when Access starts to communicate with SQL Server-for example, if you click on a linked table-you see the following action in the trace window:

Access looks to see if it can find a table called MSysConf in your database. If you create a table with this name in your database, you can enter values to control how Access interacts with SQL Server. If you create this table, it's important to correctly set any values in the table. The following script (MSysConf.sql) creates this table and includes examples of setting values in the table:

CREATE TABLE MSysConf(
Config   SMALLINT NOT 	NULL,
chValue  VARCHAR(255) 	NULL,
nValue   INTEGER 	NULL,
Comments VARCHAR(255) 	NULL
)
GO
INSERT INTO MSysConf(Config,nValue,Comments)
VALUES(101,0,
'Prevent storage of the logon ID and password in linked tables.')
GO
--VALUES(103, 50,
--'number of rows retrieved.')
--VALUES(101,1,
--'Allow storage of the logon ID and password in linked tables.')
--VALUES(102,1,
--'delay in seconds between each retrieval.')
--Note Setting a higher delay time decreases network traffic,
-- but increases the amount of time that read-locks are left on data
-- (if the server uses read-locks).

Having set the previous option to prevent saving passwords in linked tables, when connecting to SQL Server the check box option to allow saving user names and passwords is no longer displayed (remember to close and then re-open your database if testing this).

It is also worth pointing out that when we have tested option 103 (which controls the number of rows that should be retrieved), we have not been able to see any easily observable changes when monitoring traffic by using the Profiler.