Transact SQL :- sys.dm_os_enumerate_filesystem

Background

Will like to take a few minutes to discuss an un-documented Transact SQL function.

The function’s name is sys.dm_os_enumerate_filesystem.

Usage

Syntax

SQL


SELECT * 

FROM   sys.dm_os_enumerate_filesystem
(
        [basefolder]
      , [filePattern]

) tblOSEFS

Sample

Sample -01

SQL


SELECT * 

FROM   sys.dm_os_enumerate_filesystem
       (
          'c:\downloads\hibernate.org'
          ,'*'
       ) tblOSEFS

Output

output.fileSystem.hirbernate.org.01.20200423.1221PM

Issues

Issue – Parameter is incorrect

Error Message

Textual


Msg 2561, Level 16, State 101, Line 11
Parameter 1 is incorrect for this statement.

Image

parameter1IsIncorrect

Explanation

We passed a null or an empty string as the file pattern to look for.

Remediate

Please pass along a valid file pattern such as:-

  1. *
  2. *.bak
  3. *.exe

Issue – “Internal error. The string routine in file sql\ntdbms\storeng\dfs\alloc\storagedmv.cpp, line 799 failed with HRESULT 0x8007007a.”

Error Message

Textual


Msg 407, Level 16, State 1, Line 2
Internal error. The string routine in file sql\ntdbms\storeng\dfs\alloc\storagedmv.cpp, line 799 failed with HRESULT 0x8007007a.

Image

error.407.stringRoutineIsInvalid.01.20200423.1226PM

Explanation

The error read:-

Msg 407, Level 16, State 1, Line 2
Internal error. The string routine in file sql\ntdbms\storeng\dfs\alloc\storagedmv.cpp, line 799 failed with HRESULT 0x8007007a.

Interpretation

  1. File Module
    • sql\ntdbms\storeng\dfs\alloc\storagedmv.cpp
      • Line #:- 799
      • Error Number:- 0x8007007a

Remediate

None Known.

Version

Here are the SQL Server Version Numbers that we tested against:-

v2017

v2017 – CU15

Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) – 14.0.3192.2 (X64)
Jun 15 2019 00:45:05
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Enterprise 2016 LTSB 10.0 <X64> (Build 14393: )

 

Summary

sys.dm_os_enumerate_filesystem offers nice tooling for reading the Operating System File System.

It was introduced in v2017.

Dedication

Dedicated to bloggers and late hour coders.

Kept and Keep the day job; yet left and leaving something on their trail.

One thought on “Transact SQL :- sys.dm_os_enumerate_filesystem

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s