Getting an ODBC Trace Log from a Linked Server
ODBC log files are helpful if you are 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".
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 (e.g. MSSQL$SQLEXPRESS) and enable Local Access and Remote Access for this user.
