bcp_moretext & NULLs

When sending two 'text' columns using BCP, and using bcp_moretext because
the first is very large, and if the second is NULL, then how do you send a
NULL value using bcp_moretext?Peter (schmidt.peter@.comcast.net) writes:
> When sending two 'text' columns using BCP, and using bcp_moretext because
> the first is very large, and if the second is NULL, then how do you send a
> NULL value using bcp_moretext?

I have never used bcp_moretext, but what happens it you set the length to 0
with bcp_collen?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

bcp, empty strings and null

Not sure if this is the right section...
I've got a problem with empty strings in a table turning into null (ascii
code 0x00) values when bcp'ed out to file:
When I bcp out of a table columns that have an empty string in them get
written to file as a null value (ascii code 0x00).
For example:
--SQL
use Sandpit_SL
create table bcpTest (Col1 varchar(10), Col2 varchar(10), Col3 varchar(10))
insert bcpTest select 'COLUMN1', '', 'COLUMN2'
And then bcp:
bcp Sandpit_SL.dbo.bcpTest out C:\bcpTest.csv -S DevDB1 -U sa -t "," -c
Produces an output file where the 2nd field contains ASCII (0x00) rather
than an empty string.
How can I make it so that the second field is an empty string? Am I missing
the point here?
Thanks
StephenSte (Ste@.discussions.microsoft.com) writes:
> I've got a problem with empty strings in a table turning into null (ascii
> code 0x00) values when bcp'ed out to file:
> When I bcp out of a table columns that have an empty string in them get
> written to file as a null value (ascii code 0x00).
> For example:
> --SQL
> use Sandpit_SL
> create table bcpTest (Col1 varchar(10), Col2 varchar(10), Col3
> varchar(10))
> insert bcpTest select 'COLUMN1', '', 'COLUMN2'
> And then bcp:
> bcp Sandpit_SL.dbo.bcpTest out C:\bcpTest.csv -S DevDB1 -U sa -t "," -c
> Produces an output file where the 2nd field contains ASCII (0x00) rather
> than an empty string.
I agree that behaviour is not what you would expect, so I submitted a
bug for it on
http://lab.msdn.microsoft.com/Produ...px?feedbackId=F
DBK50454
However, as I note in the bug report, I don't really expect a fix. I think
I can see the logic here.
If you import the file, the NUL character will become the empty string.
Now, consider the file:
COLUMN1,,COLUMN2
What do you get if you import this file? Answer: you get a NULL value in
Col2. Which neither that may be what you want.
The basic problem is that character format does not have a way to expess
NULL values. Sybase in their original design circumvented this, by
leaving out empty strings from the mix (That is, '' was the same as ' '.)
What is the consumer in the other end for this file?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> I agree that behaviour is not what you would expect, so I submitted a
> bug for it on
> [url]http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=F[/ur
l]
> DBK50454
> However, as I note in the bug report, I don't really expect a fix. I think
> I can see the logic here.
Indeed, the bug was quickly closed as "By design".
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BCP Utility with null values

Hi all,
I am using BCP to output from a table that has some empty values into a
flat file. I noticed that when I try to use BCP to upload the flat
file into an exact replica of the table in another server I get an
error due to restrictions on that table that does not allow null
values. I think what is happening is that BCP convert empty values
into null during the output process and when trying to upload the value
I get the error.
Is there a way to go around the fact that BCP is converting empty
values to NULL or should I be using a different process to upload the
data from one server into another. Also, I don't have the option to
connect to the remote server from any of them hence the reason why I am
using BCP.
Thank you very much.
Ron.Hi Ron
What options are you specifying on the BCP command? This should be ok and
you should not get conversions.
John
"Ron" wrote:
> Hi all,
> I am using BCP to output from a table that has some empty values into a
> flat file. I noticed that when I try to use BCP to upload the flat
> file into an exact replica of the table in another server I get an
> error due to restrictions on that table that does not allow null
> values. I think what is happening is that BCP convert empty values
> into null during the output process and when trying to upload the value
> I get the error.
> Is there a way to go around the fact that BCP is converting empty
> values to NULL or should I be using a different process to upload the
> data from one server into another. Also, I don't have the option to
> connect to the remote server from any of them hence the reason why I am
> using BCP.
> Thank you very much.
> Ron.
>sql

BCP Utility with null values

