SQL Server Performance

Existing Folder

Discussion in 'T-SQL Performance Tuning for Developers' started by luma, Jan 3, 2006.

  1. luma New Member

    First , best wishes in this year.
    Help me please .. How can I find out if some folder on pc is exist ? Thanks , Lubo
    luma, Jan 3, 2006
    #2

  2. Madhivanan Moderator

    Run this

    EXEC Master..xpcmdShell 'Dir folder_path'

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Jan 3, 2006
    #2

  3. dineshasanka Moderator

    Check this SP
    create procedure sp_FolderFileExist
    @File varchar(1000) , --The folder or file name.
    @Switch bit --0 = Folder; 1 = File
    as
    --Variables
    declare @rtn int
    select @rtn = 0 --Executed with no errors.
    --Create a temp table.
    create table #FolderFileExists
    (
    [File Exists] int
    , [File is a Directory] int
    , [Parent Directory Exists] int
    )
    insert #FolderFileExists

    exec master..xp_FileExist @File

    if not exists
    ( select * from #FolderFileExists
    where
    case @Switch when 0 then [File is a Directory]
    when 1 then [File Exists]
    end = 1
    )
    begin
    select @rtn = -1
    end
    Quit:
    return @rtn

    GO



    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
    dineshasanka, Jan 3, 2006
    #2

  4. luma New Member

    Thnaks for answer .
    Dineshasanka - when i set

    set @File ='d: mp' OR 'd: mpX'
    set @Switch =0

    Drive d: ... is existing
    Directory 'd: mp' ... is existing , 'd: mpNOT' ... not exist

    exec sp_FolderFileExist 'D:TmpNOT' , 0
    exec sp_FolderFileExist 'D:Tmp' , 0


    Procedure return always values
    FileExist=0
    File is a Directory=0
    Parent Directory Exists=1

    luma, Jan 3, 2006
    #2

  5. Madhivanan Moderator

    Did you try this?

    EXEC Master..xpcmdShell 'Dir 'D:Tmp'


    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Jan 3, 2006
    #2

  6. luma New Member

    Yes , but result is same.
    luma, Jan 3, 2006
    #2

  7. dineshasanka Moderator

    dineshasanka, Jan 3, 2006
    #2

  8. luma New Member

    I want to subscribe here more about it . But i get back error always when i press SUBMIT REPLY.
    luma, Jan 3, 2006
    #2

  9. luma New Member

    Part I
    declare @cesta as varchar(255)
    set @cesta='dir D:Tmp'
    create table testDir (output varchar(255))
    insert testDir
    luma, Jan 3, 2006
    #2

  10. luma New Member

    Part II
    EXEC Master..xp_____cmdShell @cesta
    luma, Jan 3, 2006
    #2

  11. luma New Member

    REsult is
    1 The system cannot find the file specified.
    2 NULL

    But D:Tmp is exist . BTW - name of xp__procedure what i call is xp__cmdShell ( in statement is _ only 1)

    Lubo
    luma, Jan 3, 2006
    #2

  12. Luis Martin Moderator

    Did you try with other folder?
    Because what you write works.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



    Luis Martin, Jan 3, 2006
    #2

  13. Madhivanan Moderator

    Is it in the SQL Server system, or somewhere else?

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Jan 3, 2006
    #2

  14. luma New Member

    Luis , When I replace name of folder (now is name foto)

    (sample )
    declare @cesta as varchar(255)
    set @cesta='dir D:foto'
    drop table testDir
    create table testDir (output varchar(255))
    insert testDir
    EXEC Master..xp__cmdShell @cesta

    Result is
    1 The system cannot find the file specified.
    2 NULL

    You wrote that my code is good. Maybe I am wrong . Maybe I am waiting something but this code can't return it. What value (rows )is returned on your PC ?
    luma, Jan 3, 2006
    #2

  15. Madhivanan Moderator

    I repeat

    Is it in the SQL Server system, or somewhere else?



    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Jan 3, 2006
    #2

  16. luma New Member

    I am working with MS Sql server 2000. In stored procedure I need to find out if some specific folder exist on drive .
    luma, Jan 3, 2006
    #2

  17. Adriaan New Member

    quote:Originally posted by luma

    Luis , When I replace name of folder (now is name foto)

    (sample )
    declare @cesta as varchar(255)
    set @cesta='dir D:foto'
    drop table testDir
    create table testDir (output varchar(255))
    insert testDir
    EXEC Master..xp__cmdShell @cesta

    Result is
    1 The system cannot find the file specified.
    2 NULL

    You wrote that my code is good. Maybe I am wrong . Maybe I am waiting something but this code can't return it. What value (rows )is returned on your PC ?

    The account under which SQL Server is running, and/or the account under which the T-SQL code is executed, must have permission to browse the path.

    Also, does the D: drive exist on the SQL Server machine? And if it is a mapped network drive, then is it mapped to the same location that you think it is?
    Adriaan, Jan 3, 2006
    #2

  18. luma New Member

    So , my SQL run on server not on PC. And drive D is drivo on local pc. Now I understood - it's a problem .
    luma, Jan 3, 2006
    #2

  19. dineshasanka Moderator

    quote:Originally posted by luma

    So , my SQL run on server not on PC. And drive D is drivo on local pc. Now I understood - it's a problem .

    ah! yes those codes gives information about the server machine not the local pc

    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
    dineshasanka, Jan 4, 2006
    #2

  20. luma New Member

    Now it's ok, thanks, lubo
    luma, Jan 17, 2006
    #2

Share This Page