Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Poll For a File

  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Question Unanswered: Poll For a File

    Hi All,

    I am using ActiveX code in a DTS package (Package1) to search a directory for a file.
    When the file is there I want to start a DTS package (Package2) to load
    in this file.

    I will schedule Package1 to run at 10pm, however since the external file is being generated externally I cannot guarentee that the file will be there at 10pm. Therefore I want to Poll the folder and not start package2 until I have the file. I am using the If file exists method.

    How do I poll for a file, and if its not there wait for 5 mins and poll again until I find the file.

    Thanks in advance

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Have you tried FSO objects?

    Dim objFSO
    Set objFSO = CreateObject("Scripting.FileSystemObject")

  3. #3
    Join Date
    Feb 2004
    Posts
    7

    Question Poll for File

    I have tried the FSO and thats what I am using.

    My problem is that if the file is not there, I want to wait for a period of
    time ie 5 mins and then try again. And keep trying every five mins until I get the file.

    Some thing like this:


    ========================
    Function Main()

    dim result

    set objFSO = CreateObject("Scripting.FileSystemObject")

    result = 1

    DO WHILE result = 1
    If objFSO.FileExists("c:\test.txt") Then
    //Start other Package
    result = 99
    Else
    wait(5)
    //Go to start and check for file again
    End if

    LOOP


    End Function
    =========================

    So in essence the above code will LOOP until it finds the file.
    I just need something to replace the wait(5)

    Any Ideas

    Thanks in advance




    I have wait(5) but wait is not a valid function

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    EXEC [master].[dbo].[xp_fileexist] 'c:\new_auth.dat1'

    This might help
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Poll for File

    Originally posted by superquinn
    I have tried the FSO and thats what I am using.

    My problem is that if the file is not there, I want to wait for a period of
    time ie 5 mins and then try again. And keep trying every five mins until I get the file.

    Some thing like this:


    ========================
    Function Main()

    dim result

    set objFSO = CreateObject("Scripting.FileSystemObject")

    result = 1

    DO WHILE result = 1
    If objFSO.FileExists("c:\test.txt") Then
    //Start other Package
    result = 99
    Else
    wait(5)
    //Go to start and check for file again
    End if

    LOOP


    End Function
    =========================

    So in essence the above code will LOOP until it finds the file.
    I just need something to replace the wait(5)

    Any Ideas

    Thanks in advance




    I have wait(5) but wait is not a valid function
    Why do not create a new function (I did not check it, sorry) and call it:

    Function wait(theDate)
    Do Until DateDiff("n", Now, theDate)<5
    Loop
    End Function

  6. #6
    Join Date
    Feb 2004
    Posts
    7

    Question

    I dont want to write a loop function like you have suggested as this constant looping might be very hard on the CPU. I was hoping there was a clever way or else an in built wait/sleep function available to me in the ActiveX component of the DTS.

    Can Anyone help?

  7. #7
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    You could write a Agent Job that will run every 5 minutes from 10 to 12. Sounds like the easiest way.
    -bpd

  8. #8
    Join Date
    Feb 2004
    Posts
    7

    Red face

    Hi,

    Im not quiet sure I understand what you mean by an agent Job.

    I am only new to SQL Server and DTS.

    The polling for a file is causing a lot of trouble for me.
    Can someone please help

  9. #9
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    In enterprise Manager, expand Management, SQL Server Agent, then click on Jobs. From there you can add a new job, which can execute ActiveX scripts, T-SQL scripts, even DTS packages. You can add one or many schedules to kick off the job.

    I think it may be what you are looking for.

    You can get started by right-clicking on the DTS package you created, and selecting Schedule Package, which will set the DTS package up to run as a job. You can then adjust the schedule to your liking, add more steps, etc.
    -bpd

  10. #10
    Join Date
    Feb 2004
    Posts
    3

    WAITFOR DELAY

    Would the command WAITFOR DELAY be useful?

    Here is the help from MS SQL Server Books Online...
    WAITFOR
    Specifies a time, time interval, or event that triggers the execution of a statement block, stored procedure, or transaction.

    Syntax
    WAITFOR { DELAY 'time' | TIME 'time' }

    Arguments
    DELAY

    Instructs Microsoft® SQL Server™ to wait until the specified amount of time has passed, up to a maximum of 24 hours.

    'time'

    Is the amount of time to wait. time can be specified in one of the acceptable formats for datetime data, or it can be specified as a local variable. Dates cannot be specified; therefore, the date portion of the datetime value is not allowed.

    TIME

    Instructs SQL Server to wait until the specified time.

    Remarks
    After executing the WAITFOR statement, you cannot use your connection to SQL Server until the time or event that you specified occurs.

    To see the active and waiting processes, use sp_who.

    Examples
    A. Use WAITFOR TIME
    This example executes the stored procedure update_all_stats at 10:20 P.M.

    BEGIN
    WAITFOR TIME '22:20'
    EXECUTE update_all_stats
    END

    For more information about using this procedure to update all statistics for a database, see the examples in UPDATE STATISTICS.

    B. Use WAITFOR DELAY
    This example shows how a local variable can be used with the WAITFOR DELAY option. A stored procedure is created to wait for a variable amount of time and then returns information to the user as to the number of hours, minutes, and seconds that have elapsed.

    CREATE PROCEDURE time_delay @@DELAYLENGTH char(9)
    AS
    DECLARE @@RETURNINFO varchar(255)
    BEGIN
    WAITFOR DELAY @@DELAYLENGTH
    SELECT @@RETURNINFO = 'A total time of ' +
    SUBSTRING(@@DELAYLENGTH, 1, 3) +
    ' hours, ' +
    SUBSTRING(@@DELAYLENGTH, 5, 2) +
    ' minutes, and ' +
    SUBSTRING(@@DELAYLENGTH, 8, 2) +
    ' seconds, ' +
    'has elapsed! Your time is up.'
    PRINT @@RETURNINFO
    END
    GO
    -- This next statement executes the time_delay procedure.
    EXEC time_delay '000:00:10'
    GO

    Here is the result set:

    A total time of 000 hours, 00 minutes, and 10 seconds, has elapsed! Your time is up.

  11. #11
    Join Date
    Feb 2004
    Posts
    7

    Poll For File

    Hi,

    Thanks for your reply. I dont think I am explaining myself fully

    I know how to sechdule the Package.
    I know how to check if file exists

    What I need to know is how to halt processing in a activex script
    for a period of time.

    If the file is not there initially, I want the wait a while and the reloop to
    the start of the package and so on until the file is there

    ==========ACTIVEX SCRIPT========

    Set foundfile = 0

    Do While foundfile = 0

    if foundfile = 1 then
    set foundfile = 1 (This breaks loop)
    //Carry on processing
    Set Success
    else
    //Halt procesing for 5 mins and then continue
    //This is where I need HELP!!

    end if

    loop

  12. #12
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    I think we understand what you are asking, but are offering solutions that differ from the model you are chasing.

    For instance, when I mentioned a scheduled job, I meant that you could create a task that would check for the file's existence. If were not there, the job would schedule itself to run again in a few minutes, then exit. If it were there, proceed to the next step, which would kick off your DTS package.
    -bpd

  13. #13
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    The problem with having an ActiveX script wait, is that you are tying up a thread and processor resources just counting away clock cycles. The SQL Agent dooes that already, so you might as well take advantage of it.
    -bpd

  14. #14
    Join Date
    Feb 2004
    Posts
    7

    Thumbs up

    Originally posted by bpdWork
    I think we understand what you are asking, but are offering solutions that differ from the model you are chasing.

    For instance, when I mentioned a scheduled job, I meant that you could create a task that would check for the file's existence. If were not there, the job would schedule itself to run again in a few minutes, then exit. If it were there, proceed to the next step, which would kick off your DTS package.
    =====================================
    This sounds EXACTLY like what I want to do. Im sorry about this
    but Im new to SQL Server.

    How do you get a Job to reschedule itself in code?
    I assume this is done in ActiveX

  15. #15
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Off the top of my head, I would say that you would need to use an ActiveX script to access the SQLDMO object, and control it from there. I have done such a thing before. I will see what I can find in my old code heap.
    -bpd

Posting Permissions

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