Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.
Hi all, please see this major site announcement: https://www.boards.ie/discussion/2058427594/boards-ie-2026

get return value from xp_getfiledetails

  • 31-10-2008 11:35AM
    #1
    Moderators, Society & Culture Moderators Posts: 2,687 Mod ✭✭✭✭


    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.


Comments

  • Registered Users, Registered Users 2 Posts: 610 ✭✭✭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
    


  • Moderators, Society & Culture Moderators Posts: 2,687 Mod ✭✭✭✭Morpheus


    sql 2000

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


Advertisement
Advertisement