Hi all,
I am using BCP to output from a table that has some empty values into a
flat file. I noticed that when I try to use BCP to upload the flat
file into an exact replica of the table in another server I get an
error due to restrictions on that table that does not allow null
values. I think what is happening is that BCP convert empty values
into null during the output process and when trying to upload the value
I get the error.
Is there a way to go around the fact that BCP is converting empty
values to NULL or should I be using a different process to upload the
data from one server into another. Also, I don't have the option to
connect to the remote server from any of them hence the reason why I am
using BCP.
Thank you very much.
Ron.Hi Ron
What options are you specifying on the BCP command? This should be ok and
you should not get conversions.
John
"Ron" wrote:

> Hi all,
> I am using BCP to output from a table that has some empty values into a
> flat file. I noticed that when I try to use BCP to upload the flat
> file into an exact replica of the table in another server I get an
> error due to restrictions on that table that does not allow null
> values. I think what is happening is that BCP convert empty values
> into null during the output process and when trying to upload the value
> I get the error.
> Is there a way to go around the fact that BCP is converting empty
> values to NULL or should I be using a different process to upload the
> data from one server into another. Also, I don't have the option to
> connect to the remote server from any of them hence the reason why I am
> using BCP.
> Thank you very much.
> Ron.
>

BCP Utility with null values

Hi all,
I am using BCP to output from a table that has some empty values into a
flat file. I noticed that when I try to use BCP to upload the flat
file into an exact replica of the table in another server I get an
error due to restrictions on that table that does not allow null
values. I think what is happening is that BCP convert empty values
into null during the output process and when trying to upload the value
I get the error.
Is there a way to go around the fact that BCP is converting empty
values to NULL or should I be using a different process to upload the
data from one server into another. Also, I don't have the option to
connect to the remote server from any of them hence the reason why I am
using BCP.
Thank you very much.
Ron.
Hi Ron
What options are you specifying on the BCP command? This should be ok and
you should not get conversions.
John
"Ron" wrote:

> Hi all,
> I am using BCP to output from a table that has some empty values into a
> flat file. I noticed that when I try to use BCP to upload the flat
> file into an exact replica of the table in another server I get an
> error due to restrictions on that table that does not allow null
> values. I think what is happening is that BCP convert empty values
> into null during the output process and when trying to upload the value
> I get the error.
> Is there a way to go around the fact that BCP is converting empty
> values to NULL or should I be using a different process to upload the
> data from one server into another. Also, I don't have the option to
> connect to the remote server from any of them hence the reason why I am
> using BCP.
> Thank you very much.
> Ron.
>

BCP Temporary Tables

Hi,
I am trying to bcp data from a txt file into a temp table:
CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )
master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
I am running this within the dbtemp database. I am getting the error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name '#output'.
NULL
When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table?
Thanks
Steffan
Temporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bob Badger" <sjdavies47@.hotmail.com> wrote in message
news:1130707432.996880.194530@.g47g2000cwa.googlegr oups.com...
> Hi,
> I am trying to bcp data from a txt file into a temp table:
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
> I am running this within the dbtemp database. I am getting the error:
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
> name '#output'.
> NULL
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table?
> Thanks
> Steffan
>

BCP Temporary Tables

Hi,
I am trying to bcp data from a txt file into a temp table:
CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )
master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
I am running this within the dbtemp database. I am getting the error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid o
bject
name '#output'.
NULL
When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table'
Thanks
SteffanTemporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bob Badger" <sjdavies47@.hotmail.com> wrote in message
news:1130707432.996880.194530@.g47g2000cwa.googlegroups.com...
> Hi,
> I am trying to bcp data from a txt file into a temp table:
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
> I am running this within the dbtemp database. I am getting the error:
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid
object
> name '#output'.
> NULL
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table'
> Thanks
> Steffan
>

BCP Temporary Tables

Hi,
I am trying to bcp data from a txt file into a temp table:
CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )
master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
I am running this within the dbtemp database. I am getting the error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name '#output'.
NULL
When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table'
Thanks
SteffanTemporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bob Badger" <sjdavies47@.hotmail.com> wrote in message
news:1130707432.996880.194530@.g47g2000cwa.googlegroups.com...
> Hi,
> I am trying to bcp data from a txt file into a temp table:
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
> I am running this within the dbtemp database. I am getting the error:
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
> name '#output'.
> NULL
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table'
> Thanks
> Steffan
>

BCP out, extended chars when field is null (sometimes)

I want to "BCP out" some tables. The format is to be delimited with "\r\n"
row terminator.
Now for whatever reason, when I look at the exported text file some rows
have extended characters. The extended characters appear in fields that
happen to be null. The kicker is for the same column some rows with nulls
come through just fine, and a few rows (same column) come in with the
extended characters.
I am aware of the issue of there being no way to represent a null in the bcp
generated text data file. It is ok, if the source is a null and then it
comes back in as an empy string. I am ok with this.
In a sense, it sort of sounds like data corruption, but the field value is
null. The extended chars appear between the delimiters. So how does NULL
turn into extended characters.
Thanks.
I just ran into this too.
I created a little C# console guy that opens the text file and strips out
the null chars that were in my file.
you run it from a command line passing in the source file name and an output
file name.
FileScrubber.exe IckyFile.txt Cleanfile.txt
I've attached the cs file for the Class if you can use it great.
(i'm no C# Guru so please be kind with the review)
Greg Jackson
PDX, Oregon
begin 666 Class1.cs
M=7-I;F<@.4WES=&5M.PT*=7-I;F<@.4WES=&5M+DE/.PT*#0IN86UE<W!A8V4@.
M1FEL95-C<G5B8F5R#0I[#0H)+R\O(#QS=6UM87)Y/@.T*"2\O+R!3=6UM87)Y
M(&1E<V-R:7!T:6]N(&9O<B!#;&%S<S$N#0H)+R\O(#PO<W5M;6%R>3X-"@.EC
M;&%S<R!&:6QE4V-R=6)B97(-"@.E[#0H)"2\O+R \<W5M;6%R>3X-"@.D)+R\O
M(%1H92!M86EN(&5N=')Y('!O:6YT(&9O<B!T:&4@.87!P;&EC8 71I;VXN#0H)
M"2\O+R \+W-U;6UA<GD^#0H)"5M35$%4:')E861=#0H)"7-T871I8R!V;VED
M($UA:6XH<W1R:6YG6UT@.87)G<RD-"@.D)>PT*"0D)<W1R:6YG('-);G!U=$9I
M;&4@./2!A<F=S6S!=.PT*"0D)<W1R:6YG('-/=71P=71&:6QE(#T@.87)G<ULQ
M73L-"@.D)"6EN="!I;G1">71E.PT*"0D)8GET92!B=$)Y=&4[#0 H-"@.D)"49I
M;&53=')E86T@.<W1R;4]U='!U=" ](&YU;&P[#0H)"0E&:6QE4W1R96%M('-T
M<FU);G!U=" ](&YU;&P[#0H-"@.D)"71R>0T*"0D)>PT*"0D)"49I;&5);F9O
M(&]B:DEN1FEL92 ](&YE=R!&:6QE26YF;RAS26YP=71&:6QE*3L-"@.D)"0E&
M:6QE26YF;R!O8FI/=71&:6QE(#T@.;F5W($9I;&5);F9O*'-/=71P=71&:6QE
M*3L-"@.T*"0D)"7-T<FU);G!U=" ](&]B:DEN1FEL92Y/<&5N4F5A9"@.I.PT*
M"0D)"7-T<FU/=71P=70@./2!O8FI/=71&:6QE+D]P96Y7<FET92@.I.PT*#0H)
M"0D)9F]R*&EN="!I(#T@.,#MI/'-T<FU);G!U="Y,96YG=&@.[:2LK*0T*"0D)
M"7L-"@.D)"0D):6YT0GET92 ]('-T<FU);G!U="Y296%D0GET92@.I.PT*"0D)
M"0EB=$)Y=&4@./2 H8GET92EI;G1">71E.PT*#0H)"0D)"2\O:68@.:70@.:7,@.
M82!N=6QL(&-H87)A8W1E<BP@.=V4@.9V]T=&$@.<VMI<"!I="XN+BY'04H-"@.D)
M"0D):68H8G1">71E(#X@.,"D-"@.D)"0D)>PT*"0D)"0D)<W1R;4]U='!U="Y7
M<FET94)Y=&4H8G1">71E*3L-"@.D)"0D)?0T*"0D)"7T-"@.D)"7T-"@.D)"6-A
M=&-H*%-Y<W1E;2Y%>&-E<'1I;VX@.97AP*0T*"0D)>PT*"0D)"4-O;G-O;&4N
M5W)I=&5,:6YE*")%<G(@.(B K(&5X<"Y-97-S86=E*3L-"@.D)"7T-"@.D)"69I
M;F%L;'D-"@.D)"7L-"@.D)"0ES=')M3W5T<'5T+D-L;W-E*"D[#0H)"0D)<W1R
M;4EN<'5T+D-L;W-E*"D[#0H)"0E]#0H-"@.D)"4-O;G-O;&4N5W)I=&5,:6YE
M*")0<F]C97-S960@.26YP=70Z("(@.*R!S26YP=71&:6QE*3L-"@.D)"4-O;G-O
M;&4N5W)I=&5,:6YE*")'96YE<F%T960@.3W5T<'5T.B B("L@.<T]U='!U=$9I
M;&4I.PT*"0D)0V]N<V]L92Y7<FET94QI;F4H(B(I.PT*"0D)0V]N<V]L92Y7
D<FET94QI;F4H(D=I9&1Y(%5P(2(I.PT*"0E]#0H)?0T*?0T*
`
end

BCP help -Null values in select

I am using a bcp command to cretae a text file.

set @.bcpcommand = 'bcp "select med + replicate('' '',10-datalength(med)),ml+ replicate('' '',20-datalength(ml)),iname + replicate('' '',10-datalength(iname)) from copy_tbl" queryout "'+ @.filename + '" -U -P -c'

exec master..xp_cmdshell @.bcpCommand

Some of the values in the select statement are Null values and are getting skipped in the text file .My output looks like
At A02 At1E
AtE A03 At2E
c100 c1230
I want them to allign but the third row has a null in the middle so it skips it and put the third value in the seconds placeset @.bcpcommand = 'bcp "select COALESCE(med,'') + replicate('' '',10-datalength(COALESCE(med,''))),COALESCE(ml,'') + replicate('' '',20-datalength(COALESCE(ml,''))),COALESCE(iname,'') + replicate('' '',10-datalength(COALESCE(iname,''))) from copy_tbl" queryout "'+ @.filename + '" -U -P -c'

This will replace any NULLs with ''|||Thanks a lot. It works just great.

Thank You

Basic Syntax explanation

In T sql for sql server, what is the technical difference between the
comparisons "is" and "="
for example:
set @.test = null

print @.test is null -> true
print @.test = null -> falseIn SQL, NULL represents a missing/unknown value. The basic comparison
operators like =, <>, <, >, IN, etc return an UNKNOWN result when NULLs are
compared. IS NULL / IS NOT NULL are special types of comparison for
verifying the presence or absence of NULLs.

Read about NULLs and three-value logic in Books Online:
http://msdn.microsoft.com/libr*ary/...qd_02_8p*wy.asp

--
David Portas
SQL Server MVP
--|||Sorry. Broken link. Try:
http://msdn.microsoft.com/library/d..._qd_02_8pwy.asp

--
David Portas
SQL Server MVP
--|||Read about three valued logic in any intro SQL book. You got
everything wrong:

(@.test = NULL) -> UNKNOWN for all values of @.test

This makes a big difference in DDL and DML clauses.

There is no "IS" in SQL -- that reserved word is part of several
multi-word operators such as IS NULL, IS NOT NULL, and some that are
not widely implemented yet.

Basic select statement question

If I want to select certain values from a table where date is equal to something and the second field is null,would I go.

select * from table where date = 'something' and Sent = null or something else because if I do it this way I get nothing as a result but if I scroll down I can see that I have null values.

thanksNULL is a tricky beast, because nothing ever equals NULL, not even NULL itself. You need to modify your statement slightly to use a semantically different test, like:SELECT *
FROM table
WHERE date = 'something'
AND Sent IS nullThis seems like a trivial difference, and from a coding perspective it is. From the logical perspective however, the difference is huge.

-PatP|||Ooh,that made all the difference, I always say, it is easy once you know how to do it.
Thanks for you help once again|||Since you're dealing with this, you might want to look up ANSI_NULLS in Book Online and memorize it. :) It will save you a lot of pain down the road. Make sure you use SET ANSI_NULLS ON when creating tables and procedures.|||Will do so,thanks