checking for file existence within an udf

hi ng,
hi friends,
i've a problem within an udf to find out wheter a file or a path exists or
not. unc pathes and wildcards have to be supported.
i've already checked the following solutions:
- exec master..xp_fileexist @.filename, @.file_exists=@.exists output
=> wildcards are not supported
=> pathes can't be checked
- exec master..sp_MSget_file_existence @.filename,@.exists=@.exists output
=> no extended stored procedure but works perfect in tsql
=> stored procedures can't be used in udfs
- exec master..xp_cmdshell @.command
=> no reuseable output value
maybe someone can help me.
thanks
dengo
-- hope you'll understand my englishxp_cmdshell *does* return a usable output value. Try this:
declare @.i as integer
exec @.i = master..xp_cmdshell 'dir c:\config.* /b'
print @.i
If @.i = 0 then the command executed successfully and one or more matching
files were found. If @.i <> 0, then no file was found or there was an error,
as might be the case here:
declare @.i as integer
exec @.i = master..xp_cmdshell 'dir c:\config.zzzz* /b'
print @.i
"Robert Denkmayr" <RobertDenkmayr@.discussions.microsoft.com> wrote in
message news:0D9C2CCD-B335-479C-B10B-E944D34A74FF@.microsoft.com...
> hi ng,
> hi friends,
> i've a problem within an udf to find out wheter a file or a path exists or
> not. unc pathes and wildcards have to be supported.
> i've already checked the following solutions:
> - exec master..xp_fileexist @.filename, @.file_exists=@.exists output
> => wildcards are not supported
> => pathes can't be checked
> - exec master..sp_MSget_file_existence @.filename,@.exists=@.exists output
> => no extended stored procedure but works perfect in tsql
> => stored procedures can't be used in udfs
> - exec master..xp_cmdshell @.command
> => no reuseable output value
> maybe someone can help me.
> thanks
> dengo
> -- hope you'll understand my english|||hi michael,
thanks for your reply.
you're right but your reply is my answer - see returncode on errors.
additional a remark from <sp_MSget_file_existence>.
/*
** The return code from xp_cmdshell is not a reliable way to check whether
** the file exists or not. It is always 0 on Win95 as long as xp_cmdshell
succeeds.
*/
another weak spot is that you need an other command for checking the
existence of a path.
dengo
"Michael C#" wrote:

> xp_cmdshell *does* return a usable output value. Try this:
> declare @.i as integer
> exec @.i = master..xp_cmdshell 'dir c:\config.* /b'
> print @.i
> If @.i = 0 then the command executed successfully and one or more matching
> files were found. If @.i <> 0, then no file was found or there was an erro
r,
> as might be the case here:
> declare @.i as integer
> exec @.i = master..xp_cmdshell 'dir c:\config.zzzz* /b'
> print @.i
> "Robert Denkmayr" <RobertDenkmayr@.discussions.microsoft.com> wrote in
> message news:0D9C2CCD-B335-479C-B10B-E944D34A74FF@.microsoft.com...
>
>|||Ahhh you're using Win95 are ya? Wowsa.
I haven't done anything on Win95 with this, but if you have a Win95 box
handy, you might try it out and see if it counts a "No Files Found" error as
a success or not. I have used this method on Win2K, XP Pro and 2003 Server
with no issues.
You might check out xp_dirtree or xp_subdirs for paths. I'd think you'd
need some sort of custom solution if you want to return two separate codes -
one for the existence of a path, and one for the existence of a file.
"Robert Denkmayr" <RobertDenkmayr@.discussions.microsoft.com> wrote in
message news:5E0B835F-FCD7-4770-9C08-B84922D5F8C1@.microsoft.com...
> hi michael,
> thanks for your reply.
> you're right but your reply is my answer - see returncode on errors.
> additional a remark from <sp_MSget_file_existence>.
> /*
> ** The return code from xp_cmdshell is not a reliable way to check whether
> ** the file exists or not. It is always 0 on Win95 as long as xp_cmdshell
> succeeds.
> */
> another weak spot is that you need an other command for checking the
> existence of a path.
> dengo
>
> "Michael C#" wrote:
>|||no i'm not using win95.
for the first time the udf will be used by dts packages on w2k server where
dynamic filename assignment is necessary to avoid overwriting existing files
or using prior created paths. that's why i need a reliable solution.
at the moment i'll implement 'xp_cmdshell' with the appropriate command
either for a filename or a path.
because i'm not familiar with programming in c/c++ the most preferred
solution would be a dll for an extended stored procedure like the
'xpstar.dll' which contains the function for 'xp_fileexists'.
another step would be the sourcecode of 'xpstar.dll' so i could extract the
necessary code for a new dll with the new function.
"Michael C#" wrote:

> Ahhh you're using Win95 are ya? Wowsa.
> I haven't done anything on Win95 with this, but if you have a Win95 box
> handy, you might try it out and see if it counts a "No Files Found" error
as
> a success or not. I have used this method on Win2K, XP Pro and 2003 Serve
r
> with no issues.
> You might check out xp_dirtree or xp_subdirs for paths. I'd think you'd
> need some sort of custom solution if you want to return two separate codes
-
> one for the existence of a path, and one for the existence of a file.
>|||If you're interested in creating your own XP's, here's an article on the
subject: http://www.codeproject.com/database/extended_sp.asp.
You could also look at generating a filename and path using GetDate() and a
random #, and/or a counter value stored in a table which you could increment
each time a new file is created. Not sure of the specifics of your
application, but those are a couple ideas which might work in some
circumstances.
"Robert Denkmayr" <RobertDenkmayr@.discussions.microsoft.com> wrote in
message news:CCD5EF04-52E8-42BA-A9F3-472986C53A6A@.microsoft.com...
> no i'm not using win95.
> for the first time the udf will be used by dts packages on w2k server
> where
> dynamic filename assignment is necessary to avoid overwriting existing
> files
> or using prior created paths. that's why i need a reliable solution.
> at the moment i'll implement 'xp_cmdshell' with the appropriate command
> either for a filename or a path.
> because i'm not familiar with programming in c/c++ the most preferred
> solution would be a dll for an extended stored procedure like the
> 'xpstar.dll' which contains the function for 'xp_fileexists'.
> another step would be the sourcecode of 'xpstar.dll' so i could extract
> the
> necessary code for a new dll with the new function.
>
> "Michael C#" wrote:
>|||i had the same ideas as you can see in a sample of the first implementation
below:
'p2lgb_%y%m%d_%n.txt' ... data file
'p2lgb_%y%m%d_%n.err' ... error file
'\\dfs.dom\dfs\sem' ... interface path
'\\dfs.dom\dfs\archiv\sem\%y' ... archiv path
%y=year, %m=month, %d=day ... using getdate()
%n=unique number ... counter value stored in a table
the problem in this implementation is the unique number. i can't be sure
that the next
number isn't used manually by an user or it's already used in the archiv
directory => check for file existence.
at the new (second) implementation i should check both the interface and
archiv paths for the first unused number (starting at 1) => check for file
existence.
i'll examine the article about writing xp's tomorrow because i'm from
austria and it's already 11:15 pm.
thanks and good night
dengo
"Michael C#" wrote:

> If you're interested in creating your own XP's, here's an article on the
> subject: http://www.codeproject.com/database/extended_sp.asp.
> You could also look at generating a filename and path using GetDate() and
a
> random #, and/or a counter value stored in a table which you could increme
nt
> each time a new file is created. Not sure of the specifics of your
> application, but those are a couple ideas which might work in some
> circumstances.
>