[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

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

0
kvolarich
Asked:
kvolarich
  • 3
  • 2
1 Solution
 
MohammedUCommented:
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'
0
 
kvolarichAuthor Commented:
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?
0
 
reb73Commented:
"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

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
kvolarichAuthor Commented:
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
0
 
reb73Commented:
I just use xp_cmdshell to yield a list of files to demonstrate an example, the code in your case will be as follows -

(Update the <yourtable> tag in code below with the actual table containing PATH_SERVERNAME and LAYER_NAME)



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)
)
 
Declare @path varchar(100), @filename varchar(100), @pathfile varchar(200)
declare fileloopcurs cursor
        for     select PATH_SERVERNAME, LAYER_NAME
                from <yourtable>
        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 #filedetails
GO

Open in new window

0
 
kvolarichAuthor Commented:
EXCELLENT -- worked great!  Thanks!!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now