use bcp for export query result to excel

Question

Post reply

use bcp for export query result to excel

elham_azizi_62

SSCertifiable

Points: 5095
More actions
September 11, 2011 at 7:30 am

#256978

hi all.
I use this code for export query result to excel file
set @x = 'bcp "SELECT top 10 [TBL_CustomerID],[TBL_CustomerTitle]
FROM [dbname].[dbo].[TBL_Customer]" queryout c:\test.xls -S local -U sa -P pss -c -C RAW -t "," -r '
exec master..xp_cmdshell @x
go
but excel file not create and see this result in ssms:
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL
how do i do ?please guide me to see test.xls in c:\

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

check this ... http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 2

elham_azizi_62 (9/11/2011)
hi all.
I use this code for export query result to excel file
set @x = 'bcp "SELECT top 10 [TBL_CustomerID],[TBL_CustomerTitle]
FROM [dbname].[dbo].[TBL_Customer]" queryout c:\test.xls -S local -U sa -P pss -c -C RAW -t "," -r '
exec master..xp_cmdshell @x
go
but excel file not create and see this result in ssms:
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL
how do i do ?please guide me to see test.xls in c:\

My first suggestion is to NEVER EVER include the login and/or password for such a thing. ALWAYS use a "trusted connection" when calling BCP via xp_CmdShell.

Also... using "local" for the server name rarely works in such a thing. You should use the actual Server/Instance name.

Also be aware that references to "C:\" refer to the root directly of the SERVER C: drive... not the C: drive on your desktop.

Last but not least, a TAB delimiter is usually better for importing into EXCEL than a comma delimiter. I'd recommend just letting BCP and EXCEL to what they're both best at.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)