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(
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