• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 988
  • Last Modified:

file existing

i have the following code

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

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


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

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

but no matter the file is there or not the return value is '0' please help.. many thanx in advance

Me, yee
      
0
mingfattt
Asked:
mingfattt
  • 6
  • 4
  • 4
1 Solution
 
mingfatttAuthor Commented:
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
0
 
paeloCommented:
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.
0
 
mingfatttAuthor Commented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Thandava VallepalliCommented:
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
0
 
Thandava VallepalliCommented:
plz ignore my previous comments....  those are not working

have a lookt into this

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21131991.html
0
 
mingfatttAuthor Commented:
well the problem now is i want to detect the folder but not the file.... i m sorry for my first question
0
 
Thandava VallepalliCommented:
try this

EXECUTE @OLEResult = sp_OAMethod @FS, 'FileExists', @Exists OUT, '\\agmpimo173\Brio Schedule\'+@FolderName  
0
 
mingfatttAuthor Commented:
the error message...

 Incorrect syntax near '+'.

btw can you show me the code!!! i m really confusing... thanx
0
 
Thandava VallepalliCommented:
can you paste the latest code here...  so i will check it
0
 
paeloCommented:
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.
0
 
mingfatttAuthor Commented:
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
0
 
paeloCommented:
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.
0
 
mingfatttAuthor Commented:
welll its work.. but the thing is i get @OLEResult as 0 which in regards to my folder existing... anyone please tell..
0
 
paeloCommented:
@Exists is the variable which will tell you whether the folder exists or not.  1=exists, 0=not.

-Paul.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now