Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142

    Unanswered: Check file status with datetime?

    We have a really annoying job here that relies on a particular file to be created before several imports run. An old file may already exist, but if it isn't recent, we don't want the import to run. This job can't delete it, since other jobs use that file. What we'd like to do is to be able to check the creation date of the file, and if it is after a certain time of day, run the import, else, delete the file.

    I know of xp_fileexists. Is there anything similar in SQL that can return file information or am I stuck parsing the output from xp_cmdshell 'dir F:\ftpcore\inputready.txt'?

    Any help or hints are appreciated. Let me know if you need more info. Thanks.

    -D.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you are probably going to have to use filesystem objects in an ActiveX as your first job step.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    you could use xp_cmdshell to dir the folder and then parse the results so you have the datetime of the file to inspect.

    EDIT: Duh ... should have RTFM of the OP ... he's trying to get around that ... what a way to start the day!

    :-o

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Thrasymachus - I figured as much. If I can't convince the rest of the team to use another method (my first goal), I'll play with that.

    tomh53 - Don't sweat it. I started by trying to figure out what happened to xp_getfiledetails for 20 min until I remembered that this box was SQL2005.

    Thank you both for responding.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not any sort of an improvement, and barely even a variation on a theme, but CLR...

    Ultimately you are trying to interact with the file system - it is always going to be messy.

    If it is a tiddly file you could try BULK INSERTing and trap for errors</ scrapes bottom of barrell>
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Thanks for the reply, Pootle_Flump. The only problem with the BULK INSERT is that an old file may still exist. That would insert, with no errors, but wouldn't always satisfy the condition of the file being created in a certain window. The CLR proc is probably best bet at this point, for this purpose.

    Ultimately, I'd like to get rid of this file all together. It's basically a trigger for a particular task. If there = run task. If not = wait 30 seconds and check again. The process that creates the file is a job on an IBM UDB server.

    I suggested a linked server and a job status table. When the UDB job is finished, log it, and let the SQL job check the status table. I may as well have been speaking Aramaic.

    Le *sigh*...

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well yeah - now you say that I am defo with you. You don't even need a linked server - just a connection object to "ping" a sproc.



    ..... would you have preferred that in Aramaic?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Quote Originally Posted by pootle flump
    Well yeah - now you say that I am defo with you. You don't even need a linked server - just a connection object to "ping" a sproc.
    Sorry, you're over my head on that one. Can you explain or point me to a link on that?

    Quote Originally Posted by pootle flump
    ..... would you have preferred that in Aramaic?
    I really only speak English and 'Talian. It's like Italian, but nobody in Italy would unnastan you. The west side-a Cleveland OH would unnastan perfectly. And den shoot yous guys. Fuhgeddaboudit.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ReadySetStop
    Can you explain or point me to a link on that?
    Well pseudo VBish code:
    Code:
    Dim con AS NEW ADODB.Connection
    
    con.ConnectionString = "my connection string from www.connectionstring.com"
    
    con.Open
    
    con.Execute "Exec dbo.MyStoredProcedureThatDoesStuff"
    
    Set con = Nowt
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Gotcha. So it's time to learn me some VB or other such language. I had avoided that up till now, but it looks like CLR could be useful in this, and a few other situations I have.

    Thanks again for the help.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just to correct poots post above

    www.connectionstrings.com

    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    PSEUDO code
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by pootle flump
    PSEUDO code
    Is that lcode that is written by pseudo programmers. I have seen plenty of that and done a little myself. I need to learn assembly so I can write my own language called Pseudo. That could lead to so much fun in job interviews.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How many geek points do I get for knowing some assembley?
    George
    Home | Blog

  15. #15
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    You waould have gotten 10 geek points, but since you misspelled assembly you get a -25 net geek points.


    -- This is all just a Figment of my Imagination --

Posting Permissions

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