How to insert NULL when using bcp_moretext?

How do you insert a NULL value when using bcp_moretext?
If I have a table with two 'text' or 'image' columns, and I am using bcp_mor
etext to insert a very large LOB into one of them, then BCP requires that I
use bcp_moretext for both of them ("use it for one 'text'/'image', then must
use it for all").
(In fact, I've discovered that I can bcp_bind a NULL with a non-NULL vardata
address for earlier 'text'/'image', but once I bcp_bind a NULL vardata, ind
icating my wish to use bcp_moretext, then all following 'text'/'image' colum
ns in ordinal order must al
so be bound with a NULL vardata -- i.e. once you use bcp_moretext for a 'tex
t'/'image', it's really all *following* that must be also sent with bcp_more
text. At least that's the behavior I see.)
I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB BC
P). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP te
lls it that you are sending a zero-length param. You still need to call bcp_
moretext later with length
0, for that column. Setting length to SQL_NULL_DATA will cause an error late
r, whether you try to call bcp_moretext with length SQL_NULL_DATA, with leng
th 0, or try to skip calling bcp_moretext for this column.
I don't believe it's possible to send NULL, via BCP, to a 'text' or 'image'
column, once you've sent 0 or >0 length data to any earlier (in ordinal orde
r) 'text' or 'image' column using bcp_moretext. But, I would like Microsoft
to verify this, since the d
ocumentation doesn't cover this at all.
Thanks,
Jim FloodCan I expect some sort of response from Microsoft in this group within two d
ays, as part of the benefit of my MSDN subscription?
-- Jim Flood wrote: --
How do you insert a NULL value when using bcp_moretext?
If I have a table with two 'text' or 'image' columns, and I am using bcp_mor
etext to insert a very large LOB into one of them, then BCP requires that I
use bcp_moretext for both of them ("use it for one 'text'/'image', then must
use it for all").
(In fact, I've discovered that I can bcp_bind a NULL with a non-NULL vardata
address for earlier 'text'/'image', but once I bcp_bind a NULL vardata, ind
icating my wish to use bcp_moretext, then all following 'text'/'image' colum
ns in ordinal order mu
st also be bound with a NULL vardata -- i.e. once you use bcp_moretext for a
'text'/'image', it's really all *following* that must be also sent with bcp
_moretext. At least that's the behavior I see.)
I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB BC
P). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP te
lls it that you are sending a zero-length param. You still need to call bcp_
moretext later with le
ngth 0, for that column. Setting length to SQL_NULL_DATA will cause an error
later, whether you try to call bcp_moretext with length SQL_NULL_DATA, with
length 0, or try to skip calling bcp_moretext for this column.
I don't believe it's possible to send NULL, via BCP, to a 'text' or 'image'
column, once you've sent 0 or >0 length data to any earlier (in ordinal orde
r) 'text' or 'image' column using bcp_moretext. But, I would like Microsoft
to verify this, since
the documentation doesn't cover this at all.
Thanks,
Jim Flood|||I'm checking with our BCP guy.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Jim Flood" <bezspam@.bezspam.cz> wrote in message
news:C95C8F5A-BF58-48F3-A516-AC5980947BDE@.microsoft.com...
> Can I expect some sort of response from Microsoft in this group within two
days, as part of the benefit of my MSDN subscription?
> -- Jim Flood wrote: --
> How do you insert a NULL value when using bcp_moretext?
> If I have a table with two 'text' or 'image' columns, and I am using
bcp_moretext to insert a very large LOB into one of them, then BCP requires
that I use bcp_moretext for both of them ("use it for one 'text'/'image',
then must use it for all").
> (In fact, I've discovered that I can bcp_bind a NULL with a non-NULL
vardata address for earlier 'text'/'image', but once I bcp_bind a NULL
vardata, indicating my wish to use bcp_moretext, then all following
'text'/'image' columns in ordinal order must also be bound with a NULL
vardata -- i.e. once you use bcp_moretext for a 'text'/'image', it's really
all *following* that must be also sent with bcp_moretext. At least that's
the behavior I see.)
> I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5
DB-LIB BCP). Setting the param length to zero (bcp_bind or bcp_collen) for
7.0 BCP tells it that you are sending a zero-length param. You still need to
call bcp_moretext later with length 0, for that column. Setting length to
SQL_NULL_DATA will cause an error later, whether you try to call
bcp_moretext with length SQL_NULL_DATA, with length 0, or try to skip
calling bcp_moretext for this column.
> I don't believe it's possible to send NULL, via BCP, to a 'text' or
'image' column, once you've sent 0 or >0 length data to any earlier (in
ordinal order) 'text' or 'image' column using bcp_moretext. But, I would
like Microsoft to verify this, since the documentation doesn't cover this at
all.
> Thanks,
> Jim Flood|||Hello Brannon,
Have you heard back about the BCP problem yet? Is there anything else I need
to do besides post to this group, to get the two-day response from Microsof
t? It's been well over two days.
Thanks,
Jim Flood|||Hi Jim,
I've faced the same problem. I can't insert NULLs using bcp_moretext.
Have you found a solution?
Thanks in advance,
Alberto.
"Jim Flood" <anonymous@.discussions.microsoft.com> escribi en el mensaje
news:0BB0853D-430D-4299-88D2-7343C6C024A8@.microsoft.com...
> How do you insert a NULL value when using bcp_moretext?
> If I have a table with two 'text' or 'image' columns, and I am using
bcp_moretext to insert a very large LOB into one of them, then BCP requires
that I use bcp_moretext for both of them ("use it for one 'text'/'image',
then must use it for all").
> (In fact, I've discovered that I can bcp_bind a NULL with a non-NULL
vardata address for earlier 'text'/'image', but once I bcp_bind a NULL
vardata, indicating my wish to use bcp_moretext, then all following
'text'/'image' columns in ordinal order must also be bound with a NULL
vardata -- i.e. once you use bcp_moretext for a 'text'/'image', it's really
all *following* that must be also sent with bcp_moretext. At least that's
the behavior I see.)
> I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB
BCP). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP
tells it that you are sending a zero-length param. You still need to call
bcp_moretext later with length 0, for that column. Setting length to
SQL_NULL_DATA will cause an error later, whether you try to call
bcp_moretext with length SQL_NULL_DATA, with length 0, or try to skip
calling bcp_moretext for this column.
> I don't believe it's possible to send NULL, via BCP, to a 'text' or
'image' column, once you've sent 0 or >0 length data to any earlier (in
ordinal order) 'text' or 'image' column using bcp_moretext. But, I would
like Microsoft to verify this, since the documentation doesn't cover this at
all.
> Thanks,
> Jim Flood|||Hello Alberto,
If all of your NULLs are lined up contiguous to the left in the set of colum
ns for a given row, then just avoid bcp_moretext for those columns -- althou
gh, according to the documentation, this isn't supposed to work. But, once y
ou've set bcp_moretext for
a column, going left-to-right, then all other columns to the right will have
to be bcp_moretext'ed as well, and then you can't set any of them to NULL.
So, I have no solution.
Can I please get some kind of response from Microsoft on this problem? I bel
ieve my MSDN subscription should guarantee a two-day response time, and it h
as been *far* more than two days.
Jim Flood