- Sql xpdirtree file details full#
- Sql xpdirtree file details code#
- Sql xpdirtree file details windows#
SELECT = Get all the file names for the directory (includes directory names as IsFile = 0)
Sql xpdirtree file details full#
Make sure the full path name provided ends with a backslash
Sql xpdirtree file details windows#
Type VARCHAR(100) -Long Windows file type (eg.'Text Document',etc) ReadOnlyBit AS CASE WHEN Attributes& 1=1 THEN 1 ELSE 0 END, ShortPath VARCHAR(100), -8.3 full path including file nameĭateCreated DATETIME, -Date file was createdĭateLastAccessed DATETIME, -Date file was last readĭateLastModified DATETIME, -Date file was last written toĪttributes INT, -Read only, Compressed, ArchivedĪrchiveBit AS CASE WHEN Attributes& 32=32 THEN 1 ELSE 0 END,ĬompressedBit AS CASE WHEN Attributes&2048=2048 THEN 1 ELSE 0 END, Path VARCHAR(128), -Full path including file name Name VARCHAR(128), -File Name and Extension RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, IF OBJECT_ID('TempDB.#FileDetails','U') IS NOT NULL = Create a place to store the file details so we can return all the file details IF OBJECT_ID('TempDB.#DirTree','U') IS NOT NULL = Create a place to store all file names derived from xp_DirTree Made names match so they're less confusingĭECLARE INT -Read only, Hidden, Archived, etc, as a bit mapĭECLARE DATETIME -Date file was createdĭECLARE DATETIME -Date file was last read (accessed)ĭECLARE DATETIME -Date file was last written toĭECLARE VARCHAR(128) -File Name and ExtensionĭECLARE VARCHAR(128) -Full path including file nameĭECLARE VARCHAR(100) -8.3 full path including file nameĭECLARE VARCHAR(100) -Long Windows file type (eg.'Text Document',etc)
= These variable names match the sp_OAGetProperty options = These are processing control and reporting variablesĭECLARE VARCHAR(256) -Name of file currently being workedĭECLARE INT -Remembers number of rows for xp_DirTreeĭECLARE BIT -1 if Name is a file, 0 if not
= Suppress the auto-display of rowcounts so as not to interfere with the returned Initial concept borrowed from (Phil Factor) and Result set is returned (much like a function operates).ĮXEC dbo.GetDirDetails 'drive:path or UNC' Either a valid result set is returned or an empty If the trailing backslash on the path is missing, it will be added automatically.ģ. If the directory does not exist or there are no files in the directory, an emptyĢ. Which is no longer available in SQL Server 2005 except this gets all the file detailsįor all the files in the directory provided.ġ.
Sql xpdirtree file details code#
Replacement code for the sp_GetFileDetails proc that was available in SQL Server 2000 Maybe the saving grace is, it returns a single result set. 😉 It doesn't use xp_CmdShell, but it does (I'm a bit embarrassed but couldn't be helped with sp_OA* in SQL Server 2000, that I know of) use a WHILE loop and the sp_OA* procs. The easiest way is to have the SQL Server SERVICE login as a user with full domain privs. In order for the code to work and see a file on your box instead of the server, you must use a full UNC path and the necessary privs must be granted so the server actually has the rights to see the file. not the box you're running the code from. If it's not on the server, it's not going to see it because "C:\" refers to the "C" drive on the server. I guess my question would be, which machine is your C:\setup.txt file on.
If I run it for a file that doesn't exist or that the server can't "see", then I just get the 0 for successfully creating the FSO object. only thing I changed was the file name to match a file I had.
0 kommentar(er)
