Checking if directory exists in T-SQL

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 2005 Forums

Transact-SQL (2005)

Checking if directory exists in T-SQL

Author

Topic

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-18 : 10:16:29

I know there is a way to determine if a file exists using T-SQL, but I can't seem to find a way to determine if a directory exists. I need to be able to determine this so I can delete the directory if it already exists before I run other queries.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 10:18:58

http://www.sqlservercentral.com/scripts/Miscellaneous/30587/

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-18 : 10:35:16

I get errors when I try to create that function and it only creates it partially.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 10:40:35

quote:
Originally posted by im1dermike

I get errors when I try to create that function and it only creates it partially.

Did you execute the procedure as well? the function actually uses the SP to return the status.

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-18 : 10:49:19

I copied all the code in that windows and executed it in a query and got an error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 10:52:14

quote:
Originally posted by im1dermike

I copied all the code in that windows and executed it in a query and got an error.

Whats the error?

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-18 : 10:53:27

I'm not sure what the original error was, but the errors I get now when trying to recreate it are:

Msg 2714, Level 16, State 3, Procedure fn_FileExist, Line 28
There is already an object named 'fn_FileExist' in the database.
Msg 2714, Level 16, State 3, Procedure usp_FileExist, Line 34
There is already an object named 'usp_FileExist' in the database.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 11:07:38

You don't have to create the function more than once.

E 12°55'05.25"
N 56°04'39.16"

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-18 : 11:10:37

There was an error creating it the first time so it didn't create correctly the first time. Also, glancing at the code, it looks to me like it deletes the function if it already exists and then creates it again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 11:10:50

quote:
Originally posted by im1dermike

I'm not sure what the original error was, but the errors I get now when trying to recreate it are:
Msg 2714, Level 16, State 3, Procedure fn_FileExist, Line 28
There is already an object named 'fn_FileExist' in the database.
Msg 2714, Level 16, State 3, Procedure usp_FileExist, Line 34
There is already an object named 'usp_FileExist' in the database.

But it has a block which checks for existence of function and if yes it drops before creating. did you include it too?

IF EXISTS ( SELECT  1 FROM sysobjects WHERE name = 'fn_FileExist')
  DROP FUNCTION dbo.fn_FileExist
GO...

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-18 : 11:39:14

I included everything in the code window.

I just manually deleted the function and stored procedure and ran the query again. Here is the error:

Cannot add rows to sysdepends for the current object because it depends on the missing object 'dbo.usp_FileExist'. The object will still be created.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-06-18 : 11:42:24

This actually checks that the directory C:\ exists by checking for the existence of the NULL file in that directory.


declare @file_path	nvarchar(500)
declare @file_exists	int

set @file_path = 'C:\nul'

exec master.dbo.xp_fileexist 
	@file_path,
	@file_exists output 

print	'File '+isnull(@file_path,'NULL')+' '+
	case when @file_exists = 1
	then 'exists'
	else 'does not exist'
	end


Results:

File C:\nul exists

CODO ERGO SUM

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-18 : 11:49:16

MVJ: When I run the code you just entered with a directory that exists and also one that doesn't exist, it always says "File .... does not exist"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-06-18 : 11:58:59

The code works OK for me.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-18 : 11:59:52

quote:
Originally posted by im1dermike

MVJ: When I run the code you just entered with a directory that exists and also one that doesn't exist, it always says "File .... does not exist"

Note that it checks the existance of the file in Server's directory and not Client's directory

Madhivanan

Failing to plan is Planning to fail

im1dermike
Posting Yak Master

222 Posts

Posted - 2008-06-18 : 12:59:17

I tried a file on another server and also "C:\". Neither worked.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 14:29:19

Which version and edition of SQL Server are you using?

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-18 : 14:35:46

quote:
Originally posted by im1dermike

I included everything in the code window.

I just manually deleted the function and stored procedure and ran the query again. Here is the error:
Cannot add rows to sysdepends for the current object because it depends on the missing object 'dbo.usp_FileExist'. The object will still be created.

That isn't an error. It's a warning. Note the last sentence in the message.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources