Hi,

I have three related questions.


1.

I am trying to import about 30 tables from one SQL 2005 database to another.

However, whenever I do so, I get one of two errors:

ERROR : errorCode=-1073548784 description=Executing the query "TRUNCATE
TABLE [knowledge_website].[dbo].[tblCategory]
" failed with the following error: "Cannot truncate table
'knowledge_website.dbo.tblCategory' because it is being referenced by a
FOREIGN KEY constraint.".

For this error, it seems to be copying tables in the wrong order. I
remember this bug from SQL 2000 which was fixed in an early service
pack, so I don't get why this problem occurs in 2005.

How can I change the order in which the tables are copied so as not to
cause any foreign key constraint errors?


2.

If I exclude the tables that are causing the problem, I then get the
error below:

ERROR : errorCode=-1071636471 description=An OLE DB error has occurred.
Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80004005 Description: "Unicode data is odd byte size for
column 5. Should be even byte size.".

However, as usual with SQL Server error messages, it is impossible to
tell from the error message what the cause of the error is. Ie which
table or column is causing the error.

How can I find out what this error message means and why it's happening?

3.
Where is the SSIS package designer? I cannot find it anywhere or
anything that lets you edit DTS packages. In 2000 this was under "DTS
packages" and was easy to find.

Thanks,

Nick Gilbert

Nick,

SSIS can be found in the SQL Server Business Intelligense Development
Studio. It is not used within the SQL Server Management Studio (EM
replacement for 2005) as it is in 2000. To modify dts 2000 packages
you must download a DTS 2000 Designer component which can be found on
the internet with a little searching.

Sorry I have not help on the errors your receiving.


Nick Gilbert wrote:

Hi,
My understanding of your issue is that:
You had three questions, the first question was a FK constrint error when
you import data from one SQL 2005 database to another; the second question
was an Unicode data error when you perform the data transfer with excluding
the tables causing the first issue; your third question was that you wanted
to find the SSIS designer.
If I have misunderstood, please let me know.

For your first question, I would like to know:
1. Had the destination database tables created with constraints before
importing the data?
If so, you can try using ALTER TABLE... WITH NOCHECK before the import.
2. Did you use "Taskes -> Import Data..." in Microsoft SQL Server
Management Studio to import the data?
I performed a simple test which importing several AdventrureWorks
tables from another SQL Server 2005
databases to my local SQL 2005 test database without any problem.

For your second question, please check if the source table column type and
its size were equal to the destination table column type and the size. This
issue is most likely caused by the destination table column size shorter
than the source table column.

For your third question, you can use SQL Server Business Intelligence
Development Studio to design SSIS packages. SSIS is a new feature in SQL
Server 2005 which has many differences from DTS.
For more information, you can refer to Integration Services Tutorial in SQL
Server 2005 Books Online.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqltut9/html/d6d5bb1f-4cb1-4605-9cd6-f
60b858382c4.htm
Also, you may refer to:
Designing and Creating Integration Services Packages
http://msdn2.microsoft.com/en-us/library/ms141091.aspx

If this issue persists, I would like your mailing me
(changliw (AT) microsoft (DOT) com) a test database backup file and the description of
your steps so that I can reproduce your issue for further research.

If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.

Sincerely yours,
Charles Wang
Microsoft Online Community Support

================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====