asked on

xp_getfiledetails using concatenate columns from database table

I'm going to start with I understand this is NOT the way I really want to be doing this...the database is SQL 2k (no upgrade on the horizon), the dba will not grant permissions for running xp_cmdshell, and I have a database that needs to do a check against the file based system. What I have to do is compare the last modified date recorded in the database and compare to the last modified date in the file based system. The paths and filenames are stored in the database, but the two columns must be concatenated to create the full path of the file/folder I'm checking the modified date.

I'm currently getting "xp_getfiledetails() returned error 3, 'The system cannot find the specified path.'"

I have actually run the xp_getfiledetails with the full path written out and it returns what I'm looking for exactly. Ideally I want to store this in a temp table and then run a query matching the temp table to the actual table and comparing modified dates.

Can xp_getfiledetails return more than a single row at a time? xp_fileexists is setup the same way, but I can return multiple rows for that extended stored procedure.

Two sections of code the part that doesn't work:
EXEC master..xp_getfiledetails '''''dbo.tablename.column'''' + ''\'' + ''''dbo.tablename.column'''''
 
The part that works fine:
 
create table ##filedetails(
alternatename varchar(50),
size varchar(20),
creation_date varchar(20),
creation_time varchar(20),
last_written_date varchar(20),
last_written_time varchar(20),
last_accessed_date varchar(20),
last_accessed_time varchar(20),
attributes varchar(20)
)
 
Insert ##filedetails EXEC master..xp_getfiledetails
 
---the temp table is created but it's empty.  when running just the master..xp_getfiledetails the error occurs.
 
Any insight would be appreciated

Open in new window

MohammedU

Why are you passing the table name and column name to xp_getfiledetails procedure? It expects the file name with the path not the table name... in your script you are not passing the file name to the procedure so your table is empty...

If you have the files names in the table then post the table info to get the script...

Ex: EXEC master..xp_getfiledetails 'c:\boot.ini'

create table ##filedetails(
alternatename varchar(50),
size varchar(20),
creation_date varchar(20),
creation_time varchar(20),
last_written_date varchar(20),
last_written_time varchar(20),
last_accessed_date varchar(20),
last_accessed_time varchar(20),
attributes varchar(20)
)

Insert ##filedetails EXEC master..xp_getfiledetails 'c:\boot.ini'

kvolarich

ASKER

The table is within SQL 2K and has many other fields within it -- so I was trying to pick out the two fields when concatenated equal the full path name of the file/folders I need information on. So, perhaps I should export out those two fields in a concatenated format to a .ini or .txt file and then run the xp_getfiledetails against that?

Will it return all the files listed in the new file?

reb73

"Can xp_getfiledetails return more than a single row at a time?"

No, it will always return details for the the first file that matches the path\filename pattern.

You will have to use a cursor to loop through the table columns to process one file at a time to get the details..

Here's an example where I've used xp_cmdshell to preload file details in a temp table and then loop through it to obtain details of all files..

substitute your table where I've used #allfiles and you should get the details yo are looking for..

/*
create table #filedetails(
rowid int identity,
path varchar(100),
filename varchar(100),
alternatename varchar(50),
size varchar(20),
creation_date varchar(20),
creation_time varchar(20),
last_written_date varchar(20),
last_written_time varchar(20),
last_accessed_date varchar(20),
last_accessed_time varchar(20),
attributes varchar(20)
)
 
create table #allfiles (filedetails varchar(1000))
insert #allfiles
exec master..xp_cmdshell 'dir c:\'
*/
truncate table #filedetails
Declare @path varchar(100), @filename varchar(100), @pathfile varchar(200)
declare fileloopcurs cursor
	for	select Path = 'C:\', FileName = rtrim(substring(filedetails,37,100))
		from #allfiles
		where isnumeric(left(filedetails,2)) = 1
		and patindex('%<DIR>%',filedetails) = 0
	for read only
open fileloopcurs
fetch next from fileloopcurs into @path, @filename
while	(@@fetch_status = 0)
begin
	set @pathfile = @path + @filename
	insert #filedetails (alternatename,size,creation_date,creation_time,last_written_date,last_written_time,last_accessed_date,last_accessed_time,attributes)
	exec master..xp_getfiledetails @pathfile
 
	update #filedetails set path = @path, filename = @filename where rowid = (select max(rowid) from #filedetails)
	fetch next from fileloopcurs into @path, @filename
end
close fileloopcurs
deallocate fileloopcurs
select * from #filedetails
/*
drop table #allfiles
drop table #filedetails
*/

Open in new window

kvolarich

ASKER

So should I use the existing table I have that has the information or should I export this out to another table or separate file?

I know you said to replace your #allfiles with my table, but in my first post I stated that the dba will not grant permissions to use xp_cmdshell. In your solution above you use this. I cannot.

Additionally, your code is hard-wired with the C:\ drive, my file/folder locations are on a network (yes, sql has access to this area).

The table has two columns -- one is called PATH_SERVERNAME which would contain something like '\\server\location\location\location

The second column is the name of the file LAYER_NAME such as filename (no extension) and since the ending '\' is missing prior to the filename I concatenate the two with an additional backslash in the middle

so \\server\location\location\location + '\' + filename

ASKER CERTIFIED SOLUTION

reb73

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kvolarich

ASKER

EXCELLENT -- worked great! Thanks!!