I create a DTS package that will transfer the new records
and updated records from sql server on a web server to
our internal Access database.

Step1 is to use create a global varible

SELECT MAX(updateDate) AS glbMaxDate
FROM Results

step 2 is to delete the records in Results table.

step 3 is to transfer those records in SQL server >
parameter glbMaxDate to Access database.

I create this as a daily routine job. My question is I
found if there is no new or updated records, then no
records will be moved to Access tables, and for step 2 in
last run deleted all the records, so next time the job
runs,

SELECT MAX(updateDate) AS glbMaxDate
FROM Results

will turn to null.

when job runs to step 3,it gives error message: invalid
input parameter value
How can I solve this problme, should I use a if statement,
what is the syntax about that?

Thanks for any suggestions in advance.

Why do you grab a value from the Results table and then delete from it ?

I presume the records you move are based on the value retrieved from the
results table ?


--
--

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

"Ann" <xiaowei.cao (AT) state (DOT) mn.us> wrote

Yes,this is a scheduled daily job. I grab the value of Max
(date) in result table in Access database and save in a
global variable. Then I delete all the records in the
result table. Because I only want the new data from SQL
server, so I select all new data in SQLserver which is
Result table is a staging table. I will do some
manupilation of checking, then goes to the destination
table in the same Access database.

Am I correct on the way?

Thanks

Do you only want NEW records not Altered ?

You can either

If you import daily then you can take over

SELCT <<col list>> FROM TABLE WHERE CONVERT(CHAR(8),DateField,112) =
CONVERT(CHAR(8),Getdate()-1,112)

OR

Add a trigger to the Source table and INSERT into a staging table. At the
end of the day, week, month you can then DTS over only this table. You
clear it down after import.



--
--

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

"Ann" <xiaowei.cao (AT) state (DOT) mn.us> wrote

Yes,this is a scheduled daily job. I grab the value of Max(date) in
result table in Access database and save in a global variable. Then I
delete all the records in the result table. Because I only want the new
data from SQL server, so I select all new data in SQLserver which is
Result table is a staging table. I will do some manupilation of
checking, then goes to the destination table in the same Access
database.

Am I correct on the way? And if correct, can you help me with my first
posting questions?

Thanks in advance


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Yes,this is a scheduled daily job. I grab the value of Max(date) in
result table in Access database and save in a global variable. Then I
delete all the records in the result table. Because I only want the new
data from SQL server, so I select all new data in SQLserver which is
Result table is a staging table. I will do some manupilation of
checking, then goes to the destination table in the same Access
database.

Am I correct on the way? And if correct, can you help me to figure out
my first posting question?


Thanks in advance


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I do need both new records and altered records.
So does what you suggested apply to my situation too?

I'm new to SQL server, please help

Thanks

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Yes.

I personally would use a trigger on the Source. It will capture the
changes/additions to the table and the row values. You can then apply then
using the DDQ.

I have a package from a presentation I gave at Reading if you mail me
privately which does the kind of thing I would do in your situation.

--
--

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

"xiaowei cao" <xiaowei.cao (AT) state (DOT) mn.us> wrote

Thanks, Allan
I'm new to SQL server, and I tried to learn how to use DTS, it's good to
have some sample packages. I checked in SQl server there is only one
Demo package example.

Where can I look at more dts package samples.
You mentioned you have a presentation package example, how can I reach
you, can you send me an email with it?

my email address is xiaowei.cao (AT) state (DOT) mn.us

Thanks again

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

On it's way

--
--

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

"xiaowei cao" <xiaowei.cao (AT) state (DOT) mn.us> wrote