get return value from xp_getfiledetails

Morpheus · 31-10-2008 11:35AM #1

Hi all,

Simply how do I get the return value from xp_getfiledetails

I thought it'd be something simple like this.....

declare @exists int
SELECT @exists = EXEC master.dbo.xp_getfiledetails '\\computername\share\file.hhh'
SELECT @exists

I need to capture the return value, i.e. if its 2 its a file does not exist error.

nialo · 31-10-2008 11:39AM

Version of sql are you running? cause it nolonger exists in sql 2005

plus from another source this is how you would use the procedure. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39176

-- Something like this should get you close

DECLARE @filedate datetime

Create table ##filedetails(
   alternatename char(20),
   size char(20),
   creation_date char(20),
   creation_time char(20),
   last_written_date char(20),
   last_written_time char(20),
   last_accessed_date char(20),
   last_accessed_time char(20),
   attributes char(20)
)

--Get file info
INSERT ##filedetails EXEC master.dbo.xp_getfiledetails '\\server\path\file.xml'

--If file is not found return
IF @@ROWCOUNT = 0
	RETURN

--Last update time of file
SELECT @filedate = convert(datetime, rtrim(last_written_date) + ' ' + substring(last_written_time,1,2) + 
	   ':' + substring(last_written_time,3,2)  + ':' + substring(last_written_time,5,2))
FROM ##filedetails
 
--Load file if date is latter than date in DesignFileDate in MyHistoryTable. Add WHERE clause as needed
IF (
	SELECT DateDiff(ss,@filedate,DesignFileDate ) 
	FROM MyHistoryTable
    ) > 0
BEGIN
-- Execute proc to load updated XML file
	EXEC sp_load_file_proc

-- Store filetime in History Table
	UPDATE MyHistoryTable
	SET DesignFileDate = @filedate
END

Morpheus · 31-10-2008 11:42AM

sql 2000

not concerned with the actual sp itself, just cant capture the return value properly.

get return value from xp_getfiledetails

Comments