Check if UNC path exists (It is folder, not file)

  • i1888

    SSCommitted

    Points: 1810

    usual way to check if file exists

    DECLARE @File_Exists INT

    EXEC Master.dbo.xp_fileexist '\\serverB\SQL_Backup\file.bak', @File_Exists OUT

    print @File_Exists

    1

    And if check folder, can use "nul", but it doesn't work for UNC path

    DECLARE @File_Exists INT

    EXEC Master.dbo.xp_fileexist '\\serverB\SQL_Backupul', @File_Exists OUT

    print @File_Exists

    0

    If use xp_subdirs like:

    EXEC master.dbo.xp_subdirs '\\serverB\SQL_Backups'

    If the folder doesn't exists,

    Msg 22006, Level 16, State 1, Line 3

    xp_subdirs could not access '\\ServerB\SQL_Backups\*.*': FindFirstFile() returned error 67, 'The network name cannot be found.'

    Any ideas how to check if UNC folder exists in Backup? in my code I want to check if the unc folder exists before doing backup, the unc path is retrieved from other table or backup history.

  • Ed Wagner

    SSC Guru

    Points: 286985

    I would start by taking a look at the service account, meaning the Windows account under which the SQL Server service is running. When you shell out to DOS, it runs as the Windows user under which the service is running. The exception to this is if you have a proxy account configured, but that's another discussion.

    If your service account is a local account (to the server) then you aren't going to have any network permissions.

    If it's a network account, then that AD account needs permission to the path you're trying to access.

  • i1888

    SSCommitted

    Points: 1810

    All SQL Servers runs under same service account.

    I can use xp_* commands to check if file exists, list sub folders, just xp_fileexist cannot work UNC folder, it works for file's UNC path perfectly.

    e.g.

    '\\serverB\SQL_Backup\file.bak' works.

    \\serverB\SQL_Backup' does not work.

  • i1888

    SSCommitted

    Points: 1810

    It is network account(domain account), it has permission to run backup etc.

  • Perry Whittle

    SSC Guru

    Points: 233860

    i1888 (10/29/2015)


    It is network account(domain account), it has permission to run backup etc.

    This does not guarantee that access permissions are set so check them on the remote server.

    xp_subdir will only check for a location and fail if it does not exist

    xp_create_subdir will create the specified location if it does not exist (as long as you have permission to)

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • i1888

    SSCommitted

    Points: 1810

    Actually just checked out http://www.sqlservercentral.com/Forums/Topic489200-145-1.aspx, figured out the answer.

    It is not related to the permission, xp_fileexist does has the information but it is not a output parameter, so below will give the answer.

    DECLARE @FolderExists BIT;

    DECLARE @Backup_Path NVARCHAR(1024) = '\\sydwp-ssql-1c\SQL_Backup\'

    DECLARE @Tbl_FolderResult table

    (

    File_Exists INT,

    File_is_a_Directory INT,

    Parent_Directory_Exists INT

    )

    INSERT INTO @Tbl_FolderResult

    EXEC Master.dbo.xp_fileexist @Backup_Path

    SELECT @FolderExists = File_is_a_Directory FROM @Tbl_FolderResult

    select @FolderExists

  • johnwalker10

    SSCrazy Eights

    Points: 9074

    You may use [File is a Directory] instead of [File Exist] to check if the UNC path is folder, not file.

    If object_ID(N'tempdb.dbo.#File_Results') is not NULL

    Drop table #File_Results;

    CREATE TABLE #File_Results (

    File_Exists int,

    File_is_a_Directory int,

    Parent_Directory_Exists int

    )

    DECLARE @FileName varchar(255)

    SET @FileName='\\serverB\SQL_Backup'

    INSERT INTO #File_Results

    EXEC Master.dbo.xp_fileexist @FileName

    SELECT File_is_a_Directory FROM #File_Results

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply