x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Once I'm done processing files, How can I archive them?

The following stored procedure will take a supplied file path, create an archive folder within the supplied folder path, and move everything in to that folder to basically clear out the base folder.  The Archive folder gets tagged with the date and time of the operation.  The stored procedure uses code for the Dir procedure I wrote earlier.  In that link there is DDL for the table that is referenced in this sproc. Thanks for reading.

CREATE PROC Archive(@path varchar(2000))
AS
SET NOCOUNT ON
  BEGIN
 DECLARE @cmd varchar(4000), @today varchar(25), @archive varchar(2000)

 EXEC Dir @path, 1

 IF NOT EXISTS(SELECT * FROM Directory_Contents WHERE Dir = @path)
   BEGIN
  PRINT 'No directory was found for ' + @path
  GOTO Archive_Error
   END

 IF NOT EXISTS(SELECT * FROM Directory_Contents WHERE Dir = @path AND Struct_Type = 'FILE')
   BEGIN
  PRINT 'No files in directory ' + @path + ' to Archive'
  GOTO Archive_Error
   END

 SELECT @today = REPLACE(REPLACE(REPLACE(CONVERT(varchar(25),GetDate(),120),' ','_'),':','_'),'-','_')
 SELECT @archive =  + SUBSTRING(@path,1,1+LEN(@path)-CHARINDEX('\',REVERSE(@path))) + 'Archive' + '_' + @today
 SELECT @cmd = 'md "'+ @archive + '"'
 
 EXEC master..xp_cmdshell @cmd
 
  SELECT @cmd = 'move "' + @path + '" "' + @archive + '\"'
 EXEC master..xp_cmdshell @cmd

 Archive_Exit:
  SET NOCOUNT OFF
  RETURN

 Archive_Error:
  -- Add any additional Error Handling
  GOTO Archive_Exit
  END
GO

 

Print | posted on Thursday, June 30, 2005 3:40 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Once I'm done processing files, How can I archive them?

I really think this is an instance where you should keep knowledge of a programming language handy and do this task in it rather than in sql server. And the fact that you put this out as if recommending it to others makes it that much more out of place.
8/17/2005 8:16 PM | shriop
Gravatar

# OK, I'll byte

Thanks for your comments. Would you care to elaborate as to why this, in your opinion, is not a good idea?

I prefer to keep this within the transaction that is doing the work. So for instance, a task has to be accomplished, and you need to process 4 files, and let's say, the first 2 have worked, and I've archived them, but the third fails. What would you do? Keep the processing all as 1 transaction and rollback everything.

My process will just pick up where it has left off.

And since T-SQL by it's very nature is procedural, what's the difference.

And, as an aside, SQL Server 2005, only pushes in this direction...substantially with CLR.

Thanks again for the comments, and thanks for reading.
8/18/2005 9:07 AM | Window Washer
Gravatar

# re: Once I'm done processing files, How can I archive them?

Running something from inside the CLR is considerably different than running something using xp_cmdshell, but still, messing with files from inside SQL Server is going to cause all kinds of security issues. Also, from say .Net code, you would get standard controllable exceptions if moving these files caused any issues such as running out of space or running into a security exception. You just don't have full control of exceptions, or full power over these files because you're working inside an environment that is not made to do these types of things. I'm also a little worried what you're doing to your sql server threads having them wait on an io operation like this. Jumping out to xp_cmdshell is pretty intensive. It's hard to explain, but SQL Server is just not the proper tool to be doing file io with.
8/18/2005 4:57 PM | shriop
Gravatar

# Good Point

Thanks for the insight...I don't notice any problems with I have today, but if there are problems with the file, size, out of space ect, yes I can see the problem.

Same with the length of the transaction...I've never had to worry to much because they file are on the same drive and they don't physically get "copied" so it really never takes long at all.

Thanks again for the input

What do you think about COM objects?


8/18/2005 5:04 PM | Brett
Gravatar

# re: Once I'm done processing files, How can I archive them?

I'm sure that "COM", in the language of some ancient, long ago extinct civilization translates to "The Devil", but I have yet to find any documentation to that fact.

As for using COM objects from inside SQL Server, it really just depends on the object. You're still going to have some security issues, and you're still bailing out to things outside of the control of SQL Server, like screwed up memory management, etc. If you're asking whether using your method is better or worse than using the FileSystemObject, if you can get the object working ok, it would probably be better because it does give you some of the lower level control as to what exactly is going on with the files, but it is still file io, and you are still locking up one of your important worker threads to wait until this process is complete. Again, I would tend to just set up an architecture where SQL Server handles data and data manipulation, and outside code handles everything else. Easier said than done I know, but that's my goal.
8/18/2005 6:38 PM | shriop
Gravatar

# re: Once I'm done processing files, How can I archive them?

It's a continuum. If there is only a few lines of code, do it in T-SQL. If there are many lines of code, you might want to do it in a more robust language. After a few dozen lines of T-SQL I usually start wishing I had a real language to work in. If T-SQL can't do it, but you can do it in a single line of batch, then use xp_cmdshell. If the code already exists in COM and it would take a week to rewrite in TSQL, then use sp_OA & call COM.
9/7/2005 11:16 AM | Matthew Martin
Gravatar

# re: Once I'm done processing files, How can I archive them?

My process will just pick up where it has left off
2/8/2006 12:39 PM | maduras follando
Gravatar

# re: Once I'm done processing files, How can I archive them?

VERY GOOD BRETTK
2/10/2006 7:54 PM | putilla
Gravatar

# re: Once I'm done processing files, How can I archive them?

OK
3/21/2006 11:27 AM | james saiz
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET