0

I'm beginner in SQL Server, and want to save query result auto in csv file.

This is the query:

EXEC master..xp_cmdshell'bcp "SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[StartYear]
  FROM [dbo].[Employees]" queryout "e:\text.txt" -c -T -x

but get this error:

Unclosed quotation mark after the character string 'bcp "SELECT [ID] ,[FirstName] ,[LastName] ,[StartYear] FROM [dbo].[Employees]" queryout "e:\text.txt" -c -T -x

What happened? How can I solve that?

I get this error:

enter image description here

user3671271
  • 551
  • 2
  • 8
  • 21

1 Answers1

0

You can use:

DECLARE @sql VARCHAR(8000);

SET @sql = 'bcp "SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[StartYear]
  FROM [dbo].[Employees]" queryout "e:\text.txt" -c -T -t, -S' + @@servername;

EXEC master..xp_cmdshell @sql;

You need first to enable xp_cmdshell:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Keep in mind that enabling it may expose you to some type of attacks and you should avoid using it.

Alternatively if you need to do it once you can use SSMS -> Tasks -> Export Data -> Flat File Destination

http://www.sqlservercurry.com/2011/01/sql-server-export-table-to-csv.html

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275