File exists check function method not finding file even though it is copied to folder

1

I have 2 stored procedures: first 1 is copying the file from one location to other folder path. and second sp, is trying to send an email with fileattachment, both stored procedure call is happening within same scope from outside application. for some reason even though the file copied and to path the second procedure is not finding it and giving file exixts =0 saying not found,

Stored procedure1: i am doing a file copy from one location to other folder location

set @cmdstring = 'copy '+ @inBoundFileLoc +' ' + @fileInputpath
exec master..xp_cmdshell @cmdstring

Set @cmdstring =''

Right after the above sp call, the second sp call is happening which does filecheck, if file exists or not.

it is always saying file is not existing: even though file is copied to the right location.

right after Sp 1. file copy do i need to release anything? something seems like locked.

seems like the other process still locking and holding the file what is the best way to have the next sp call within same scope to have access to the file which is copied by SP1.

2

Try doing it inside the same stored procedure. I am not aware of an issue like this where it locks the file, but maybe there is until the first one closes. I don't know.

But I do want to mention that this is highly not recommended to be doing inside stored procs/TSQL.

What is calling these stored procs?

3

Hi Tara, Thanks for the response. For sure it is happening due to the call happening within the same scope even though the file is placed in the target folder, the final email dispatch procedure call couldn't read or access it.

I am going to remove this file handling logic from sql server sp call.

will make it from front end c# asp.net app itself.

Thanks a lot for the helpful info.

4

Good choice!