MS SQL Server DTS - Scheduled package appears to fail when new tables are added

Author

Scheduled package appears to fail when new tables are added - why?

m00nm0nkey

2006-01-23, 7:23 am

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. tmpRateNumberDescrip
tion'.

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 !

Ryan

2006-01-23, 7:23 am

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

--
HTH. Ryan
"m00nm0nkey" < m00nm0nkey@discussio
ns.microsoft.com> wrote in message
news:830054C6-2C91-4F31-A67C- DE1FD0797482@microso
ft.com...
>
> 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. tmpRateNumberDescrip
tion'.
>
> 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 !

Joseph Geretz

2006-01-23, 1:23 pm

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@discussio
ns.microsoft.com> wrote in message
news:830054C6-2C91-4F31-A67C- DE1FD0797482@microso
ft.com...
>
> 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. tmpRateNumberDescrip
tion'.
>
> 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 !

Sponsored Links