We have a scheduled DTS package which transfers a database (tables, stored
procedures, views, functions, etc) on a nightly basis.

We specifically choose the following options:
Create Destination Objects - checked
Replace Existing Data - checked
Include Extended Objects - checked
Use Collation - unchecked
Copy All Objects - checked
Use default options - unchecked and "copy database users" and "copy object
level permissions" both unchecked

But I have noticed in recent days that adding new tables to the source
database is causing the database to fail.

Why should this be the case when I am telling the DTS package to
specifically create destination objects and replace the existing data.

On my latest attempt to copy all data across using this package, i get this
error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'dbo.tmpRateNumberDescription'.

Why can't it copy this table across? I'm telling it to recreate all tables
and replace any existing data, so I don't understand how this error can occur.

I do really want to use the "copy all objects" option, so that if we add new
tables to the source database, the destination database will always mirror
the source database.

Please advise!

--
welcome to the mooon !

As you want a copy of all objects a different approach would be to BACKUP /
RESTORE


--
HTH. Ryan
"m00nm0nkey" <m00nm0nkey (AT) discussions (DOT) microsoft.com> wrote

We're playing around with the same issue ourselves. Initial testing
indicates that a re-initialization of the target database results in a clean
DTS transfer, whereas DTS between mismatched databases can result in
problems of the sort you describe.

Dan Guzman [SQL Server MVP] provides a comprehensive database
re-initialization script at:

http://tinyurl.com/9t9m6

We include this script in an Execute SQL Task to run prior to the Database
Objects Transfer Task. We're testing this now, but it seems to be working.
Give this a shot and let us know whether this corrects your problem as well.

Hope this helps,

- Joe Geretz -

"m00nm0nkey" <m00nm0nkey (AT) discussions (DOT) microsoft.com> wrote