August 13, 2001 at 5:21 am
I am looking to export some data from SQL 6.5 for importing into SQL 2000.
This works OK using Character mode, but I want to use Native format for speed.
I have read through MSDN on this topic and found that I should be looking at the -V6.5 option.
I am confused, however, on whether I should use this on the Export, the import (difficult as I want to use BULK INSERT) or both.
Microsoft do not give any examples and I cannot find any further details of people using it.
Does anyone have any experience of doing this - examples please!
Many thanks,
Nigel.
August 13, 2001 at 10:49 am
Use it on both.
export from v6.5 using it IF you are using the v2000 BCP program. You could also use the v6.5 program (on the v6.5 server) and then only import using the v2000 version.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 13, 2001 at 10:54 am
I read your posts in the other forum related to this topic. Have you checked out snapshot replication?
Sean
August 14, 2001 at 10:06 am
I need a solution to a similar problem in trying to replicate between 2000 and 6.5. i asked a consultant what is the best way to synchronize the databases, i thought it would be to use bcp, but he mentioned the snapshot feature in 2000.how does this work?and is it the best solution to synchronize a 6.5 db with 2000.
August 14, 2001 at 6:31 pm
The snapshot replication method basically scripts out all of your chosen database objects on the source server, creates the objects on the destination server, then BCP's the data out and over to the newly created tables. Is it the BEST way to sync databases? It depends on your needs. You can do DTS as well. There are a few tools on the market that let you do the same thing, with added control. Check aout Andy warren's review of SQL Compare at http://qa.sqlservercentral.com/columnists/awarren/sqlcomparereview.asp.
Sean
August 16, 2001 at 9:37 am
Sean / Mike
To report back, I have someone investigating using Replication to do this at the moment.
I took a look at SQLCompare - looked great for the job, then found that it doesn't support SQL 6.5.
Will keep you posted on how we get on.
August 16, 2001 at 7:50 pm
Thanks for the feedback. It's important that we get the final outcome to these topics. Other readers find a lot of vlaue in knowing "the rest of the story".
Sean
August 20, 2001 at 9:37 am
i also herd that Sql Compare does not work between 2000 and 6.5. the reason given that page sizes are differrent.
August 29, 2001 at 8:01 am
Okay, back again (I have someone else working on this right now).
We are using the -V switch on both the export and import using bcp as suggested.
I would prefer to use the BULK INSERT statement instead but this does not support the switch. Is it possible to load native files from previous versions using BULK INSERT? Perhaps another option to specify (using the Clutch event of the Straw object here)
September 6, 2001 at 9:00 am
Reporing back... again.
I passed this job onto another developer and they have not been able to get a successful transfer going in Native mode.
Firstly, can we confirm that it is not possible to use BULK INSERT using Native Mode? If it's possible, examples please!
We are using SQL 2000 bcp on both ends (required for export from 6.5 to do it in order, avoiding use of a view).
Things work OK for character fields, dates and numbers screw up e.g.
master..xp_cmdshell 'BCP reporting_Dev.dbo.Billing_Transaction IN
> \\BIG-SERVER\F$\BIGImport\Billing_Transaction.txt -E -V65
> -b50000 -n -S"BIG-SERVER" -T -h "ORDER (tr_number ASC ) , TABLOCK"'
>
> output
> ------
> NULL
> Starting copy...
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
> SQLState = 22003, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
> SQLState = 22003, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
> SQLState = 22003, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
> SQLState = 22003, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
> SQLState = 23000, NativeError = 515
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the
> value NULL into column 'TR_AMOUNT', table
> 'Reporting_Dev.dbo.BILLING_TRANSACTION'; column does not allow
> nulls. INSERT
> fails.
> SQLState = 01000, NativeError = 3621
> Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
> been terminated.
> NULL
> BCP copy in failed
> NULL
Any suggestions / examples welcomed.
September 6, 2001 at 11:07 am
Couple things:
BULK INSERT does work for native mode:
Run this on a server:
bcp Northwind.dbo.Customers out c:\NWTest.txt -n -T
Then create a copy of the Customers table on another server (or same). Should be 0 rows.
Then run this:
BULK INSERT Northwind.dbo.[NewCustomers]
FROM 'c:\NWTest.txt'
WITH
(
Datafiletype = 'Native'
)
It works. Tested on SQL 2000 and SQL 7. Export was from SQL 2000, import on both servers.
I don't have a v6.5 server, but I suspect that the version of export from v6.5 in native mode may not be the same.
If I understand correctly, you should be:
1. Running SQL2000 bcp to export data from v6.5 server. I'd run this with minimum options. Can you run this without the -V65 option?
2. Run BULK INSERT (SQL 2000) on the new server to import the data. This is failing.
3. Run bcp (SQL 2000) to import data into SQL 2000. This works or not?
Have you verified that the tables are EXACTLY the same? No changes, no columns in different order? Code pages the same? collations?
Can you include the DDL for both 6.5 and 2000 along with the exact commands the work and do not work (native and char).
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 7, 2001 at 10:15 am
Steve,
Thanks for the reply. The main problem seems to be with the export from v6.5 in native mode. He has tried specifying the Code Page directly to no avail.
I have asked him to mail on the full details, as it's a bit lengthy.
Cheers,
Nigel.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply