Professional Web Applications Themes

FileSystem - Microsoft SQL / MS SQL Server

OK here's the scenario, I would like to archive file system files that relate to completed claims in out database. An run a job to perform this task every night by simply moving the files into an archive directory that can be burn to cd once a week or so... I have created a linked server for the file system and am able to query the database to produce a listing of filesystem files to be archived... Beyond this point I have no idea where to go ! Any pointers would be appreciated... Thanks Henry...

  1. #1

    Default FileSystem

    OK here's the scenario, I would like to archive file system files that
    relate to completed claims in out database. An run a job to perform this
    task every night by simply moving the files into an archive directory that
    can be burn to cd once a week or so...

    I have created a linked server for the file system and am able to query the
    database to produce a listing of filesystem files to be archived...

    Beyond this point I have no idea where to go !

    Any pointers would be appreciated...

    Thanks

    Henry


    Henry Guest

  2. #2

    Default Re: FileSystem

    you could use 'xp_cmdshell' to shell out to DOS and perform whatever you
    need to archive your files. more info on this ext. proc can be found here.
    http://msdn.microsoft.com/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp?frame=true

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net



    "Henry" <com> wrote in message
    news:Oi$phx.gbl... 
    the 


    oj Guest

  3. #3

    Default Re: FileSystem

    Hi Henry,
    There are a couple of ways to do this, but perhaps using VBScript would be
    the simplest.
    Here is some code to help you along:

    You could save the code here in a plain text file and name it MoveFiles.vbs
    or whatever

    You can also use the scheduler to run this periodically - say at the end of
    the month, but you may have to use UNC for the path names

    Regards
    Habib


    '---------------------- Code Start ----------------------
    ' Destination location of the files
    DestFolderPath = "G:\Claims\Archives\"
    LogFileName = "C:\Temp\Archive" & format(Date, "YYMMDD") & ".log"

    ' The connection string to the SQL Server
    Const ConnStr = "Provider=SQLOLEDB.1;Password=567;UserID=XYZ;Initi al
    Catalog=SOMEDB;Data Source=SOMESERVER"
    Const AdOpenForwardOnly = 0 ' ADO constant
    Const AdUseClient = 3 'ADO Constant
    Const ForWriting = 2 ' FileSystemObject constant


    'Create a connection to the server using ADO
    set oConn = Createobject("adodb.Connection")
    set oRs = CreateObject("adodb.Recordset")
    oConn.ConnectionString = ConnStr
    oConn.CursorLocation = AdUseClient
    oconn.connectionTimeout = 120 ' in seconds

    'The SQL statement used to retrieve the list of files to be moved
    ssql = "Select Path, FileName from FileList where Archive = 1 "

    ' Create a connection to the FileSystemObject
    set fso = CreateObject("Scripting.FileSystemObject")
    set LogFile = fso.OpenTextFile(LogFileName, ForWriting, True) ' True
    indicates overwrite

    ' Add notes to the file
    LogFile.WriteLine "YCS Claims Service - File Transfer Log"
    LogFile.WriteLine "Started" & Now()
    LogFile.WriteLine "*********************"


    'first open the connection
    oconn.open

    ' Now open the recordset
    oRs.open ssql, oconn, adOpenForwardOnly

    ' if the recordset is not empty
    if not(oRs.BOF and oRs.EOF) then
    oRs.MoveFirst

    While not oRs.EOF
    ' Check if the file exists and Move it.
    if fso.FileExists
    fso.movefile(oRs("Path") & oRs("FileName"), DestFolderPath)
    LogFile.WriteLine "Moved " & oRs("Path") & oRs("FileName") & " to "
    & DestFolderPath
    Success = Success +1
    Else
    LogFile.WriteLine "File Not Found: " & oRs("Path") &
    oRs("FileName")
    Failure = Failure +1
    End if
    oRs.Movenext
    Wend
    fso.WriteLine " Total Files moved = " & Success
    if Failure > 0 then fso.WriteLine "Total Files Not Found = " & Failure

    Else
    LogFile.WriteLine "The recordset did not return any records. No action
    taken"
    End if

    LogFile.WriteLine "Finished" & Now()
    LogFile.WriteLine "*********************"

    '---------------------- Code End ----------------------



    "Henry" <com> wrote in message
    news:Oi$phx.gbl... 
    the 


    HSalim Guest

  4. #4

    Default FileSystem

    Hi Henry.

    Just to add my 20c worth, you might also consider doing
    this via a DTS package or via a COM object because access
    to xp_cmdshell is often restricted for security purposes.

    If you're writing a SQL Server based application that will
    be installed on various (eg customer) target servers, this
    might be a problem as various sites are likely to restrict
    access to xp_cmdshell.

    As ternative, you could consider either DTS or COM.
    From DTS, you can access the FileSystemObject (FSO) via an
    ActiveX script task or you could write a COM wrapper
    around FSO & call it via sp_OACreate..

    HTH

    Regards,
    Greg Linwood
    SQL Server MVP
     
    system files that 
    to perform this 
    archive directory that 
    able to query the 
    archived... 
    Greg Guest

  5. #5

    Default Re: FileSystem

    Hi Tony.

    Yes - & that's why I didn't suggest going direct from TSQL
    to FSO - I actually suggested a COM wrapper, such as
    you've developed.

    Regards,
    Greg Linwood
    SQL Server MVP
     
    stored procs as 
    and there is no 
    slower than writing a 
    into an Array and 
    that called the VB 
    ('\\unc\path\','*.*') 
    is best avoided [/ref]
    access [/ref]
    purposes. [/ref]
    will [/ref]
    this [/ref]
    restrict [/ref]
    an 
    >> system files that [/ref][/ref]
    job 
    >> archive directory that [/ref][/ref]
    am 
    >> archived... [/ref]
    >
    >
    >.
    >[/ref]
    Greg Guest

Similar Threads

  1. FileSystem Object
    By Boris in forum ASP Database
    Replies: 4
    Last Post: March 2nd, 05:58 AM
  2. Filesystem io
    By Peter in forum AIX
    Replies: 3
    Last Post: January 27th, 12:42 PM
  3. SCO Filesystem
    By c3q8 in forum SCO
    Replies: 5
    Last Post: August 8th, 09:58 AM
  4. filesystem help
    By Bela Lubkin in forum SCO
    Replies: 0
    Last Post: July 29th, 08:41 AM
  5. Replies: 4
    Last Post: July 3rd, 10:37 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139