How best to perform File I/O within SQL

Last Post 09 Aug 2006 12:16 PM by EdCarden_SQL. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
EdCarden_SQL
New Member
New Member

--
08 Aug 2006 01:27 PM
I posted this same question yesterday at another site and have not yet gotten 1 response so I'm hoping to have better luck here.

I have been tasked with finding an alternative method to using xp_CmdShell for doing some basic I/O with a single file. Specifically I need to move a file from it's current location into a sub-directory of that same location and also change the name of the file. Why? The file (csv) contains records for an import and when the import is done the file needs to be moved to a sub-directory and renamed. The importing of the data in the csv file is already being handled by native functionality within trhe product I support so I need only worry about the file moving/renaming. Orginally our custom dev staff same up with a SP that calls xp_CmdShell to move & rename the csv file. For security reasons the use of xp_CmdShell is a big no no. When management realized the solution developed by programming for our client could not be implemented, they asked the IT group that manages the servers, both the DB serves as well as Web & Files Servers where we host the clients data along with our application, what alterntiaves they suggest. DTS was recomended as the alterntaive to use. Since I am the only one with some knowledge of DTS I was aksed to look into this. That's how I got involved.

Now that I look at this I'm not so sure that DTS is the best route and so I was hoping to solicit so input from some more experienced users out there. I have found what I believe are a couple of different options on how to handle the file move & rename.

1) DTS Package

2) sp_OA... SP's (instantiationg the FileSystemObject)

3) Other method not yet mentioned..



The reason xp_CmdShell can't be used is because of the permissions it requires and the hole in security that it opens.

Does anyone have any feedback on either of the above 2 solutions and or this whole process in general, that is how best to handle file operations from with SQL.



Thanks

mwesch
New Member
New Member

--
08 Aug 2006 02:51 PM
If timing is not an issue, you could schedule copy/rename through OS scheduler.
EdCarden_SQL
New Member
New Member

--
09 Aug 2006 12:16 PM
Thanks to all fro your replies.
To address a few questions about this.

Yes it has to all be done within SQLServer/SQL and yes it is time sensative. Not only mus the file move/rename occur at a specific time every day but it must occcur after another piece of SP code executes.
An external vb script or anything else like it is not an option and that's something I have no control over. In truth I probably wouldn't be allowed to go with using the sp_OA XP's either. DTS was suggested by those in the IT dept but I wanted to make sure there wasn't a better option to go with. So DTS it is.

Thanks again.
SQLUSA
New Member
New Member

--
08 Apr 2007 01:49 AM
You can also put the xp_cmdshell into a stored procedure and permission the sproc appropriately.

This is however another option, I am not suggesting that it is anyway safer than DTS. In fact you can even do a DTSRUN from a stored proc.

Kalman Toth, Database Architect
SQL Server Training - http://www.sqlusa.com/bestpractices2005

SwePeso
New Member
New Member

--
08 Apr 2007 08:08 AM
Original poster wrote
quote:

For security reasons the use of xp_CmdShell is a big no no
because they wanted to implement the solution at several customers.
SQLUSA
New Member
New Member

--
08 Apr 2007 03:14 PM
In this kind of cases, SQL Server 2005 EXECUTE AS facility will be very helpful!

Do you agree Peter?

Kalman Toth, Database Architect
SQL Server Training - http://www.sqlusa.com/bestpractices2005
SQLUSA
New Member
New Member

--
14 Oct 2007 03:46 AM
Not easy. You want to perform file system operations withouth giving permission to someone to do so?

You can put the commands into a .cmd file .

Schedule periodic execution of the .cmd file in SQL server agent.

Kalman Toth
www.sqlusa.com
You are not authorized to post a reply.

Acceptable Use Policy