get return value from xp_getfiledetails

Morpheus

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

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

sql 2000

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