Can't copy objects with DTS

I'm trying to copy everything from one database to another using DTS.
I'm connecting using
SQL SERver authentication
and I choose
Copy objects and data between SQL Server databases
then

Create destination objects
Drop destination objects first
Include all dependant objects
Include extended properties

Copy data
Replace existing data

Copy all objects
Use default options

Script file directory
Run immediately

Then I get the following errors.
Copy SQL

Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server

In the progress bar it hangs at 22%
with
Error occurred
Need to run the object to perform this operation

[SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION

I got this problem trying to copy to a remote server so I tried it locally,
but its the same.

What am I doing wrong?

If I use Copy table and views from the source database it succeeds but None
of the tables have keys or defaults and none of the stored procedures or user
defined functionsare copied.
Ive got 200 tables and over 1000 strored procedures so - what can I do?
--
Thanks

Grant Ord

It would be better to backup database on one server and restore it on the
other server. Objects in the database have dependencies upon each other and
DTS might have trouble picking up the dependencies and creating objects in
the specific order.

If you still want to do it through Export/Import or DTS - make sure you
transfer the logins first and then - that the user that you are running your
DTS under has appropriate access to all the objects on both servers.

Regards,
Ilona Shulman
Senior Development Consultant, DBA
SSE Inc
http://www.sseinc.com

"Grant Ord" <grant.ord (AT) nospam (DOT) nospam> wrote

Hi Grant,

Thanks for your post.

First of all, I would like to show my gratitude for Ilona Shulman's great
answer and workaround.

From your descriptions, I understood that you encounter difficulty
EXCEPTION_ACCESS_VIOLATION when trying transfering stored procedures. Have
I understood you? Correct me if I was wrong.

Based on my scope, let's see the description about transfering objects with
DTS from BooksOnline first
==============================
DTS Basics

With DTS, you can transfer indexes, views, logins, stored procedures,
triggers, rules, defaults, constraints, and user-defined data types in
addition to the data. In addition, you can generate the scripts to copy the
database objects.

Note There are restrictions on this capability. For more information, see
Copy SQL Server Objects Task.
http://msdn.microsoft.com/library/de...us/dtssql/dts_
elemtsk2_27xn.asp
==============================

I am not sure what caused that EXCEPTION_ACCESS_VIOLATION and here are some
questions for us collecting more information about this issue
1. What's the operating system and SQL Server 2000 version? Have you
upgraded all of them to the latest patches?

2. Do you have BLOG datatype in your tables? Would you please show me a
description of your table structures? I would love to try reporducing it on
my machine.

At last, I think generating the scripts to copy the database objects is a
good workaround and Ilona Shulman's idea about Backup & Restore is also
great.

Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---------------------------------------------------------------
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Hi Grant,

I am currently standing by for an update from you and would like to know
how things are going there. Should you have any questions or concerns on
the recent questions/ information I've posted, please don't hesitate to let
me know directly. It's my pleasure to be of assistance!

Mingqing Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---------------------------------------------------------------
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Hi,

Did you resolve this?

I am also experiencing problems when trying to export data from SQL
Server 2000 to an Oracle 9i.

SQL Server: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) - Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
Oracle9i Database version 9.2.0.1

Most of the tables are transferring fine (created and populated with
data), but there are a few that is causing an exception access violation
every time I run it. Not only when I run the package, but also when I
try to export only this table.

The Scenario:
1. Create table in Oracle Schema using DTS ( I have also tried to create
the table in Oracle first and only transfer data)
2. Transfer data from SQL Server to Oracle.

There is one table I am experiencing problems with. There is no problem
creating the table on the oracle database, but it fails when DTS is
trying to transfer data. I then get the error message:
“Need to run the object to perform this operation Code execution
exception: EXCEPTION_ACCESS_VIOLATION” Please find table script below.

There have been some about a workaround if the script contains a
WITHEVENT – but I have saved the package as VB Script and it does not
contain any WITHEVENTS, so the workaround does not apply.


The user on Oracle is both role DBA and system SYSDBA.

Any Suggestions???

Many Thanks, Nina


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I'm having the same problem. I noticed you asked the other person if
they had a BLOG field. I have an image field in one of my tables, at
that appears to be the table the error is occurring on. Is this a known
issue with image fields?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!