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.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2003-10-24 : 15:56:16
|
| It's possible using xp_cmdshell to copy a file that is currently in the process of being FTP'ed to the server.Is there a way to detect that the file is open by the User's FTP so I can try to DTS the file to the database later ?Sam |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-24 : 16:27:54
|
| It seems you'll have to write some code to check for it:[url]http://www.swapcode.com/domdoc/page/lscode003.html[/url]Tara |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-10-24 : 16:34:52
|
| Thanks Tara but: Argh! I wanted to stay within SQL and sp_cmdshell.Sam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-24 : 16:43:53
|
| Well I don't know if there is a DOS command that would help you out. You could put the code in a VBScript file then have xp_cmdshell run the VBScript. There might be some API that you can call as well. At any rate, I think that you'll have to call a program with xp_cmdshell and not actually run a DOS command.Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-24 : 16:45:58
|
| You can use the scripting object to do the same thing from t-sql.Thought I had an example on my web site but can only find the activex one.I'll ty to do a t-sql one when I get timehttp://www.nigelrivett.net/DTSActivexFileExists.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-10-24 : 16:49:19
|
| Thanks Nigel.Just to make sure scripting would work, I have a loop in a stored proc that inspects every row of an IMPORT table and uses xp_cmdshell to copy, rename, and DTS in the file. I need to get a flag if the file is in use, in the context of the stored proc to alter the flow.Sam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-24 : 16:50:36
|
| Nigel, how does that code detect if the file is open?Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-24 : 16:53:09
|
| declare @i int, @i2 int, @rc int exec @rc = sp_OACreate 'Scripting.FileSystemObject', @i OUT select @rc, @i exec @rc = sp_OAMethod @i , 'OpenTextFile', @i2 out, 'c:\a.txt' , 1 , 0 select @rc, @i2==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Detecting imported file state