December 11, 2012 at 3:06 pm
Superb Jeff, also is there any similar command for copy the files?
December 12, 2012 at 7:38 am
sqlquery-101401 (12/11/2012)
Superb Jeff, also is there any similar command for copy the files?
If by "copy files", you mean to import a file to a table, then yes. You can use BULK INSERT in one form or another.
If by "copy files", you mean are there T-SQL commands to copy a file from one place on disk to another place on disk, then unfortunately there's not (other than xp_CmdShell, that is) and that's why many people like SSIS and Powershell and scripts and ActiveX components and CLR (more on that in a minute) and.... I can't help there because I've got my systems locked down to properly use xp_CmdShell.
There are a number of work arounds without using SSIS. You can, for example, run such tasks in an SQL Agent job which can include "Operating System (CmdExec)" and "Powershell (as of 2008)" steps.
Shifting gears a bit, I've always thought this (file handling) would be a great place for a CLR because you can impart some great security, provide some serious limitations on what it can be used for, make most file handling as easy as calling a normal proc, and still do some serious auditing of who did what just like you might in a T-SQL proc. If your company is going to do a whole lot of file handling and the tool of choice is going to be T-SQL and stored procedures, the short term investment of writing a small handful of thoughtful CLRs to do the file handling can have a really good ROI.
--Jeff Moden
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply