Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Export SQL query in CSV

Posted on 2013-10-24
3
Medium Priority
?
573 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1500 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

618 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