0

I am currently working in an environment where the ability to export a table programatically from within a hand-run SQL script would be of great help.

Performing the exports from script will be the first step towards running the entire process from within a stored procedure, therefore I have to be able to initiate the export from SQL.

The organisation currently has the following configuration on most servers -

  • SQL Server 2005 or 2008
  • xp_cmdshell - disabled
  • CLR - enabled

Ultimately, I would like to be able to call a procedure passing the following parameters and have it perform the export.

  • table name
  • file path/name (on a network share)
  • file format

Currently BCP seems like a perfect option in terms of functionality but I am unable to invoke it via the command line due to xp_cmdshell being disabled.

The organisation is quite small and happy work towards a secure solution and my impression so far is that they have a good level of control over their security. They have made a blanket decision to disable xp_cmdshell but if I could present a safe way to allow use of it I think they would be pretty receptive.

In my research I've come across both the 'EXECUTE AS' functionality as well as signing procedures with certificates, but still cant work out if either approach can help me achieve what I want.

Also, if you have another solution that allows me to achieve the same end-result I'm all ears!

samaspin
  • 2,342
  • 1
  • 26
  • 31
  • Why not have an app do this for you (you don't necessarily need BCP - a C# command-line app would be simple enough to write) - then all you have to do is give the users permissions to grab files from the folder where the app dumps the files. Or it could even e-mail them or put them in a network location the user already has access to. – Aaron Bertrand Oct 29 '12 at 17:57
  • How would I invoke the app? command line? Also, Being able to place the exported tables in a specific location is an essential part of the process as I am automating a manual process. The files could need to be placed anywhere under a certain folder on one of 2 network shares. – samaspin Oct 29 '12 at 18:00
  • Wouldn't I have the same problem calling a C# command-line app as I currently have trying to call BCP? – samaspin Oct 29 '12 at 18:07
  • You could invoke the app from just about anywhere - a web page, a caller app, etc. – Aaron Bertrand Oct 29 '12 at 18:07
  • No, the problem you're encountering calling BCP is that you're doing it from within SQL Server and xp_cmdshell is disabled. A novel idea: have an app that calls BCP (or just dumps a SELECT from a table to a file) without going through T-SQL. – Aaron Bertrand Oct 29 '12 at 18:08
  • Thanks Aaron, I've edited my question to try and make my requirements clearer. I'm attempting to automate a process, end-to-end, if I have to stop and kick-off an external app manually then I wont have gained much in this scenario. I think they would consider enabling xp_cmdshell if they can maintain control over its access but I need to present a safe solution to them. I think I might revise my question title to try and make things clearer. Sam. – samaspin Oct 30 '12 at 09:30

1 Answers1

0

As Aaron Bertrand pointed out, the problem is xp_cmdshell disabled.

There are two options you may consider.

  1. Use BULK INSERT. Requires INSERT and ADMINISTER BULK OPERATIONS permissions

  2. Create a SQL Agent job that type is "Operating System(CmdExec)" to run BCP. you may need code to create/update jobs for passing parameters.

EricZ
  • 6,065
  • 1
  • 30
  • 30
  • Thanks Eric but I need to perform an Export, not an import. xp_cmdshell is disabled but if I could find a way to only allow safe use I think the might consider turning it on. I've read about the SQL Agent technique and it seems a bit too hacky for me personally although I realise a lot of people have employed it successfully. – samaspin Oct 29 '12 at 19:41