DBA-Philiac

SET QUOTED_IDENTIFIER ON declare @yesterday varchar ( 20 ), @filename varchar ( 200 ), @bcpcommand varchar ( 500 ) select @yesterday = convert ( varchar , DateAdd ( "d" , - 1 , GETDATE ()), 112 ) set @filename   = 'E:\Temp\MYFILE_' + @yesterday + '.csv' print @filename set @bcpcommand = 'bcp "select * from databasename.dbo.table" queryout' + ' ' + @filename + ' -c -t, -T -SMYSERVER\MYINSTANCE' print @bcpcommand exec master .. xp_cmdshell @bcpcommand

## Using OpenRowSet we can load data from an xl file to a table insert into dbo.Customers_xl SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',   'Excel 8.0;IMEX=1;HDR=NO;DATABASE=E:\sample_entry.xls', 'Select * from [Sheet1$]'); ## Using BCP (bulk copy) we can write the output of a XPATH query to an xml file. declare @sql varchar(8000) SELECT @sql = 'bcp "SELECT TOP 5 * FROM AdventureWorks.HumanResources.Department for xml auto,elements" queryout c:\myXML.xml -c -t -T -S "MININT-CM4Q9R0\SQLSERVERDEV2008"' exec master..xp_cmdshell @sql before doing this The first thing you have to do is make sure xp_cmdshell is enabled EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE EXEC master.dbo.sp_configure 'xp_cmdshell', 1 RECONFIGURE  ref: http://www.mssqltips.com/sqlservertip/1633/simple-way-to-export-sql-server-data-to-text-files/ http://thiagsundar.wordpress.com/export-data-to-text-file/ ...