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 |
|
pvccaz
Yak Posting Veteran
87 Posts |
 Posted - 2010-10-19 : 12:32:14
|
| Hi,I am trying to export the sql data into a excel file. So i used the bcp along with xp_cmdshell.The excel file after it gets generated gets stored in the DBServer.1) Is there a way to direct the output xls it to some other server that has a share directory ?2) The column headings are not getting exported. Do i need to generate a temp table, store the column headings along with data and then output it. Is there any other way. ?I had given the code below. Any ideas please ?DECLARE @FileName varchar(50), @bcpCommand varchar(2000)SET @FileName = REPLACE('c:\Data'+ CONVERT(char(8),GETDATE(),1)+'.xls','/','-')SET @bcpCommand = 'bcp "SELECT CustomerName, CustomerEmail FROM DBSVR.DbName.dbo.tableName" queryout "'SET @bcpCommand = @bcpCommand + @FileName + '" -c -T'print @bcpCommand EXEC master..xp_cmdshell @bcpCommand |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
pvccaz
Yak Posting Veteran
87 Posts |
Posted - 2010-10-19 : 19:54:27
|
| Thank you vishak. It worked !!.. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-10-20 : 12:45:10
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Export file using xp_cmdshell