Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kgundava
Yak Posting Veteran
66 Posts |
 Posted - 2011-08-11 : 10:21:40
|
| Hi All, I have a situation where i have to write a query output to a File.The problem is that i have to write the column headers to the file. Pleas let me know if it is possible.DECLARE @cmd14 sysname, @var19 sysname ,@TEST_RESULT_FILE VARCHAR(8000),@TEST_NAME VARCHAR(8000),@VAR20 VARCHAR(800)Select @TEST_RESULT_FILE = 'bcp "SELECT * FROM TEST" queryout "E:\New\ton.txt" -U admin -P PWD -c'print(@TEST_RESULT_FILE)EXEC master..XP_cmdshell @TEST__RESULT_FILEThanks........ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
kgundava
Yak Posting Veteran
66 Posts |
Posted - 2011-08-11 : 12:02:19
|
| Hi Visakh, Thanks for the reply.In order to avoid such a lengthy code to acheieve what i need. I was wondering whether there is any other way to write the output to a file in sql server (i.e. sqlcmd,OSQL etc)?.Please see my requirements below and if possible let me know your inputs.1) Write to output file which would have column headers included.Column names would have special characters in them and a table might have 200+ column names.2) I would be using a simple query like Select * from.... 3) The output file would not have more than 1000 rows.Thanks..... |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-11 : 12:05:38
|
| it is not a lengthy code. if you see, its just grabbing the column names for the table using INFORMATION_SCHEMA.COLUMNS catalog view and then using actual query and merging them with UNION ALL. its then applying bcp over this.If you want to avoid this, you can use simple SSIS package to do this data transfer by specifying column names in first row option for flat file destination.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
BCP COLUMN HEADERS