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

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 06/22/04

• Next message: JJ Wang: "Re: urgent help: view of partitioned tables"
• Previous message: Allan Mitchell: "Re: DTS bug (?): Data Driven Query Task removes milliseconds from DATETIME?"
• In reply to: John Peterson: "Re: DTS bug (?): Data Driven Query Task incorrectly replaces empty strings with NULL."
• Messages sorted by: [ date ] [ thread ]

Date: Tue, 22 Jun 2004 21:51:21 +0100

I have a table somwhere else I think which explains the difference between
CHAr and VARCHAR and the ANSI PADDING settings so I do not think it as easy
as saying VARCHAR(50) to CHAR(50) = 50 chars

I'll dig it out tomorrow

-- 
-- 
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:u937aQIWEHA.1152@TK2MSFTNGP09.phx.gbl...
> 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
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

• Next message: JJ Wang: "Re: urgent help: view of partitioned tables"
• Previous message: Allan Mitchell: "Re: DTS bug (?): Data Driven Query Task removes milliseconds from DATETIME?"
• In reply to: John Peterson: "Re: DTS bug (?): Data Driven Query Task incorrectly replaces empty strings with NULL."
• Messages sorted by: [ date ] [ thread ]

• Windows
• Science
• Usenet

• Archive
• About
• Privacy
• Search
• Imprint