Reserved word as field name in Format file in bcp.

Question

Post reply

Reserved word as field name in Format file in bcp.

Ashwin M N

Hall of Fame

Points: 3668
More actions
March 29, 2010 at 4:50 am

#156827

I am importing a very large data into a table using bcp command.One of the column in the table is a reserved word, I am
getting this error
Starting copy...
SQLState = 37000, NativeError = 156 Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Plan'
How can I handle the reserve word while importing data into a table?
I have attached an example.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

You might try the '-q' parameter so that quoted identifiers are used.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Ashwin M N Hall of Fame Points: 3668 More actions · Answer 2

Hi Bhuvnesh, can you show me for the attached example how to use the

'-q' parameter in bcp for the column names.

Ashwin M N Hall of Fame Points: 3668 More actions · Answer 3

Ashwin M N

Hall of Fame

Points: 3668

March 29, 2010 at 5:27 am

#1141316

Thanks Bhuvnesh I got it

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

Ashwin M N (3/29/2010)
Thanks Bhuvnesh I got it

Got what? Two way street here... what "got it"? What was the fix?

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

Ashwin M N Hall of Fame Points: 3668 More actions · Answer 5

I used [] for column names in the format file and in the bcp command prompt I added -q.

bcp AdventureWorks.HumanResources.myTeam in input.dat -U username -S servername -t , -f myTeam.Fmt -q

It is working fine.

But I have another problem in another table The column is of bit datatype not null default(0) how should I handle this.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

Always try to post exact error to get faster response :-).also post BCP query

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

Ashwin M N (3/29/2010)
But I have another problem in another table The column is of bit datatype not null default(0) how should I handle this.

Instead of using -n (native) datatype (data-format switch), try using -c (character).this might help you.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Ashwin M N Hall of Fame Points: 3668 More actions · Answer 8

I tried using -c

bcp AdventureWorks.HumanResources.myTeam in input.dat -U username -S servername -t , -f myTeam.Fmt -c

It gave the message that -f overwrites the -c

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

NO idea about that message

This link might help you http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.utility/html/utility/utility73.htm

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)