file existing

i realize that this is my mistake that i not suppose to use file exist in this case because this is a folder... any idea.. please help

me, yee

You can do this with the filesystem object:

DECLARE @FS int
DECLARE @OLEResult int
DECLARE @Exists int

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
      PRINT 'Error while instancing Scripting.FileSystemObject'
END

EXECUTE @OLEResult = sp_OAMethod @FS, 'FolderExists', @Exists OUT, '\\agmpimo173\Brio Schedule\'+ @FolderName +''

EXECUTE @OLEResult = sp_OADestroy @FS



-Paul.

EXECUTE @OLEResult = sp_OAMethod @FS, 'FolderExists', @Exists OUT, '\\agmpimo173\Brio Schedule\'+@FolderName  

this part doest really can run because something wrong with the + Please advise

try this

You can't access the network files using  master..xp_fileexist .  Create a map network drive for \agmpimo173.

let say

\agmpimo173  is mapped to G drive... then access the file with below format



SET @FolderName  = 'WW'+@WorkWeek+''''+@WorkYear+''

select @filename = 'G:\Brio Schedule\'+ @FolderName +''    
EXEC master..xp_fileexist @filename, @return OUT
print @return


itsvtk

plz ignore my previous comments....  those are not working

have a lookt into this

https://www.experts-exchange.com/questions/21131991/Deleting-files-through-tsql.html

well the problem now is i want to detect the folder but not the file.... i m sorry for my first question

try this

EXECUTE @OLEResult = sp_OAMethod @FS, 'FileExists', @Exists OUT, '\\agmpimo173\Brio Schedule\'+@FolderName  

the error message...

 Incorrect syntax near '+'.

btw can you show me the code!!! i m really confusing... thanx

can you paste the latest code here...  so i will check it

Yeah, Please paste all the code including where you declare and set @FolderName.  Otherwise, the code should be working - I tested it before I posted it.

here here is my code

declare @FolderName varchar(255)
declare @filename varchar(255)
declare @return  int
declare @WorkWeek  varchar(255)
declare @WorkYear  varchar(255)

DECLARE @FS int
DECLARE @OLEResult int
DECLARE @Exists int



set @WorkWeek = DatePart(week,getdate()-1)
set @WorkYear = DatePart(year,getdate()-1)

SET @FolderName  = 'WW'+ @WorkWeek +''''+ @WorkYear +''
print @FolderName



EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
     PRINT 'Error while instancing Scripting.FileSystemObject'
END

EXECUTE @OLEResult = sp_OAMethod @FS, 'FolderExists', @Exists OUT, '\\agmpimo173\Brio Schedule\'+ @FolderName +''

EXECUTE @OLEResult = sp_OADestroy @FS

sp_OAMethod doesnt like the concatenation taking place within the parameter.  Set the full path to @FolderName or use an intermediate variable, like this:

DECLARE @Fldr varchar(255)
SET @Fldr='\\agmpimo173\Brio Schedule\'+ @FolderName +''
EXECUTE @OLEResult = sp_OAMethod @FS, 'FolderExists', @Exists OUT, @Fldr


-Paul.

welll its work.. but the thing is i get @OLEResult as 0 which in regards to my folder existing... anyone please tell..