We need to write the SELECT query results to a csv file. How can it be done using T-SQL in SQL Server 2008 r2? I know that it can be done in SSIS, but for some reasons, we don't have this option.
I tried to use the suggested proc in the article below, but when I run the proc, SQL complains that can't run sys.sp_OACreate and sys.sp_OADestroy which are called in this proc.
Do you know how we can turn on these components or know any better way to write to a file using T-SQL?
Thanks in advance.
Best Answer
Use BCP utility
The
-c
argument specifies character output, as opposed to SQL's native binary format; this defaults to tab-separated values, but-t ,
changes the field terminator to commas.-T
specifies Windows authentication ("trusted connection"), otherwise use-U MyUserName -P MyPassword
.This doesn't export column headers by default. You need to use a UNION ALL for headers
OR
Use SQLCMD
OR
Use Powershell
Here is a link to an article. If you end up using this, you might want to append
-notype
afterExport-Csv $extractFile
to get rid of the unnecessary column on the output file.