4

I'm using Microsoft SQL Server 2008 R2 and I try to dump a table via bcp (with a scheduled Job). First of all I implemented it with xp_cmdshell, but I've heard, that this should be avoided due to security reasons.

So my question: What's about sp_oaMethod? Should it be used? If not, why not? And could you suggest other possibilities?

user1830058
  • 41
  • 1
  • I recently had reliability problems with using `sp_oa...` and writing files (FSO), and moved to xp_cmdshell with no issues. I never found the cause. Not an answer, but may help you decide if you're on the fence. – Tim Lehner Nov 16 '12 at 15:46
  • thank you for your response ... I'm not allowed to use xp_cmdshell anymore - It's only due to the security issues, not because of functionality – user1830058 Nov 16 '12 at 15:56
  • Why would you use the sp_OA% procedures when you could use CLR support instead? It's much better and will give you a lot more flexibility. Although if you just want to run bcp.exe then you can simply put it in a batch file and call it from the SQL Agent job, or even call bcp.exe directly. – Pondlife Nov 16 '12 at 19:39
  • xp_cmdshell can be used safely, but it should be disabled if there is a risk of sql injection associated with that ms-sql account. Simply not using it doesn't help secure system, that is just silly. – rook Nov 18 '12 at 00:33

1 Answers1

1

A better solution than xp_cmdshell is to use a SQL agent job step of type "CmdExec" or "Powershell".

CmdExec would be my preference for a one-liner call to BCP.exe. Sysadmin credentials are needed to create the job step.

Powershell is your answer if you need to perform any additional tasks, for example zipping the extracted file and copying it to a network share.

David G
  • 94,763
  • 41
  • 167
  • 253
StrayCatDBA
  • 2,740
  • 18
  • 25