bcp NULL data into table

i'm trying to use bcp to input some data into a table which has columns accepting null values.  i have tried using /c and i've also tried using /f with a format file modified to look like this:

1 SQLCHAR 0 0 "" 0 column1
2 SQLCHAR 0 10 "," 2 column2
3 SQLCHAR 0 0 "" 0 column3
4 SQLCHAR 0 10 "\n" 4 column4

etc. . . and i always get "attempt to bulk copy an oversized row. . ."

i have tried to made my data look like this:
12345467890,1234567890\n

i've tried (with different fmt file)
1234567890,,1234567890\n

1234567890,NULL,1234567890\n
1234567890,(null),1234567890\n

and nothing seems to work.  i have counted and recounted - i have the same number of data columns in my text file  as are on the server, and the NULL values are allowed. . .

what's going on?
LVL 2
Billy_PilgrimAsked:
Who is Participating?
 
Gustavo Perez BuenrostroConnect With a Mentor Commented:
Use -c argument of BCP utility. Your data must look as follows:

1234567890->->1234567890->
1234567890->->1234567890->

PD: "->" means tab ASCII character.

If you need a better approach post your table definition and some data.
0
 
simonsabinCommented:
If your data is in the format

1234567890,,1234567890
1234567890,,1234567890
1234567890,,1234567890

then using
BCP database..table in file /c /t, /r\n /SServer /UUser /PPassword /ddatabase


This will put NULLs in second column
 
0
 
Billy_PilgrimAuthor Commented:
i'm going to accept your answer, because you were right--

but my real problem was that i was creating my output file in C and i had fopen()'d the file in "wb" or binary mode by mistake, which left off the \r in front of \n. . .

and even though i explicitly told bcp /r\n it looked for /r\r\n. . .
0
 
Gustavo Perez BuenrostroCommented:
If you need something else, please give me more information and let me help you.

Don't award me points if I don't deserve them.
0
 
Billy_PilgrimAuthor Commented:
no - you had a fine answer.  and a correct one.  i just don't like rejecting a valid answer.  if it was incorrect i wouldn't have awarded. . .

i fixed my problem by fopen()ing the file in "wt" mode, which gave me the \r\n i needed instead of just the \n at the end of each record. . .

so thank you and for people in the future needing to know how to bcp NULL columns your answer will help them. . .
0
All Courses

From novice to tech pro — start learning today.