SQL DTS Database Copy Fails

Question

Hey All, I have been working on this problem for a while and the usual google searches are not helping :(

I have a production database in SQL 2000. I want to copy it over the top of a training database to refresh it. I want this to be something that is scheduled to happen once a week to keep the training database up-to-date.

I have a DTS job created for doing this. Within that DTS job I have a single "Copy SQL Server Objects" task. That task is set up to:

• Create all copied objects
  • Drop destination objects first
• Copy data
  • Replace existing data
• Copy indexes, triggers, primary and foreign keys
• Copy all user tables, views, functions and stored procedures.

When I run this DTS package (in pre-production for testing of course) it gets to 99% done and throws the following error:

Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 42S02)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.vwEstAssetStationAddress'.
Step Error code: 800400D0
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:1131

My searches on the net didn't provide much help. There are reports of these errors getting hit, but none seem to match my circumstances. One suggestion I found was the the sysdepends table had become corrupted, making the DTS job run its scripts in the wrong order. Howeever, I ran the following script to correct that table and it still throws the same error:

USE master
GO
ALTER DATABASE [DATABASE NAME]
SET SINGLE_USER
GO

USE [DATABASE NAME]
GO

DBCC CHECKTABLE('sysdepends',REPAIR_REBUILD )
GO

USE master
GO
ALTER DATABASE [DATABASE NAME]
SET MULTI_USER
GO

I have also seen that having different object owners can cause this error. But I have confirmed that the objects are all owned by the dbo user in this case.

Any suggestions?

Answer 1

Somehow the dbo.vwEstAssetStationAddress table is not being found by your DTS package. Unfortunately, the message doesn't say if it was on the source or destination that it couldn't find it.

What are the exact steps, in the order that you have them in your DTS package? I'm assuming that the list of the task items above is not in order. I know this not an answer, but it looks like we are going to need a bit more information to help you further.

Answer 2

Thanks for the response hectorsosajr.

the object aparrently causing the error (dbo.vwEstAssetStationAddress) is a view that references 2 underlying tables. I have tested querying the view, as well as running the SELECT statement that defines it, on both the source and destination databases and it works fine.

The database object copy task in DTS doesn't allow you to specify the order it transfers things in. As far as I understand it, it uses the sysdepends table to determine the requisite order of events.

Answer 3

Sounds like it is trying to create a stored procedure/view based on a view that doesn't yet exist.

Why not just backup and restore the database under a different name? (if it wasn't production, I would say detach, copy and re-attach). You can do all that under the control of T-SQL.

See if this link helps you find your dependency issue.

Answer 4

I was trying to avoid doing it via backup / restore. There are some users of the database that are SQL Server accounts (not Active Directory). This becomes a pain in the but if you need to do it from one server to another as you have to drop those users and recreate them.

Answer 5

I've run another test to try and isolate this. I removed the mentioned view from the destination database totally, then ran the DTS again. It failed with the same error. However, the view that aparrently is an invalid object name was recreated successfully. It seems that the error is coming from something trying to reference that view, but it doesn't actually stop the script when it hits that error.

Cade - I will check out that link. I will also try and establish what is referencing the view and breaking.

Answer 6

I feel stupid, but am posting the answer I just found for prosperity (and so all you helpful fellows can stop stressing on my behalf.

Even though I had selected all the user tables, views, stored procedures and user defined functions to copy, I hadn't selected "Include all dependant objects". I had assumed that if you selected two objects to copy, and one was dependant on the other, SQL would always do them in the correct order. Aparrently not. Selecting this little check box made all the difference.

Thanks again to those who helped with suggestions :)