1

I need to export the content of a Table into a file CSV.

I tried to use the execution of a xp_cmdshell from a stored procedure but it doesn't work because this component is turned off as part of the security configuration for this server.

Do you know others way to write a file from a stored procedure?

Arulkumar
  • 12,966
  • 14
  • 47
  • 68
Nello
  • 11
  • 1
  • 4
  • Go through this link it may helps you https://stackoverflow.com/questions/40028326/export-contents-of-sql-server-2008-r2-table-to-csv-without-xp-cmdshell –  Jul 05 '17 at 07:39
  • Can't use sql server's export utility ? Else : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/75329577-ea25-4898-b533-a6c311d051d9/export-data-from-stored-procedure-to-csv?forum=transactsql – Prabhat G Jul 05 '17 at 07:41
  • @Srini131 I've tried the solution described in the link. But it use Ad Hoc Distributed Queries and I have this error: SQL Server Database Error: SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. – Nello Jul 05 '17 at 08:20
  • Thanks @PrabhatG, but I must export this table automatically during the execution of a Stored Procedure, not manually. And without use xp_cmdshell – Nello Jul 05 '17 at 08:22
  • You could do your own SP CLR in .Net to achieve this. Useful if you need custom stuff – Jimbot Jul 05 '17 at 08:41

1 Answers1

1

Here are a couple of methods you can try :

1. Using BCP

syntax : 

bcp "SELECT * FROM Database.dbo.input" queryout C:\output.csv -c -t',' -T -S .\SQLEXPRESS

microsoft document : BCP Utility

2. Second Method(for excel, but should work for csv too) :

Insert into OPENROWSET
('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

For this, you need to enable adhoc distributed queries by following command :

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

you might encounter a link server error. So you should refer : this stack overflow solution

Prabhat G
  • 2,974
  • 1
  • 22
  • 31