June 28, 2006 at 1:13 pm
Hi I am trying to figure out if Im at risk losing data when I bcp OUT and then BULK insert between physical servers. My concern is the codepage and or collation.(is this the same thing? does the codepage determine the collation?
Any way I understand the -N flag on bcp give the best performance. But when I bulk insert with the 'native' datafiletype I get errors indicative of a datafile type mismatch.
For example this one workds ---
EXEC master.dbo.xp_cmdshell "BCP db.dbo.table OUT C:\bcpf\table1.dat -e C:\bcpf\table1.err -S OHJOHN -T -m 10 -w"
BULK INSERT staging.dbo.qb_account FROM 'C:\bcpf\table1.dat'
WITH (
DATAFILETYPE = 'widechar',
--CODEPAGE = 'OEM',
TABLOCK
)
now change the OUT and INSERT flags to native unicode (supposedly the fastest) it breaks
EXEC master.dbo.xp_cmdshell "BCP db.dbo.table OUT C:\bcpf\table1.dat -e C:\bcpf\table1.err -S OHJOHN -T -m 10 -N"
BULK INSERT staging.dbo.qb_account FROM 'C:\bcpf\table1.dat'
WITH (
DATAFILETYPE = 'native',
--CODEPAGE = 'OEM',
TABLOCK
)
now i try a codepage and it works....
EXEC master.dbo.xp_cmdshell "BCP db.dbo.table OUT C:\bcpf\table1.dat -e C:\bcpf\table1.err -SOHJOHN -T -m 10 -c -C850"
BULK INSERT staging.dbo.qb_account FROM 'C:\bcpf\table1.dat'
WITH (
CODEPAGE = '850',
TABLOCK
)
How do I find the code page i should use between the two servers? If the codepages are different between the two servers can I use native datafile types?
thanks if you can shine some light on this for me
J
SQL 2012 Standard VPS Windows 2012 Server Standard
June 29, 2006 at 10:32 am
DATAFILETYPE = 'widenative'
that was the problem.
SQL 2012 Standard VPS Windows 2012 Server Standard
June 29, 2006 at 10:33 am
DATAFILETYPE = 'widenative'
that was the problem.
SQL 2012 Standard VPS Windows 2012 Server Standard
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply