Billy_Pilgrim
asked on
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
1234567890,(null),12345678
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?
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
1234567890,(null),12345678
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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. . .
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. . .
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.
Don't award me points if I don't deserve them.
ASKER
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. . .
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. . .
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