Trying to check to see if a folder exists

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trying to check to see if a folder exists

Author

Topic

morphias0
Starting Member

20 Posts

Posted - 2006-02-08 : 09:59:00

I started writing a script to back up all databases on a server. I need to write them to a network drive in an appropriatly named folder depending on the database. The problem with that is that I would have to go through and create all the folders for all the databases for all the servers. How can I check to see if a folder exist and if not create it. I checked out tkizer's website that he recommended to me but I'm not really grasping the code for checking. This is what I have, but as you can tell its pretty much his code...

@cmd VARCHAR(2000),
@result INT,
@Path VARCHAR(100)

--I have @path defined and thats working.
--Right now I have the folder created and it will write the backup
--just fine. As soon as I delete the folder it will error out.
--It says it cant find the path so this next part obviously isnt
--working

--checking if dir exist if not create it
SELECT @cmd = 'dir' + @Path
EXEC @result = master.dbo.xp_cmdshell @cmd, NO_OUTPUT
IF @result <> 0
BEGIN
SELECT @cmd = 'mkdir ' + @Path
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
END

morphias0
Starting Member

20 Posts

Posted - 2006-02-08 : 10:02:41

I did issue a print command to check to see if path is returning the correct string and it is its \\computername\mainbackupfolder\foldernamedafterdatabase

morphias0
Starting Member

20 Posts

Posted - 2006-02-08 : 10:29:48

I also printed @result and it is = to 1. So i guess my main problem is created the folder

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-08 : 11:23:05

You can use the undocumented stored procedure master.dbo.xp_fileexist to check if a directory exists.

This code sample shows how you can use it:


set nocount on
declare @file_string varchar(400)
select  @file_string = 'c:\'

create table #xp_fileexist_output (
[FILE_EXISTS]			int	not null,
[FILE_IS_DIRECTORY]		int	not null,
[PARENT_DIRECTORY_EXISTS]	int	not null)

insert into #xp_fileexist_output
exec master.dbo.xp_fileexist @file_string

if exists ( select * from #xp_fileexist_output where FILE_IS_DIRECTORY = 1 )
	begin
	print 'File is a directory = '+@file_string
	end
else
	begin
	print 'File is not a directory = '+@file_string
	end

select * from #xp_fileexist_output

go
drop table #xp_fileexist_output

Results:


File is a directory = c:
FILE_EXISTS FILE_IS_DIRECTORY PARENT_DIRECTORY_EXISTS 
----------- ----------------- ----------------------- 
0           1                 1

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources