Trying to do a BCP export of a table

Question

Trying to do a BCP export of a table

itmasterw 60042

SSCrazy

Points: 2858
More actions
August 28, 2018 at 7:21 am

#415513

Hi,
I am Trying to do a BCP export of a table. I am using the following that I got online:
EXEC master..xp_cmdshell 'bcp "SELECT Dph_index,file_no,phone_no From DMPCNI.dbo.Star" queryout "C:\Temp\Star2.csv" -c -T -S PS2016\DMP2'
This doesn't give an error when I run it, it just comes back with showing me the different options I can use (like -c, -t etc.); and it does not write to the file.
Any ideas what I am doing wrong here?
Thank you

Viewing 13 posts - 1 through 12 (of 12 total)

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

Jackie Lowery SSCommitted Points: 1886 More actions · Answer 1

I'm not sure about this particular command, but command options are usually case sensitive.
The output error seems to indicate the -t option should be lowercase.
You may want to try.

EXEC master..xp_cmdshell 'bcp "SELECT Dph_index,file_no,phone_no From DMPCNI.dbo.Star" queryout "C:\Temp\Star2.csv" -c -t -s PS2016\DMP2'

Jackie Lowery SSCommitted Points: 1886 More actions · Answer 2

A good MS article.

https://blogs.msdn.microsoft.com/timart/2012/08/30/bulk-copy-use-bcp-from-sql-to-concat-databases/

Jackie Lowery SSCommitted Points: 1886 More actions · Answer 3

Upon reading the article a bit, I'd point out that you need to use an uppercase '-S' to identify the server name.

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 4

Thanks but I tries that as well as other options and I still get the same thing, but thanks for your help, and I look at the link you provided.
Thanks

Jackie Lowery SSCommitted Points: 1886 More actions · Answer 5

Also, the -t may not work without a separator arguement such as a comma. i.e. '-t,'

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 6

Thanks the link helped I got it to export, but for some reason it does not include the column names, Any idea what I can do there?

Jackie Lowery SSCommitted Points: 1886 More actions · Answer 7

itmasterw 60042 - Tuesday, August 28, 2018 10:00 AM
Thanks the link helped I got it to export, but for some reason it does not include the column names, Any idea what I can do there?

Try changing your select to something like this. Of course, you may have to cast integer columns in your table to varchar type.

"select 'col1', 'col2',... union all select * from mytable"

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 8

Yah I seen a lot of people on line had that, and I tried it and but is then says that the BCP failed

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 9

This is what I had tried:
SELECT Dph_index, file_no, phone_no UNION ALL SELECT Dph_index, file_no, phone_no FROM

Jackie Lowery SSCommitted Points: 1886 More actions · Answer 10

itmasterw 60042 - Tuesday, August 28, 2018 10:42 AM
This is what I had tried:
SELECT Dph_index, file_no, phone_no UNION ALL SELECT Dph_index, file_no, phone_no FROM

You need single quotes around the column names. Like this.

SELECT 'Dph_index', 'file_no', 'phone_no' UNION ALL SELECT Dph_index, file_no, phone_no FROM

sgmunson SSC Guru Points: 110639 More actions · Answer 11

Jackie Lowery - Tuesday, August 28, 2018 11:48 AM
itmasterw 60042 - Tuesday, August 28, 2018 10:42 AM
This is what I had tried:
SELECT Dph_index, file_no, phone_no UNION ALL SELECT Dph_index, file_no, phone_no FROM
You need single quotes around the column names. Like this.
SELECT 'Dph_index', 'file_no', 'phone_no' UNION ALL SELECT Dph_index, file_no, phone_no FROM

This could be problematic unless ALL the actual column data has a varchar data type, as otherwise every data type in the table would have to be convertible to varchar implicitly.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jackie Lowery SSCommitted Points: 1886 More actions · Answer 12

sgmunson - Tuesday, August 28, 2018 12:48 PM
This could be problematic unless ALL the actual column data has a varchar data type, as otherwise every data type in the table would have to be convertible to varchar implicitly.

Right. I was just giving an example. He would have to use the correct method to convert each column.