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

Question

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

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

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

i1888 SSCommitted Points: 1970 More actions · Answer 2

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: 1970 More actions · Answer 3

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

Perry Whittle SSC Guru Points: 234065 More actions · Answer 4

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: 1970 More actions · Answer 5

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: 9124 More actions · Answer 6

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