March 11, 2002 at 10:33 am
Hi,
I need to change the sort order on a SQL7 installation from Binary to DOCI.
I've got a script that generates the BCP commands for outputting the data then reading back it into the table after rebuilding the master and user databases.
However initial investigation shows that when reloading the data in DOCI that some primary and unique keys will have duplicate values. There can be multiple unique key indexes on each table.
Can anyone point me to a script or utility that I can execute against the data before exporting the data to identify records in each table that are okay in binary sort order but would be duplicates in DOCI?
Thanks in advance.
March 11, 2002 at 8:03 pm
If you are using BCP try the following command line switches.
/m maxerrors = Maximum number of errors that can occur before the BCP operation stops. Each failed insert counts as 1. The default value is 10 with a maximum value of 65535 (anything higher is ignored).
and
/e errfile = The name of the file to store BCP error messages and unsuccessfully transferred rows. A path can be included with this statement, such as "c:\errorfiles\authors.err". This can be very useful in finding BCP errors, especially during unattended operations like nightly data imports.
and possibly
/o output file = File to redirect BCP output (optional).
Tip: Use the /o switch to log BCP output during unattended BCP operation. This creates a useful trail of BCP output that can be used to monitor and diagnose BCP performance and execution.
As far as anything else I am not aware of anything. Also depending how many items you have you can use
/F firstrow = The number of the row to start copying at.
/L lastrow = The number of the last row to stop copying at.
To break it into batches.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 11, 2002 at 8:27 pm
Hi Antares,
Thanks for the bcp /e switch.
However I was thinking more in the line of using sp_MShelpindex or sp_helpindex to extract the fields that make up each index on each table. If it is a varchar or char field, construct a select statement forcing all char field to upper (accented characters are not used in the data) and grouping by the fields of the index. Remedial action could then be taken on the identified duplicate records in the application before exporting via bcp.
Sample statement
select index1col1char, index1col2int, index1col3decimal
from table1
group by upper(index1col1char), indexcol2int, indexcol3decimal
having count(*) > 1
similar statement for all other unique key indexes on this table, and all other tables.
Example
Create table Credit_cards
( CardId Char(10) not null,
CardName Char(50) not null
)
ALTER TABLE [Credit_Cards] WITH NOCHECK ADD
CONSTRAINT [PK_Credit_Cards] PRIMARY KEY ( [CardId] )
In Binary, may have values
Visa, Visa Card
VISA, Visa Card
In loading into DOCI database this would have second occurrence rejected.
I want to find these type records before changing the sort order on the installation.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply