将SQL Server 2008 R2表的内容导出到CSV，而无需使用xp_cmdshell [英] Export contents of SQL Server 2008 R2 table to CSV WITHOUT xp_cmdshell

问题描述

我需要将SQL SErver 2008 R2 Express表的内容导出到CSV或TXT文件.

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

我无法启用xp_cmdshell，也不允许临时分布式查询.

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.

推荐答案

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.

这篇关于将SQL Server 2008 R2表的内容导出到CSV，而无需使用xp_cmdshell的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！