bulk insert

I create format file with DTS then I execute the bulk insert statement:
BULK INSERT tableTest FROM 'c:\testData.txt'
WITH (FORMATFILE = 'c:\testFormat.fmt')
but I get error:
string or binary data would be truncated
Why? What could be the reason?
Format file seems to be ok.
Regards,SSimon,
You receive an error like this if the destination column is smaller than the
value you are trying to INSERT.
HTH
Jerry
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:etBsrYoyFHA.3180@.TK2MSFTNGP14.phx.gbl...
>I create format file with DTS then I execute the bulk insert statement:
> BULK INSERT tableTest FROM 'c:\testData.txt'
> WITH (FORMATFILE = 'c:\testFormat.fmt')
> but I get error:
> string or binary data would be truncated
> Why? What could be the reason?
> Format file seems to be ok.
> Regards,S
>|||You get a "string or binary data would be truncated" when the data you are
trying to insert in a column is longer than the maximum length allowed in
that column. You can compare the format file with the table definition to
see if that is the case. If it seems to be ok, but you still get the error,
please post both the table definition and the format file.
Jacco Schalkwijk
SQL Server MVP
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:etBsrYoyFHA.3180@.TK2MSFTNGP14.phx.gbl...
>I create format file with DTS then I execute the bulk insert statement:
> BULK INSERT tableTest FROM 'c:\testData.txt'
> WITH (FORMATFILE = 'c:\testFormat.fmt')
> but I get error:
> string or binary data would be truncated
> Why? What could be the reason?
> Format file seems to be ok.
> Regards,S
>|||Check the data itself. One of the fields is too long on one of the lines
causing an overflow. Try importing with the Import/Export tool and you shoul
d
find the culprit rather quickly.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"simon" wrote:

