Re: DTS bug (?): Data Driven Query Task incorrectly replaces empty strings with NULL.

From: John Peterson (j0hnp_at_comcast.net)
Date: 06/22/04 

Date: Tue, 22 Jun 2004 11:09:20 -0700

The problem with converting an empty VARCHAR string to a CHAR string is that the *value*
of the data changes due to the space padding inherent in the CHAR. :-(

For example, I have a VARCHAR(50) field that is exhibiting this problem. If I change the
VARCHAR(50) in my Source query and Binding table to be CHAR(50) and then use the DDQ Task
to invoke a SP that is accepting a VARCHAR(50) to put into a Destination table with a
VARCHAR(50), instead of an empty string, I get a string with 50 spaces.

NULL is actually more preferable to me than trying to convert to a CHAR. If you find out
a different workaround to this, I'd be obliged.

Thanks for your help! :-)

John Peterson

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:OuH8XzHWEHA.1888@TK2MSFTNGP11.phx.gbl...
> Yep the '' is transferred as a NULL
>
> Change the VARCHAR to a CHAR and the problem goes away.
>
> I am not sure why at the moment but it would look to be something that is
> being interpreted in the Active Script as a straight COPY --> COPY using a
> DataPump task works as expected so it is not something that DTS cannot
> handle.
>
> I will do some digging
>
> --
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
>
> "John Peterson" <j0hnp@comcast.net> wrote in message
> news:OSdXTdHWEHA.2972@TK2MSFTNGP12.phx.gbl...
> > Allan, could you try your test with a varchar(50) instead of a char(1) to
> see if it makes
> > any difference? That's what my definition is.
> >
> > Thanks!
> >
> >
> > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> > news:uUZ1VjFWEHA.556@tk2msftngp13.phx.gbl...
> > > OK in the absence of schema and sample data I created what I think is a
> > > repro and my results differ from John's
> > >
> > > CREATE TABLE DDQ_NULL_SOURCE(col1 int primary key, colNullableCharCol
> > > char(1) NULL)
> > > GO
> > > CREATE TABLE DDQ_NULL_DEST(col1 int, colNullableCharCol char(1) NULL)
> > > GO
> > > CREATE PROCEDURE InsertInto_DDQ_NULL_SOURCE_i @i int, @c char(1)
> > > AS
> > > INSERT DDQ_NULL_DEST(col1, colNullableCharCol) VALUES(@i, @c)
> > > GO
> > > INSERT DDQ_NULL_SOURCE(col1, colNullableCharCol) VALUES(1,'')
> > > GO
> > > INSERT DDQ_NULL_SOURCE(col1, colNullableCharCol) VALUES(2,NULL)
> > > GO
> > > SELECT * FROM DDQ_NULL_SOURCE
> > > GO
> > > --Results
> > > --col1 colNullableCharCol
> > > ----------- ------------------
> > > --1
> > > --2 [NULL]
> > >
> > > --After DTS
> > > --Results
> > > --SELECT * FROM DDQ_NULL_DEST
> > > --col1 colNullableCharCol
> > > ----------- ------------------
> > > --1
> > > --2 [NULL]
> > >
> > > --
> > >
> > > ----------------------------
> > >
> > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > www.SQLDTS.com - The site for all your DTS needs.
> > > I support PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > >
> > > "John Peterson" <j0hnp@comcast.net> wrote in message
> > > news:uWluvXFWEHA.3472@TK2MSFTNGP09.phx.gbl...
> > > > Thanks Mark! But I *really* need to differentiate between NULLs and
> empty
> > > strings -- I
> > > > can't have them all be NULL or all be empty strings. I'm *very*
> surprised
> > > that the Data
> > > > Driven Query Task behaves this way. It seems to propagate *other*
> NULLs
> > > correctly,
> > > > including NULL strings.
> > > >
> > > >
> > > > "mark" <mark@remove.com> wrote in message
> > > > news:c6TBc.22$Nx2.9@newsfe3-win.server.ntli.net...
> > > > >
> > > > > "John Peterson" <j0hnp@comcast.net> wrote in message
> > > > > news:OvBMir9VEHA.1012@TK2MSFTNGP09.phx.gbl...
> > > > > > (SQL Server 2000, SP3a)
> > > > > >
> > > > > > Hello all!
> > > > > >
> > > > > > I have a DTS package that uses the Data Driven Query Task (DDQT)
> to
> > > invoke
> > > > > a stored
> > > > > > procedure on the Insert action (that's the only action that's
> > > defined).
> > > > > My source table
> > > > > > has a column that I'm using as the value to one of the parameters
> to
> > > the
> > > > > stored procedure.
> > > > > > Sometimes I have explicit NULLs in this column, sometimes I have
> an
> > > empty
> > > > > string ('').
> > > > > >
> > > > > > For some reason, going through DDQT is *always* converting my
> empty
> > > > > strings to NULLs when
> > > > > > passing the value into the stored procedure. Is there any way to
> > > retain
> > > > > the original
> > > > > > value (empty string)?
> > > > > >
> > > > > > Thanks!
> > > > > >
> > > > > > John Peterson
> > > > > >
> > > > > this happened to me - didnt fix it but i was advised to change nulls
> > > using
> > > > >
> > > > > UPDATE SomeTable SET col1 = COALESCE(col1, ''), coll2 =
> COALESCE(col2,
> > > ''),
> > > > > ... WHERE col1 IS NULL OR col2 IS NULL etc using query analyzer -
> not
> > > ideal
> > > > > but an option
> > > > >
> > > > >
> > > > > mark
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: DTS bug (?): Data Driven Query Task incorrectly replaces empty strings with NULL.
    ... The problem with converting an empty VARCHAR string to a CHAR string is that the *value* ... of the data changes due to the space padding inherent in the CHAR. ... VARCHAR, instead of an empty string, I get a string with 50 spaces. ...
    (microsoft.public.sqlserver.dts)
  • Re: set char to empty
    ... You need to define what 'empty' means to you. ... test is an array of twenty char. ... empty string. ... darrell at cs dot toronto dot edu Don't send e-mail to vice.president@whitehouse.gov ...
    (comp.lang.c)
  • Re: DTS bug (?): Data Driven Query Task incorrectly replaces empty strings with NULL.
    ... CHAr and VARCHAR and the ANSI PADDING settings so I do not think it as easy ... > The problem with converting an empty VARCHAR string to a CHAR string is ... > VARCHAR, instead of an empty string, I get a string with 50 spaces. ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS bug (?): Data Driven Query Task incorrectly replaces empty strings with NULL.
    ... CHAr and VARCHAR and the ANSI PADDING settings so I do not think it as easy ... > The problem with converting an empty VARCHAR string to a CHAR string is ... > VARCHAR, instead of an empty string, I get a string with 50 spaces. ...
    (microsoft.public.sqlserver.server)
  • Re: Exporting excel to text file
    ... filenumber is just a new local variable which is going to hold the system ... >> Thank you so much for your help, the While statement and empty is ... The selection may very large as compared to what is ... >> 'Walk down each row and include it in the string if the cell is not ...
    (microsoft.public.excel.misc)