DTS erroring on index in unicode conversion

I have undertaken the following process to convert a database to unicode support. This is sql 2000 SP4

- Create a new database dbnew
- Script the old database dbold with all objects, everything, and dependencies
- Global replace varchar with nvarchar (etc etc) in the script
- Execute the script to create all objects into dbnew
- (Objects all exist fine)
- Startup DTS and choose olddb as the source, newdb as the destination
- On DTS step 3 choose "Copy Objects and Data between Sql Server Databases"
- Untick "Create destination objects"
- Change copy data to append data (all tables in dbnew are empty)
- Tick copy all objects
- Untick "Use default options" and clear every option (so hopefully we are only copying data)
- Click next and run

DTS gets through the first "phase" to 100% but then it fails on a duplicate key error on a table that has a unique key on its (now nvarchar) description field

Yet in Query Analyser I can do "insert into failingtable select * from olddb..failingtable" and the data comes across fine.

So why is it failing in DTS ? And are there any other options or settings I can try ?

thanks

One development on this..

The tables that are getting across are showing the nvarchar data as chinese symbols (where the source db was varchar not nvarchar, so just A-Z ascii etc). So I think this problem translates to how to get DTS to copy varchar data into nvarchar fields

I have been reading this article

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp

Which implies that everything is ok copying varchar to nvarchar, not so in my case. I think possible m$ only tested their software with US collection sequence and not UK default. ? Otherwise I'm lost.

sql

DTS - 5 million row load with indexes?

When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?The table that you are loading into - is that an empty table?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegroups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegroups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
David Gugick
Quest Software
www.imceda.com
www.quest.com

DTS - 5 million row load with indexes?

When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?The table that you are loading into - is that an empty table?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegroups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegroups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

DTS - 5 million row load with indexes?

When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?
The table that you are loading into - is that an empty table?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122554831.421049.180070@.g14g2000cwa.googlegr oups.com...
When loading large quantities between servers via DTS, I'm wondering
what's best to do with a table that has an index on it. It has about
10 columns and has a Primary key on the first 2 columns. Is the index
having to do extra work as the data load progresses? Would it be
better to load the data in an unindexed table, and then reindex it
using a tsql statement?
|||Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.
|||Good. If it is an empty table, then you could keep the clustered index and
still load the data quickly using BULK INSERT command, provided the data is
coming from a text file. BUKK INSERT command allows you to specify an ORDER
parameter which indicates the order of the clustered index columns. You just
have to make sure the text file is also sorted in the same order as the
clustered index.
If you are loading from a SQL Server database table, then I suggest you load
the data into the empty table, without any indexes. After the data load
create the clustered index.
Please search Google for the following keywords. There are some good
resources out there on this topic:
optimizing data load performance sql server
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122559237.926405.177340@.g49g2000cwa.googlegr oups.com...
Yes - empty table. But after the data comes in I'll be doing some
processing and will need the indexes.
|||It is in fact coming from another SQL Server, and you're right -
loading without the index perfomed much better. Then I recreated the
indexes and this did not take very long. Text file loading sounds
pretty fast too, but I would have to unload to text file from the
source sql server then load back in, so i figured this way would be
just as fast.
|||unc27932@.yahoo.com wrote:
> It is in fact coming from another SQL Server, and you're right -
> loading without the index perfomed much better. Then I recreated the
> indexes and this did not take very long. Text file loading sounds
> pretty fast too, but I would have to unload to text file from the
> source sql server then load back in, so i figured this way would be
> just as fast.
If you're coming from another SQL Server, have you tried selecting the
data from the "other" table in the clustered index key order of the
destination table. That would likely help insert speed a lot and might
allow you to keep the clustered index in place without undue stress
during insert.
David Gugick
Quest Software
www.imceda.com
www.quest.com