> I create format file with DTS then I execute the bulk insert statement:
> BULK INSERT tableTest FROM 'c:\testData.txt'
> WITH (FORMATFILE = 'c:\testFormat.fmt')
> but I get error:
> string or binary data would be truncated
> Why? What could be the reason?
> Format file seems to be ok.
> Regards,S
>
>|||everything seems OK to me.
FORMAT FILE:
7.0
38
1 SQLCHAR 0 10 "," 1 column1
2 SQLCHAR 0 4 "," 2 column2
3 SQLCHAR 0 10 "," 3 column3
4 SQLCHAR 0 100 "," 4 column4
5 SQLCHAR 0 8 "," 5 column5
6 SQLCHAR 0 8 "," 6 column6
7 SQLCHAR 0 8 "," 7 column7
8 SQLCHAR 0 8 "," 8 column8
9 SQLCHAR 0 8 "," 9 column9
10 SQLCHAR 0 8 "," 10 column10
11 SQLCHAR 0 2 "," 11 column11
12 SQLCHAR 0 2 "," 12 column12
13 SQLCHAR 0 4 "," 13 column13
14 SQLCHAR 0 2 "," 14 column14
15 SQLCHAR 0 8 "," 15 column15
16 SQLCHAR 0 2 "," 16 column16
17 SQLCHAR 0 10 "," 17 column17
18 SQLCHAR 0 2 "," 18 column18
19 SQLCHAR 0 10 "," 19 column19
20 SQLCHAR 0 2 "," 20 column20
21 SQLCHAR 0 100 "," 21 column21
22 SQLCHAR 0 16 "," 22 column22
23 SQLCHAR 0 100 "," 23 column23
24 SQLCHAR 0 16 "," 24 column24
25 SQLCHAR 0 2 "," 25 column25
26 SQLCHAR 0 16 "," 26 column26
27 SQLCHAR 0 2 "," 27 column27
28 SQLCHAR 0 16 "," 28 column28
29 SQLCHAR 0 2 "," 29 column29
30 SQLCHAR 0 16 "," 30 column30
31 SQLCHAR 0 20 "," 31 column31
32 SQLCHAR 0 2 "," 32 column32
33 SQLCHAR 0 8 "," 33 column33
34 SQLCHAR 0 8 "," 34 column34
35 SQLCHAR 0 8 "," 35 column35
36 SQLCHAR 0 2 "," 36 column36
37 SQLCHAR 0 8 "," 37 column37
38 SQLCHAR 0 8 "\r\n" 38 column38
table definition:
CREATE TABLE [T_TABLE] (
[column1] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[column2] [int] NOT NULL ,
[column3] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[column4] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
[column5] [float] NOT NULL CONSTRAINT [DF_T_column5] DEFAULT (0),
[column6] [float] NULL CONSTRAINT [DF_T_column6] DEFAULT (0),
[column7] [float] NOT NULL CONSTRAINT [DF_T_column7] DEFAULT (0),
[column8] [float] NOT NULL CONSTRAINT [DF_T_column8] DEFAULT (0),
[column9] [float] NULL CONSTRAINT [DF_T_column9] DEFAULT (0),
[column10] [float] NOT NULL CONSTRAINT [DF_T_column10] DEFAULT (0),
[column11] [smallint] NULL CONSTRAINT [DF_T_column11] DEFAULT (0),
[column12] [Boolean] NOT NULL ,
[column13] [int] NULL ,
[column14] [smallint] NULL CONSTRAINT [DF_T_column14] DEFAULT (0),
[column15] [float] NULL CONSTRAINT [DF_T_column15] DEFAULT (0),
[column16] [Boolean] NOT NULL ,
[column17] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[column18] [Boolean] NOT NULL ,
[column19] [varchar] (10) COLLATE Slovenian_CI_AS NULL ,
[column20] [smallint] NULL CONSTRAINT [DF_T_column20] DEFAULT (0),
[column21] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
[column22] [smalldatetime] NULL ,
[column23] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
[column24] [smalldatetime] NULL ,
[column25] [Boolean] NOT NULL ,
[column26] [smalldatetime] NULL CONSTRAINT [DF_T_column26] DEFAULT
(getdate()),
[column27] [varchar] (2) COLLATE Slovenian_CI_AS NOT NULL CONSTRAINT
[DF__T_column27] DEFAULT ('**'),
[column28] [smalldatetime] NOT NULL CONSTRAINT [DF__T_column28] DEFAULT
(getdate()),
[column29] [varchar] (2) COLLATE Slovenian_CI_AS NOT NULL CONSTRAINT
[DF__T_column29] DEFAULT ('**'),
[column30] [smalldatetime] NOT NULL CONSTRAINT [DF_T_column30] DEFAULT
(getdate()),
[column31] [varchar] (20) COLLATE Slovenian_CI_AS NULL ,
[column32] [Boolean] NOT NULL CONSTRAINT [DF_T_column32] DEFAULT (0),
[column33] [float] NULL CONSTRAINT [DF_T_column33] DEFAULT (0),
[column34] [timestamp] NULL ,
[column35] [varbinary] (8) NULL ,
[column36] [char] (2) COLLATE Slovenian_CI_AS NULL ,
[column37] [float] NULL CONSTRAINT [DF_T_column37] DEFAULT (0),
[column38] [float] NULL CONSTRAINT [DF_T_column38] DEFAULT (0),
CONSTRAINT [PK_T_TABLE] PRIMARY KEY CLUSTERED
(
[column1],
[column2]
) ON [PRIMARY]
) ON [PRIMARY]
I just change column names.
And the data file was exported directly from Enterprise manager. One line
looks like:
"_000000281",1156055,"I0121","product",-1,0,39960,0,0,-39960,0,0,0,0,0,0,"AV
",0,"",0,"",,"",,0,2003-01-23
20:50:00,"NS",2000-01-02 09:25:00,"99",2000-01-02
09:25:00,"#NS:2000000281",-1,0,000000018AC09D10,,"",,
If I import this data without format file it works (not for all lines).
If I include only good lines in other file and try with format file, I get
the following error.
Without format file, works.
Regards,
Simon
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:eU4cRioyFHA.2960@.tk2msftngp13.phx.gbl...
> You get a "string or binary data would be truncated" when the data you are
> trying to insert in a column is longer than the maximum length allowed in
> that column. You can compare the format file with the table definition to
> see if that is the case. If it seems to be ok, but you still get the
> error, please post both the table definition and the format file.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "simon" <simon.zupan@.stud-moderna.si> wrote in message
> news:etBsrYoyFHA.3180@.TK2MSFTNGP14.phx.gbl...
>|||I think the problem could be with the varbinary column. The implicit
conversion from strings to binaries is not very intuitive. I can quickly
pinpoint the exact problem though.
Jacco Schalkwijk
SQL Server MVP
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:eEUNbqwyFHA.1256@.TK2MSFTNGP09.phx.gbl...
> everything seems OK to me.
> FORMAT FILE:
> 7.0
> 38
> 1 SQLCHAR 0 10 "," 1 column1
> 2 SQLCHAR 0 4 "," 2 column2
> 3 SQLCHAR 0 10 "," 3 column3
> 4 SQLCHAR 0 100 "," 4 column4
> 5 SQLCHAR 0 8 "," 5 column5
> 6 SQLCHAR 0 8 "," 6 column6
> 7 SQLCHAR 0 8 "," 7 column7
> 8 SQLCHAR 0 8 "," 8 column8
> 9 SQLCHAR 0 8 "," 9 column9
> 10 SQLCHAR 0 8 "," 10 column10
> 11 SQLCHAR 0 2 "," 11 column11
> 12 SQLCHAR 0 2 "," 12 column12
> 13 SQLCHAR 0 4 "," 13 column13
> 14 SQLCHAR 0 2 "," 14 column14
> 15 SQLCHAR 0 8 "," 15 column15
> 16 SQLCHAR 0 2 "," 16 column16
> 17 SQLCHAR 0 10 "," 17 column17
> 18 SQLCHAR 0 2 "," 18 column18
> 19 SQLCHAR 0 10 "," 19 column19
> 20 SQLCHAR 0 2 "," 20 column20
> 21 SQLCHAR 0 100 "," 21 column21
> 22 SQLCHAR 0 16 "," 22 column22
> 23 SQLCHAR 0 100 "," 23 column23
> 24 SQLCHAR 0 16 "," 24 column24
> 25 SQLCHAR 0 2 "," 25 column25
> 26 SQLCHAR 0 16 "," 26 column26
> 27 SQLCHAR 0 2 "," 27 column27
> 28 SQLCHAR 0 16 "," 28 column28
> 29 SQLCHAR 0 2 "," 29 column29
> 30 SQLCHAR 0 16 "," 30 column30
> 31 SQLCHAR 0 20 "," 31 column31
> 32 SQLCHAR 0 2 "," 32 column32
> 33 SQLCHAR 0 8 "," 33 column33
> 34 SQLCHAR 0 8 "," 34 column34
> 35 SQLCHAR 0 8 "," 35 column35
> 36 SQLCHAR 0 2 "," 36 column36
> 37 SQLCHAR 0 8 "," 37 column37
> 38 SQLCHAR 0 8 "\r\n" 38 column38
> table definition:
> CREATE TABLE [T_TABLE] (
> [column1] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [column2] [int] NOT NULL ,
> [column3] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [column4] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
> [column5] [float] NOT NULL CONSTRAINT [DF_T_column5] DEFAULT (0),
> [column6] [float] NULL CONSTRAINT [DF_T_column6] DEFAULT (0),
> [column7] [float] NOT NULL CONSTRAINT [DF_T_column7] DEFAULT (0),
> [column8] [float] NOT NULL CONSTRAINT [DF_T_column8] DEFAULT (0),
> [column9] [float] NULL CONSTRAINT [DF_T_column9] DEFAULT (0),
> [column10] [float] NOT NULL CONSTRAINT [DF_T_column10] DEFAULT (0),
> [column11] [smallint] NULL CONSTRAINT [DF_T_column11] DEFAULT (0),
> [column12] [Boolean] NOT NULL ,
> [column13] [int] NULL ,
> [column14] [smallint] NULL CONSTRAINT [DF_T_column14] DEFAULT (0),
> [column15] [float] NULL CONSTRAINT [DF_T_column15] DEFAULT (0),
> [column16] [Boolean] NOT NULL ,
> [column17] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [column18] [Boolean] NOT NULL ,
> [column19] [varchar] (10) COLLATE Slovenian_CI_AS NULL ,
> [column20] [smallint] NULL CONSTRAINT [DF_T_column20] DEFAULT (0),
> [column21] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
> [column22] [smalldatetime] NULL ,
> [column23] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
> [column24] [smalldatetime] NULL ,
> [column25] [Boolean] NOT NULL ,
> [column26] [smalldatetime] NULL CONSTRAINT [DF_T_column26] DEFAULT
> (getdate()),
> [column27] [varchar] (2) COLLATE Slovenian_CI_AS NOT NULL CONSTRAINT
> [DF__T_column27] DEFAULT ('**'),
> [column28] [smalldatetime] NOT NULL CONSTRAINT [DF__T_column28] DEFAULT
> (getdate()),
> [column29] [varchar] (2) COLLATE Slovenian_CI_AS NOT NULL CONSTRAINT
> [DF__T_column29] DEFAULT ('**'),
> [column30] [smalldatetime] NOT NULL CONSTRAINT [DF_T_column30] DEFAULT
> (getdate()),
> [column31] [varchar] (20) COLLATE Slovenian_CI_AS NULL ,
> [column32] [Boolean] NOT NULL CONSTRAINT [DF_T_column32] DEFAULT (0),
> [column33] [float] NULL CONSTRAINT [DF_T_column33] DEFAULT (0),
> [column34] [timestamp] NULL ,
> [column35] [varbinary] (8) NULL ,
> [column36] [char] (2) COLLATE Slovenian_CI_AS NULL ,
> [column37] [float] NULL CONSTRAINT [DF_T_column37] DEFAULT (0),
> [column38] [float] NULL CONSTRAINT [DF_T_column38] DEFAULT (0),
> CONSTRAINT [PK_T_TABLE] PRIMARY KEY CLUSTERED
> (
> [column1],
> [column2]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> I just change column names.
> And the data file was exported directly from Enterprise manager. One line
> looks like:
> "_000000281",1156055,"I0121","product",-1,0,39960,0,0,-39960,0,0,0,0,0,0,"
AV",0,"",0,"",,"",,0,2003-01-23
> 20:50:00,"NS",2000-01-02 09:25:00,"99",2000-01-02
> 09:25:00,"#NS:2000000281",-1,0,000000018AC09D10,,"",,
> If I import this data without format file it works (not for all lines).
> If I include only good lines in other file and try with format file, I get
> the following error.
> Without format file, works.
> Regards,
> Simon
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:eU4cRioyFHA.2960@.tk2msftngp13.phx.gbl...
>