0

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.

Maa421s
  • 159
  • 1
  • 12
  • 1
    This sounds like a horrible hack to overcome an even more horrible design pattern. (Sorry, I don't mean to be rude.) But if you really want to go down this path, you might look into a SQL Agent job with a job step containing a powershell script to export table data to a file. A trigger could invoke `msdb.dbo.sp_start_job`. But seriously, yuck! – Dave Mason Oct 13 '16 at 20:07
  • Why does it need to be on a trigger? @DMason has it right IMO. – SMM Oct 13 '16 at 21:29
  • Yes, it is a horrible hack to try to get data out of an outdated system (circa 1999) where the source code would be extremely difficult to modify. DB can be modified easily. It's also running SQL Express, sorry I forgot to mention, so no SQL Agent. I only want the extract updated if there is new data, thus the trigger. My other solution is Task Scheduler with a PS script, but it seems stupid to keep running it when there may be no updates. The extract only needs to be created when there is new data. The extract is picked up and transferred to another system for reporting. – Maa421s Oct 15 '16 at 03:47

1 Answers1

0
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.

Community
  • 1
  • 1
Harsimranjeet Singh
  • 514
  • 2
  • 6
  • 19
  • sorry, as stated in my post, I cannot enable 'ad hoc distributed queries'. I am assuming that this will not work otherwise. thanks though – Maa421s Dec 12 '16 at 20:24