Sql-server – how to use fn_xe_file_target_read_file to read files over network

extended-eventssql serversql server 2014

We are running Microsoft SQL Server 2014, and SQL Server Analysis Services 2014.

I am trying to setup a system to provide us with analytics concerning our SSAS OLAP cubes adoption and usage. I have setup a an Extended Event trace with the events that I need to track and it is creating a bunch of .xel files on the server like it should.

The issue I am having now is that we do not have a SQL instance on that server, so I need to load the files into a database on a different server. I can load the files if I copy them over to the SQL server and call the sys.fn_xe_file_target_read_file function with a local path, but I get an error when I try to read the file with a UNC path.

The log file name "\\SERVERNAME\f$\ExtendedEvents\MyTrace_0_12345.xel" is invalid. Verify that the file exists and that the SQL Server service account has access to it.

I have configured folder security on the SSAS server to allow the domain account that our SQL server is running under to have access to the folder. So that shouldn't be a problem. I am making the assumption that this function does not like UNC paths, so my next step would be to map a network drive on the SQL server machine so I can pass a local path to this function.

Before I request this from our DBAs I wanted to reach out to see if my assumption about the UNC paths is correct, and if any one else has worked around a similar issue by mapping a network drive to the remote share. I have read through the MSDN documentation for the function, but I cannot see anything to indicate how I would read a file on a network share.

Best Answer

As Kin suggested in his comment, the F$ share is problematic since that only allows administrator access. Although you've provided file-system access to the folders in question, the account does not have access to the share. Create a share specifically for the xel files, and provide access to the SQL Server Service account on both the Share, and the folder.

Using the Universal Naming Convention name, or UNC, is preferable to accessing the file via a mapped-network-drive. A mapped network drive just obfuscates the true location of the file, and you'll need to somehow create the mapping inside the profile for the SQL Server Service account (that's what Kin is suggesting with the net use command he shows in his comment.)

My SQL Server 2012 instance reads files across the network perfectly like this:

SELECT *
FROM sys.fn_xe_file_target_read_file(
    '\\machine\share\ErrorLogs\system_health_*.xel' --this is the UNC
    , null
    , null
    , null
    );

Related Solutions

Sql-server – Error running Powershell Script in SQL Server Agent

I believe your issue is going to be the SQLPS provider. Since PowerShell steps in SQL Server Agent automatically put you into the context of that provider some commands that work in your normal console will not function the same way. A write up was done here with Set-Location. You basically have to tell SQLPS the provider you want to use.

Your code would look something like below:

set-location -Path Microsoft.PowerShell.Core\FileSystem::"\\server\companydocuments\MyApp\Application Files\"

You could also wrap the remainder into two lines if you wanted to:


cd (Get-ChildItem | Sort-Object name -Descending | Select name -First 1).name
& ".\Application.exe"

Sql-server – Database cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery

This isn't an access issue. The problem is that you are attaching the database to a higher version of SQL Server than the instance the .mdf was originally attached to and it's read only. SQL Server is trying to upgrade the database as part of the attach and can not because the db is in read-only mode.

If your database is in READ_ONLY (which sounds like it is the case based on your error messages), then you need all files (both log and data) to attach it per the Technet documentation. If you look under the FOR ATTACH clause, it explicitly describes that:

...for a read-only database, the log cannot be rebuilt because the primary file cannot be updated. Therefore, when you attach a read-only database whose log is unavailable, you must provide the log files or files in the FOR ATTACH clause.

You need the accompanying .ldf for the database. I would contact whoever provided the database and ask them to provide both the .mdf and .ldf files. Then, you can attach it with the following syntax:

CREATE DATABASE [foo]
ON (FILENAME='<<path to mdf>>')
LOG ON (FILENAME='<<path to ldf>>')
FOR ATTACH;

The READ_ONLY status cannot be removed from the database without attaching it because it is stored in the metadata of the database. To alter it, the database needs to be online and attached.

Another alternative is to ask for a backup of the database. You can work around some of these issues more easily if you're doing a database restore instead of an attach.

Best Answer

Related Solutions

Sql-server – Error running Powershell Script in SQL Server Agent

Sql-server – Database cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery

Related Question