Friday, February 24, 2012

"Invalid Object.." error when using Copy SQL Server Objects task

SQL Server 2000. I have a production database, of which, I want to
make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
all objects from MyDbName to MyDbName_Test (on the same instance of SQL
Server). I've used this method on other databases many times and it
completes just fine. On this db, however, I get "Invalid object name
'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
course, one of my views. This view functions properly when ran. I
later found that the error seemed to be happening when the DTS attempts
to copy another view that calls vwWhereUsed. This other view does not
get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
copied.
My thought was that the other view is being copied to the new db before
vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
from what I just explained, that doesn't seem to be the case.
On another DTS attempt to move a few tables,that have nothing to do
with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
same error message (Invalid object name 'dbo.vwWhereUsed').
Interestingly enough, if I UNcheck the "Drop Destination Objects First"
option before moving those tables, the error does not occur. This does
not make sense, because it was a NEW db.. there was nothing to DROP.
Is there something I'm not seeing here? or..
Is there techniques to help me discover exactly whats happening.. or
any tips on making copies of databases using the Copy SQL Server Object
Task that may help correct this issue?
TIA
Lindsey,
Seems like a lot of unnecessary work. Why not use a BACKUP/RESTORE
approach? Or if the DB can be offline (say afterhours)
sp_detach_db/sp_attach_db?
HTH
Jerry
"lindseyhansen" <lindsey.hansen@.fmc-na.com> wrote in message
news:1128614409.695658.177570@.o13g2000cwo.googlegr oups.com...
> SQL Server 2000. I have a production database, of which, I want to
> make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
> all objects from MyDbName to MyDbName_Test (on the same instance of SQL
> Server). I've used this method on other databases many times and it
> completes just fine. On this db, however, I get "Invalid object name
> 'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
> course, one of my views. This view functions properly when ran. I
> later found that the error seemed to be happening when the DTS attempts
> to copy another view that calls vwWhereUsed. This other view does not
> get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
> copied.
> My thought was that the other view is being copied to the new db before
> vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
> from what I just explained, that doesn't seem to be the case.
> On another DTS attempt to move a few tables,that have nothing to do
> with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
> same error message (Invalid object name 'dbo.vwWhereUsed').
> Interestingly enough, if I UNcheck the "Drop Destination Objects First"
> option before moving those tables, the error does not occur. This does
> not make sense, because it was a NEW db.. there was nothing to DROP.
>
> Is there something I'm not seeing here? or..
> Is there techniques to help me discover exactly whats happening.. or
> any tips on making copies of databases using the Copy SQL Server Object
> Task that may help correct this issue?
> TIA
>
|||Thanks Jerry.. I haven't tried that before.. I'll give it a shot. It
still makes me nervous, however, that I can't get the dts to complete
successfully. I've run the dbcc check on the database and everything
is fine. I'm not sure what else to look at.
Thanks, though.

No comments:

Post a Comment