BCP OUT xp_cmdshell errors out maximum column

Question

Post reply

BCP OUT xp_cmdshell errors out maximum column

Saran

Right there with Babe

Points: 760
More actions
January 6, 2023 at 3:01 pm

#4134553

Hi All,
I am doing BCP out the table having 175 columns. For small table columns it works fine.
It errors out for this big column table.
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT col1...col175 FROM tbl123"
Msg 214, Level 16, State 201, Procedure master.dbo.xp_cmdshell, Line 1 [Batch Start Line 37]
Procedure expects parameter 'command_string' of type 'varchar'.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

We will need your help in order to be able to help you, so please help us!

😎

It would be greatly appreciated if you could provide the DDL (create table) script, sample data as an insert statement, the desired output from the sample data, and what you have tried so far.

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

Saran wrote:

Hi All,
I am doing BCP out the table having 175 columns. For small table columns it works fine.
It errors out for this big column table.
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT col1...col175 FROM tbl123"
Msg 214, Level 16, State 201, Procedure master.dbo.xp_cmdshell, Line 1 [Batch Start Line 37] Procedure expects parameter 'command_string' of type 'varchar'.

You're missing some things in your command. You need to go back and read up on the BCP command. For example, you're not even identified which database it should read from. You're also missing the closing single quote, which is the cause of the "expects" error.

--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)

Saran Right there with Babe Points: 760 More actions · Answer 3

Sorry, I just shown as example.

I am using STRING_AGG from INFORMATION_SCHEMA.columns and other system table to populate all the columns to BCP.

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 4

I guess I don't understand why you think that you need to list every column. If you're trying to export the whole table, then export the whole table.

--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)

Saran Right there with Babe Points: 760 More actions · Answer 5

I thought of using quotename. select * is not working some places found that because of CR LF.

cyrusbaratt SSC-Addicted Points: 417 More actions · Answer 6

I am not sure if this will help you. I had a similar situation with a table with large number of fields. I concatenated all the fields together to one text field using proper bcp command options. This seems to work fine.

I hope this helps.

DBASupport

frederico_fonseca SSCoach Points: 16155 More actions · Answer 7

Saran wrote:

I thought of using quotename. select * is not working some places found that because of CR LF.

select * does NOT CARE about CRLF - other than the thread you have with extracting data to load to MySQL do you have other extracts that truly require you to remove those from the data being exported?