DTS data turns into null

hi,
I have a sql server 2000 DTS package, that selecting
yesterday's data from one of our vendors database
(Advantage platform) into our sql server 2000 db, daily
process.
my select query is like this:
SELECT *
FROM vendor_table
WHERE (DATE = { fn CURDATE() } - 1)
Advantage don't work with 'getdate()' for some reason.
recently, we are experiencing a problem with this task:
On certain day, over 10% of the records that got pulled
over by the above filter turns into null in the date field
once got imported into our tables. some of the records'
date field will become '11/12/1926', any significants of
this date? This has happened several times now.
When I log on advantage platform and view the data, there
is no null date in the whole table, and all the date are
in the same format, so why some changed into null once it
imported over to our db?
many thanks!
JJ
I personally would identify the rows that come out "wrong" from the source
system and I would create a source of only these rows. I would then watch
them come through and try to figure out why they are coming through as they
do. Profiler might be useful to you here.
Is it always the same rows?
Allan
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:22ba601c45daf$7c144660$a401280a@.phx.gbl...
> hi,
> I have a sql server 2000 DTS package, that selecting
> yesterday's data from one of our vendors database
> (Advantage platform) into our sql server 2000 db, daily
> process.
> my select query is like this:
> SELECT *
> FROM vendor_table
> WHERE (DATE = { fn CURDATE() } - 1)
> Advantage don't work with 'getdate()' for some reason.
> recently, we are experiencing a problem with this task:
> On certain day, over 10% of the records that got pulled
> over by the above filter turns into null in the date field
> once got imported into our tables. some of the records'
> date field will become '11/12/1926', any significants of
> this date? This has happened several times now.
> When I log on advantage platform and view the data, there
> is no null date in the whole table, and all the date are
> in the same format, so why some changed into null once it
> imported over to our db?
> many thanks!
> JJ
|||I personally would identify the rows that come out "wrong" from the source
system and I would create a source of only these rows. I would then watch
them come through and try to figure out why they are coming through as they
do. Profiler might be useful to you here.
Is it always the same rows?
Allan
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:22ba601c45daf$7c144660$a401280a@.phx.gbl...
> hi,
> I have a sql server 2000 DTS package, that selecting
> yesterday's data from one of our vendors database
> (Advantage platform) into our sql server 2000 db, daily
> process.
> my select query is like this:
> SELECT *
> FROM vendor_table
> WHERE (DATE = { fn CURDATE() } - 1)
> Advantage don't work with 'getdate()' for some reason.
> recently, we are experiencing a problem with this task:
> On certain day, over 10% of the records that got pulled
> over by the above filter turns into null in the date field
> once got imported into our tables. some of the records'
> date field will become '11/12/1926', any significants of
> this date? This has happened several times now.
> When I log on advantage platform and view the data, there
> is no null date in the whole table, and all the date are
> in the same format, so why some changed into null once it
> imported over to our db?
> many thanks!
> JJ
|||Hi
I am not sure why the date is significant, it looks rather near the windows
cut off date, but that should not come into play! If you change the date so
it is formatted as a safe string i.e YYYYMMDD format then I think you should
avoid this problem.
John
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:22ba601c45daf$7c144660$a401280a@.phx.gbl...
> hi,
> I have a sql server 2000 DTS package, that selecting
> yesterday's data from one of our vendors database
> (Advantage platform) into our sql server 2000 db, daily
> process.
> my select query is like this:
> SELECT *
> FROM vendor_table
> WHERE (DATE = { fn CURDATE() } - 1)
> Advantage don't work with 'getdate()' for some reason.
> recently, we are experiencing a problem with this task:
> On certain day, over 10% of the records that got pulled
> over by the above filter turns into null in the date field
> once got imported into our tables. some of the records'
> date field will become '11/12/1926', any significants of
> this date? This has happened several times now.
> When I log on advantage platform and view the data, there
> is no null date in the whole table, and all the date are
> in the same format, so why some changed into null once it
> imported over to our db?
> many thanks!
> JJ
|||Hi
I am not sure why the date is significant, it looks rather near the windows
cut off date, but that should not come into play! If you change the date so
it is formatted as a safe string i.e YYYYMMDD format then I think you should
avoid this problem.
John
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:22ba601c45daf$7c144660$a401280a@.phx.gbl...
> hi,
> I have a sql server 2000 DTS package, that selecting
> yesterday's data from one of our vendors database
> (Advantage platform) into our sql server 2000 db, daily
> process.
> my select query is like this:
> SELECT *
> FROM vendor_table
> WHERE (DATE = { fn CURDATE() } - 1)
> Advantage don't work with 'getdate()' for some reason.
> recently, we are experiencing a problem with this task:
> On certain day, over 10% of the records that got pulled
> over by the above filter turns into null in the date field
> once got imported into our tables. some of the records'
> date field will become '11/12/1926', any significants of
> this date? This has happened several times now.
> When I log on advantage platform and view the data, there
> is no null date in the whole table, and all the date are
> in the same format, so why some changed into null once it
> imported over to our db?
> many thanks!
> JJ
|||thanks Allan. it's hard to tell whether they are always
the same rows since there is no primary keys in this
table! I will take your good advise and watch it through
the profiler. what kind of filter should I do in
profiler? (I don't want to kill the system).
JJ
>--Original Message--
>I personally would identify the rows that come
out "wrong" from the source
>system and I would create a source of only these rows. I
would then watch
>them come through and try to figure out why they are
coming through as they
>do. Profiler might be useful to you here.
>Is it always the same rows?
>Allan
>
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:22ba601c45daf$7c144660$a401280a@.phx.gbl...
field[vbcol=seagreen]
there[vbcol=seagreen]
it
>
>.
>
|||thanks for the good tip, I will keep it in mind.
JJ
>--Original Message--
>Hi
>I am not sure why the date is significant, it looks
rather near the windows
>cut off date, but that should not come into play! If you
change the date so
>it is formatted as a safe string i.e YYYYMMDD format then
I think you should
>avoid this problem.
>John
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:22ba601c45daf$7c144660$a401280a@.phx.gbl...
field[vbcol=seagreen]
there[vbcol=seagreen]
it
>
>.
>
|||Once you have your "Bad" rowset you can simply watch the TextData
column to see how it is interpreted.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
|||thanks Allan!
JJ
>--Original Message--
>Once you have your "Bad" rowset you can simply watch the
TextData
>column to see how it is interpreted.
>
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>.
>