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!