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:
SELECT Config, nValue FROM MSysConf
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.
In this tutorial:
- Upsizing Access to SQL Server
- Planning for Upsizing
- Boolean Data
- IMAGE, VARBINARY(Max), and OLE Data
- Cycles and Multiple Cascade Paths
- Replicated Databases and Random Autonumbers
- Unique Index and Ignore Nulls
- Timestamps and Row Versioning
- Schemas and Synonyms
- Upsizing Wizard and the SQL Server Migration Assistant
- Upsizing to Use an Access Data Project
- Comparing Table Conversion in the Upsizing Wizard and SSMA
- Comparing Query Conversion in the Upsizing Wizard and SSMA
- Developing with Access and SQL Server
- Stored Procedures and Temporary Tables
- Handling Complex Queries
- Performance and Execution Plans
- SQL Server Profiler