Submitted by st on
It's pretty easy on SQL Server but pay attention to the database/server rights of used connection. Usually, the preparing like sp_configure
requires a system administrator privileges.
Activate "xp_cmdshell" option (only once)
EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
Create and run wrapping stored procedure
USE AdventureWorks GO CREATE PROCEDURE dbo.export_to_csv AS BEGIN SET NOCOUNT ON; DECLARE @file_name varchar(1000); SET @file_name = 'c:\temp\'; -- SQL Server account should have rights to write here SET @file_name = @file_name + convert(varchar(16), convert(int, (rand() * 1000000000))) + '.csv'; DECLARE @sql varchar(8000); SET @sql = 'bcp AdventureWorks.Person.Contact out "' + @file_name + '" -c -t; -T -S' + @@servername; EXEC xp_cmdshell @sql, no_output; END GO