error message when trying to copy views between databases

Question

Post reply

error message when trying to copy views between databases

Stephen Craig

SSC-Addicted

Points: 464
More actions
September 11, 2006 at 7:34 am

#62042

Hi,
I am trying to copy the views that I have created in a SQL Server database into another database using DTS everything seems to go OK until right at the end where I get a message that says
"Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server"
Could anyone help?
Thanks
Steve

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Wow that's usefull.. Are the tables that the view needs created when the view tries to copy?

Is there a syntax error in the view (try to alter it and resave it on the local server).

Stephen Craig SSC-Addicted Points: 464 More actions · Answer 2

I'm trying to copy about 20 of them over - even if I just try to copy one of them I still get the message. The tables are in the new database so no problem there - no syntax error either.

thanks

SQLBill SSC Guru Points: 51440 More actions · Answer 3

As you should be aware, a view has no data. So, does the data (tables/columns) exist already in the new database? Are they correct (the same as the old version)?

Are you sure the DTS is only trying to copy the view? From the error it seems to be trying to copy the objects also. If the tables/columns/etc are being copied, is the destination database large enough for it?

-SQLBill

Stephen Craig SSC-Addicted Points: 464 More actions · Answer 4

I've managed to fix it - if I uncheck the Create destination Objects checkbox ( using the DTS) it works. I'm not entirely sure why doing this has caused it to work but thanks to both of you for your input.

David le Quesne SSCertifiable Points: 5229 More actions · Answer 5

Hi,

I get this problem as well. In my case it seems to occur where views are referencing other views or UDFs. I have a batch of about 10 views in my database which always fail to copy using DTS. I suspect that the DTS package does everything in one pass and has no check for dependency.

I have been copying the problem views across manually after the package fails. Not sure how your solution works Stephen, do the views still get created if you uncheck 'Create destination objects' ?

David

If it ain't broke, don't fix it...