Export contents of SQL Server 2008 R2 table to CSV WITHOUT xp_cmdshell

Question

I need to export the contents of a SQL SErver 2008 R2 Express table to a CSV or TXT file.

I cannot enable xp_cmdshell nor can I allow ad hoc distributed queries.

It needs to be executable from a trigger on one of the tables.

Answer 1

USE master;  
GO  
EXEC sp_configure 'show advanced option', '1';  
RECONFIGURE;  
EXEC sp_configure; 
EXEC sp_configure 'Ad Hoc Distributed Queries', '1';  
RECONFIGURE;  
EXEC sp_configure; 

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                       'Text;Database=C:\Temp\;HDR=Yes;',
                       'SELECT * FROM test.csv')
            (object_id, name)

SELECT 'object_id', 'name'

UNION ALL

SELECT object_id, name
FROM sys.tables

--This require csv file to be there at location

Follow this Link , if you face any problem while insertion.

Once working you use same script in trigger.