BCP Newbie - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

BCP Newbie

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-30 : 09:32:56

I'm having some trouble getting the BULK INSERT side of things working. (SQL2000 to SQL2000)
I've managed to do BULK INSERTs before but this time I started getting the "Column is too long in the data file... Make sure the field terminator and row terminator are specified correctly" error message.
I never use to provide the terminator. I just left it at the default. But because I got the error I thought I'd explicitly make one up. But I still have the error.

Here's the code:
----------------

--BCP to file:
exec master..xp_cmdshell 'del \\MyServer\DBs\His.txt'

declare @SQL varchar(300)
Set @SQL='
exec master..xp_cmdshell ''bcp "SELECT * FROM Staging.dbo.vw_TriHistory " queryout C:\DBs\His.txt -t¦¦\t -r¦¦\n -n -Usa -PmyPWord'''
Exec(@SQL)

GO

AND THEN

BULK INSERT EventHistory
FROM '\\MyServer\DBs\his.txt'
WITH
(
DATAFILETYPE = 'native',
FIELDTERMINATOR = '¦¦\t',
ROWTERMINATOR = '¦¦\n'
)

Can anyone give me any direction?

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-30 : 11:48:10

When you BCP the data out to the text file, try leaving out the "-n" switch, for the native datatype. I've always had trouble when using that. I think it's better to use nothing, which defaults to "-c" (character).

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-30 : 12:24:42

tried that. same problem.
any other ideas?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2005-06-30 : 12:57:02

The probelm looks like a uniqueidentifier field at least I have had the same issue
try this to recreate it

use Northwind

CREATE TABLE [t1] (
[id] uniqueidentifier ROWGUIDCOL NULL CONSTRAINT [DF_t1_id] DEFAULT (newid()),
[data] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [t2] (
[id] uniqueidentifier ROWGUIDCOL NULL CONSTRAINT [DF_t2_id] DEFAULT (newid()),
[data] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

insert into t1 (data)
select 'a'
union select 'b'
union select 'c'
union select 'd'
union select 'e'
union select 'f'
union select 'g'
union select 'h'
union select 'i'
go

select * from t1
go

--Export the data
exec master..xp_cmdshell 'bcp "Northwind..t1 " out C:\t1.txt -t¦¦\t -r¦¦\n -n -T '

--Show failure
BULK INSERT Northwind.dbo.t2
FROM 'C:\t1.txt'
WITH
(
DATAFILETYPE = 'native',
FIELDTERMINATOR = '¦¦\t',
ROWTERMINATOR = '¦¦\n'
)

--Export the data again explicity declaring char type
exec master..xp_cmdshell 'bcp "Northwind..t1 " out C:\t1.txt -t¦¦\t -r¦¦\n -c -T '

--Insert data to the 2nd table
BULK INSERT Northwind.dbo.t2
FROM 'C:\t1.txt'
WITH
(
FIELDTERMINATOR = '¦¦\t',
ROWTERMINATOR = '¦¦\n'
)
go

--Check the import
select t1.id, t1.data, t2.data
from t1, t2
where t1.id = t2.id

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-30 : 14:29:21

Also, if this is a text file you're generating yourself, I'd prefer to not designate the column and row delimiters and just use the defaults (tab and return) that the "-c" switch uses. If you are still getting a "column is too long" error, then I'd say check your source table DDL vs. destination table DDL and make sure they match up. Otherwise, I would create a dummy table with very wide column definitions (and all of them character types) to make sure that the data will get inserted. Then I could query the data to see where the problem is occurring.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-07-01 : 04:18:44

Thanks jhocutt, that worked great. also learend something else I didnt know. I presumed stupidly that Bulk Insert purges the table 1st. But this is ofcourse not the case.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources