Solved

Export SQL query in CSV

Posted on 2013-10-24
3
545 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 39

Expert Comment

by:lcohan
Comment Utility
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
Comment Utility
i am using basic query. How can i re direct to CSV

select * DB_Name

Thanks in Advance
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
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 Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Truncate vs Delete 63 86
Would coalesce be what I use here and, if so, how? 8 34
Calculating Business Hours 18 58
Azure SQL DB? 3 11
Usually shares are where we want them for our users and we tend to take them for granted. There are times, however, when those shares may disappear causing difficulty for your users. One of the first things to try is searching for files that shou…
More or less everybody in the IT market understands the basics of Networking, however when we start talking about Storage Networks, things get a bit dizzier, and this is where I would like to help.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now