Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Scenario

Debugging a failed transaction, I asked the DBA to run a trace on SQL Server. Eventually he send me a .trc file.

Issue

I use the fn_tracegettable() function to access the trace:

select * from sys.fn_trace_gettable('C:\path\to\myTraceFile.trc', default)

But I get the following error:

  Msg 567, Level 16, State 7, Line 1
  File 'C:\path\to\myTraceFile.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.

Facts

  • The SQL Management Studio I am executing the fn_tracegettable from runs in my local machine.
  • The .trc file is in my local machine.
  • Both the server and the local machine run a SQL Server 2008 R2.
share|improve this question

migrated from stackoverflow.com May 31 '13 at 13:16

This question came from our site for professional and enthusiast programmers.

    
I'm not sure if it makes a difference, but what is the version of the server instance where the trace was collected, and what is the version of your local instance? –  Jon Seigel May 31 '13 at 13:26
    
@Jon Seigel: fact added in my question. –  ADC May 31 '13 at 13:34

3 Answers 3

I had the same issue. I ran an SQL trace on a remote server and transferred the trace files to a local directory on my workstation so that I load the data into a table on my local SQL Server instance for running queries against.

SELECT * INTO ProfileTracesTable
FROM ::fn_trace_gettable('C:\Users\anthony\Documents\SQL_traces\first.trc', default)

However, each time I tried, I got the following error:

File 'C:\Users\anthony\Documents\SQL_traces\first.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.

At first I thought the error might be related permission but I ruled this out since I had no problem loading the .trc files directly into SQL Profiler or as a file into SSMS.

After trying a few other ideas, I thought about it a bit more and realised that it was due to permissions after all: the query was being run by the SQL Server process (sqlsrvr.exe) as the user NT AUTHORITY\NETWORK SERVICE – not my own Windows account.

The solution was to grant Read and Execute permissions to NETWORK SERVICE on the directory that the trace files were stored in and the trace files themselves.

You can do this by right-clicking on the directory, go to the Security tab, add NETWORK SERVICE as a user and then select Read & Execute for its Permissions (this should automatically also select Read and List folder contents). These file permissions (ACLs) should automatically propagate to the directory contents.

If you prefer to use the command line, you can grant the necessary permissions to the directory – and its contents – by running the following:

icacls C:\Users\anthony\Documents\SQL_traces /t /grant "Network Service:(RX)"
share|improve this answer
    
+1 since this is the correct answer. –  Max Vernon Jul 15 at 13:55

Why are you opening the file in the SQL Management Studio? Use the SQL Server Profiler tool instead to open .trc files.

share|improve this answer
    
Unfortunatly, SQL Server Profiler tool is not available in my SQL Management. –  ADC May 30 '13 at 12:52
    
You should install the Profiler tool then. In SQL Management Studio are you connecting to a local DB instance? –  bastos.sergio May 30 '13 at 12:57
    
Just FYI you can open trace files like that, the columns & rows show up in the results pane just like a normal query. –  Meff May 30 '13 at 13:37

You are running query on the database server. The path in the query is the path on the actual database server and not your local c drive.

Some of your options: 1. Have the DBA put the file on the server and give you the servers path. 2. Have a local instance of SQL Server and use the local path on your desktop. 3. Install SQL Profiler and open it there.

share|improve this answer
    
I edited my question: everything is running in my local machine. –  ADC May 30 '13 at 16:11
    
Where is the actual database server that Management Studio is connected to? To find out, run the statement: select @@SERVERNAME –  Chris H May 30 '13 at 17:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.