Solved

Export SQL query in CSV

Posted on 2013-10-24
3
559 Views
Last Modified: 2013-10-25
Hi,
I have SQL query which create report for health check purpose. I need to export the same into CSV so that we can  do auto mail of report.

Please post with any sample code for the same.
0
Comment
Question by:kbjijo
  • 2
3 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 39598339
Like this using SQLCMD:

SQLCMD -S . -d AdventureWorks2012 -Q “SELECT TOP 10 sp.BusinessEntityID, sp.TerritoryID, sp.SalesQuota, sp.Bonus, sp.CommissionPct FROM Sales.SalesPerson sp” -s “,” -o “e:\result.csv”


or you can run it via t-sql if you have cmdshell enabled like:

exec master..xp_cmdshell N'SQLCMD -S -d AdventureWorks2012 -Q ''SELECT TOP 10 sp.BusinessEntityID, sp.TerritoryID, sp.SalesQuota, sp.Bonus, sp.CommissionPct FROM Sales.SalesPerson sp'' -s '','' -o ''e:\result.csv'''
0
 

Author Comment

by:kbjijo
ID: 39598458
i am using basic query. How can i re direct to CSV

select * DB_Name

Thanks in Advance
0
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 39598561
First you must check and make sure xp_cmdshell is enabled however...you must check your security policies to see if you can do that - like:


exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
exec sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO


Then run a SQL query like:

exec master..xp_cmdshell N'sqlcmd -E -Q"set nocount on; select * from DB_Name.dbo.TableName;" -s"," -W -o"c:\output.csv"'


and please keep in mind the c:\output.csv will be ON the server where SQL is installed.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
IF SQL Query 12 29
Sql case statement to calculate totals 5 37
TSQL - How to get output results in row with additional character(comma) 7 16
SQL profiler 3 18
Every server (virtual or physical) needs a console: and the console can be provided through hardware directly connected, software for remote connections, local connections, through a KVM, etc. This document explains the different types of consol…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question