Getting an ODBC trace log from a linked server

ODBC log files are helpful if you're having a problem with a Linked Server that uses an ODBC data source. If you enable ODBC tracing in ODBC Data Source Administrator, but don't get a log file when working with your Linked Server, here are some things to try:

• Have you enabled ODBC tracing in the correct version of ODBC Data Source Administrator? If you are using a 64-bit version of SQL Server, you need to enable tracing in the 64-bit version of ODBC Data Source Administrator. Similarly, if you are using a 32-bit version of SQL Server, you need to enable tracing in the 32-bit version of ODBC Data Source Administrator.
• If your SQL Server instance was running when you enabled ODBC tracing, have you restarted it? You need to restart SQL Server before tracing will come into effect for this application.
• Check multiple locations for your ODBC trace file. Because multiple components of SQL Server interact with the ODBC layer, multiple trace files get generated. You should have a trace file located in the directory you specified in ODBC Data Source Administrator. For example, C:\Windows\Temp\SQL.log. To find other trace files, in Windows explorer, search for SQL.log (or whatever you named the file). We also had the file C:\Users\MSSQL$SQLEXPRESS\AppData\Local\Temp\SQL.log and C:\SQL.log.

  You need all the trace files because their contents are different. In our files, C:\Windows\Temp\SQL.log logged the initial connection, C:\Windows\Temp\SQL.log logged the SQL Query we executed.

• Have you enabled DCOM Access Permissions for MSDAINITIALIZE? To do this, run DCOMCNFG. In Component Services, right-click Component Services > Computers > My Computer > DCOM Config > MSDAINITIALIZE and choose Properties. Add the user account that SQL Server runs under (for example, MSSQL$SQLEXPRESS) and enable Local Access and Remote Access for this user.