• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

Export SQL query in CSV

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
kbjijo
Asked:
kbjijo
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
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
 
kbjijoAuthor Commented:
i am using basic query. How can i re direct to CSV

select * DB_Name

Thanks in Advance
0
 
lcohanDatabase AnalystCommented:
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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now