October 29, 2015 at 6:34 pm
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.
October 29, 2015 at 6:45 pm
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.
October 29, 2015 at 7:08 pm
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.
October 29, 2015 at 9:15 pm
It is network account(domain account), it has permission to run backup etc.
October 30, 2015 at 4:42 am
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" 😉
November 1, 2015 at 8:41 pm
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
November 1, 2015 at 9:26 pm
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply