DTS bug (?): Data Driven Query Task incorrectly replaces empty strings with NULL. -- sql server dts

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

John Peterson
6/21/2004 2:58:08 PM

(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

John Peterson
6/22/2004 5:38:53 AM

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.

[quoted text, click to view]

John Peterson
6/22/2004 9:37:52 AM

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!

[quoted text, click to view]

mark
6/22/2004 9:42:56 AM

[quoted text, click to view]
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

John Peterson
6/22/2004 10:11:10 AM

Allan, I did *exactly* as you did, and was able to reproduce the error. Here's the
schema/sample data I used:

use tempdb
go

create table [dbo].[Test_Source]
(
[Id] int not NULL,
[Field] varchar(255) NULL
)
go

insert into [dbo].[Test_Source] select 1, ''
insert into [dbo].[Test_Source] select 2, NULL
go

create table [dbo].[Test_Destination]
(
[Id] int not NULL,
[Field] varchar(255) NULL
)
go

create procedure [dbo].[Test_Destination_Insert]
@_Id int,
@_Field varchar(255) = NULL
as
insert into [dbo].[Test_Destination]([Id], [Field]) select @_Id, @_Field
go

-- Invoke a DTS package using DDQ Task.

select * from [dbo].[Test_Source]
select * from [dbo].[Test_Destination]
go

And I show Source as having 1/empty string and 2/NULL. My Destination table has 1/NULL
and 2/NULL.

Additional thoughts?

[quoted text, click to view]

John Peterson
6/22/2004 11:09:20 AM

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

[quoted text, click to view]

Allan Mitchell
6/22/2004 1:59:47 PM

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

[quoted text, click to view]

Allan Mitchell
6/22/2004 6:23:41 PM

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

[quoted text, click to view]

Allan Mitchell
6/22/2004 9:51:21 PM

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server dts

View Other Months
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007

home · blog · groups

Questions? Comments? Contact the dn