build sql script

Saturday, February 25, 2012

BULK INSERT - escape " - .fmt

Hi All,
Simple table:
TABLE:
create table employee (
name char(20),
title char(120)
)
DATAFILE(.csv):
name;title
Gustavo;"Marketing Assistant"
Catherine;"Engineering Manager"
I'd like use BULK INSERT command to insert this data. Tell me please,
how should look formatfile (.fmt) for this example (SQL 2005). I need
data after BULK INSERT looks like this:
name title
-- --
Gustavo Marketing Assistant
Catherine Engineering Manager
NOT like this:
name title
-- --
Gustavo "Marketing Assistant"
Catherine "Engineering Manager"
So, " should be escape.
Thank you
--
RegardsHello,
I've done this, it's almost finished. You can tray this:
/
***************************************************************************************************/
--table
CREATE TABLE [dbo].[ImportTest](
[fname] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[lname] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[organization] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[address] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[zip] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[city] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[state] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[email] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
--data file (ImportTest.csv), REMEMBER at the end you HAVE TO add
ENTER
fname;lname;organization;address;zip;city;state;email;
Shirley;Birosik;;;90013;"Los Angeles";"CA";"sbirosik@.gmail.com";
Dennis;Eschen;;;94920;"Alhambra";"CA";"de@.hotmail.com";
Valerie;Chambers;;;91803;"Long Beach";"CA";"vweber@.verizon.net";
--fmt file(ImportTest.Fmt), add ENTER at the end
9.0
8
1 SQLCHAR 0 510 ";" 1
fname Latin1_General_CI_AS
2 SQLCHAR 0 510 ";" 2
lname Latin1_General_CI_AS
3 SQLCHAR 0 510 ";" 3
organization Latin1_General_CI_AS
4 SQLCHAR 0 510 ";" 4
address Latin1_General_CI_AS
5 SQLCHAR 0 510 ";\"" 5
zip Latin1_General_CI_AS
6 SQLCHAR 0 510 "\";\"" 6
city Latin1_General_CI_AS
7 SQLCHAR 0 510 "\";\"" 7
state Latin1_General_CI_AS
8 SQLCHAR 0 510 "\r\n" 8
email Latin1_General_CI_AS
bulk insert ImportTest
from 'd:\bulk\ImportTest.csv'
with (
formatfile = 'd:\bulk\ImportTest.Fmt',
firstrow = 2
)
--(2 row(s) affected)
select*
from ImportTest
Dennis Eschen NULL NULL 94920 Alhambra CA
de@.hotmail.com";
Valerie Chambers NULL NULL 91803 Long Beach CA
vweber@.verizon.net";
/
***************************************************************************************************/
For me now most important is why didn't insert first row (data row,
not column definition)?
When I added quotation mark to column definition works fine, but I
can't modify .csv file, I received it from third part company :( is it
possible add all rows without modification .csv file?
Second, how remove quotation mark and semicolon from last column?
Regards,
anxcomp

BULK INSERT - error column too long

Hi,
I need to load a data file with bulk load.
I have these table:
CREATE TABLE [dbo].[test_db_import](
[campo_0] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_1] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_2] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_3] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_4] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_5] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_6] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_7] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_8] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[campo_9] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_10] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_11] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_12] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[tappo] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
the input file (a file .txt) is like that:
"value1";"value2";"value3";"value4";"value5";"value6";"value7";"value8";"value9";"value10";"value11";"value12";"value13"
13 value, with text qualifier double quote, in one row
and the format file:
9.0
13
1 SQLCHAR 0 0 "\"" 1 campo_0 "SQL_Latin1_General_CP1_CI_AS"
2 SQLCHAR 0 0 "\";\"" 2 campo_1 "SQL_Latin1_General_CP1_CI_AS"
3 SQLCHAR 0 0 "\";\"" 3 campo_2 "SQL_Latin1_General_CP1_CI_AS"
4 SQLCHAR 0 0 "\";\"" 4 campo_3 "SQL_Latin1_General_CP1_CI_AS"
5 SQLTEXT 0 0 "\";\"" 5 campo_4 "SQL_Latin1_General_CP1_CI_AS"
6 SQLCHAR 0 0 "\";\"" 6 campo_5 "SQL_Latin1_General_CP1_CI_AS"
7 SQLCHAR 0 0 "\";\"" 7 campo_6 "SQL_Latin1_General_CP1_CI_AS"
8 SQLCHAR 0 0 "\";\"" 8 campo_7 "SQL_Latin1_General_CP1_CI_AS"
9 SQLTEXT 0 0 "\";\"" 9 campo_8 "SQL_Latin1_General_CP1_CI_AS"
10 SQLCHAR 0 0 "\";\"" 10 campo_9 "SQL_Latin1_General_CP1_CI_AS"
11 SQLCHAR 0 0 "\";\"" 11 campo_10 "SQL_Latin1_General_CP1_CI_AS"
12 SQLCHAR 0 0 "\";\"" 12 campo_11 "SQL_Latin1_General_CP1_CI_AS"
13 SQLCHAR 0 0 \"" 13 campo_12 "SQL_Latin1_General_CP1_CI_AS"
I have these error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]
error bulk load. The data file contains a column too long for the row
1, column 13. Check the rowterminator and the field terminator.
(sorry, that is a translation from error message in other languages)
I have SQL Server 2005 St. edition, and I don't have direct access to
the db server.
Can you help me?
Thank's!
FrancI would guess that the data file is missing row terminators on some columns.
You may have to visually inspect and/or correct the data file.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Franc" <webmasterpoint@.gmail.com> wrote in message
news:1166518533.669439.310530@.t46g2000cwa.googlegroups.com...
> Hi,
> I need to load a data file with bulk load.
> I have these table:
> CREATE TABLE [dbo].[test_db_import](
> [campo_0] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_1] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_2] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_3] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_4] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_5] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_6] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_7] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_8] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> [campo_9] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_10] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_11] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_12] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> [tappo] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> the input file (a file .txt) is like that:
> "value1";"value2";"value3";"value4";"value5";"value6";"value7";"value8";"value9";"value10";"value11";"value12";"value13"
> 13 value, with text qualifier double quote, in one row
> and the format file:
> 9.0
> 13
> 1 SQLCHAR 0 0 "\"" 1 campo_0 "SQL_Latin1_General_CP1_CI_AS"
> 2 SQLCHAR 0 0 "\";\"" 2 campo_1 "SQL_Latin1_General_CP1_CI_AS"
> 3 SQLCHAR 0 0 "\";\"" 3 campo_2 "SQL_Latin1_General_CP1_CI_AS"
> 4 SQLCHAR 0 0 "\";\"" 4 campo_3 "SQL_Latin1_General_CP1_CI_AS"
> 5 SQLTEXT 0 0 "\";\"" 5 campo_4 "SQL_Latin1_General_CP1_CI_AS"
> 6 SQLCHAR 0 0 "\";\"" 6 campo_5 "SQL_Latin1_General_CP1_CI_AS"
> 7 SQLCHAR 0 0 "\";\"" 7 campo_6 "SQL_Latin1_General_CP1_CI_AS"
> 8 SQLCHAR 0 0 "\";\"" 8 campo_7 "SQL_Latin1_General_CP1_CI_AS"
> 9 SQLTEXT 0 0 "\";\"" 9 campo_8 "SQL_Latin1_General_CP1_CI_AS"
> 10 SQLCHAR 0 0 "\";\"" 10 campo_9 "SQL_Latin1_General_CP1_CI_AS"
> 11 SQLCHAR 0 0 "\";\"" 11 campo_10 "SQL_Latin1_General_CP1_CI_AS"
> 12 SQLCHAR 0 0 "\";\"" 12 campo_11 "SQL_Latin1_General_CP1_CI_AS"
> 13 SQLCHAR 0 0 \"" 13 campo_12 "SQL_Latin1_General_CP1_CI_AS"
>
> I have these error:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> error bulk load. The data file contains a column too long for the row
> 1, column 13. Check the rowterminator and the field terminator.
> (sorry, that is a translation from error message in other languages)
> I have SQL Server 2005 St. edition, and I don't have direct access to
> the db server.
> Can you help me?
> Thank's!
> Franc
>|||Hi,
Thank's!
I try with only one row:
"10092itid2011";"Batterie Notebook e Computers Portatili HP,
COMPAQ";"125.04";"EUR";""Cod CBI0823A; Casa 2-Power; Descrizione
Batteria notebook 14.8v 4400mAh per HP Pavilion NX,
XT1-XT2-XT3-XT4-XT5, ZE4000, 5000, ZE4300-5185, HP e Compaq Business
Notebook NX9000-NX9005-NX9010, N1050V, Compaq Presario 1110,1115,1120,
2100, 2500
serie,";"value";"658";"Accumulatori";"value";"value";"value";"value";"HP,
COMPAQ"
and import correctly.
But I have a problem:
in the import skip the first column of table (field: 'campo_0')
and insert the last two field
"value";"HP, COMPAQ"
in 'campo_12' column
Thank's!
Franc
Arnie Rowland ha scritto:
> I would guess that the data file is missing row terminators on some columns.
> You may have to visually inspect and/or correct the data file.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Franc" <webmasterpoint@.gmail.com> wrote in message
> news:1166518533.669439.310530@.t46g2000cwa.googlegroups.com...
> > Hi,
> > I need to load a data file with bulk load.
> >
> > I have these table:
> >
> > CREATE TABLE [dbo].[test_db_import](
> > [campo_0] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_1] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_2] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_3] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_4] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_5] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_6] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_7] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_8] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> > [campo_9] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_10] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_11] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_12] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> > [tappo] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> >
> > the input file (a file .txt) is like that:
> >
> > "value1";"value2";"value3";"value4";"value5";"value6";"value7";"value8";"value9";"value10";"value11";"value12";"value13"
> >
> > 13 value, with text qualifier double quote, in one row
> >
> > and the format file:
> >
> > 9.0
> > 13
> > 1 SQLCHAR 0 0 "\"" 1 campo_0 "SQL_Latin1_General_CP1_CI_AS"
> > 2 SQLCHAR 0 0 "\";\"" 2 campo_1 "SQL_Latin1_General_CP1_CI_AS"
> > 3 SQLCHAR 0 0 "\";\"" 3 campo_2 "SQL_Latin1_General_CP1_CI_AS"
> > 4 SQLCHAR 0 0 "\";\"" 4 campo_3 "SQL_Latin1_General_CP1_CI_AS"
> > 5 SQLTEXT 0 0 "\";\"" 5 campo_4 "SQL_Latin1_General_CP1_CI_AS"
> > 6 SQLCHAR 0 0 "\";\"" 6 campo_5 "SQL_Latin1_General_CP1_CI_AS"
> > 7 SQLCHAR 0 0 "\";\"" 7 campo_6 "SQL_Latin1_General_CP1_CI_AS"
> > 8 SQLCHAR 0 0 "\";\"" 8 campo_7 "SQL_Latin1_General_CP1_CI_AS"
> > 9 SQLTEXT 0 0 "\";\"" 9 campo_8 "SQL_Latin1_General_CP1_CI_AS"
> > 10 SQLCHAR 0 0 "\";\"" 10 campo_9 "SQL_Latin1_General_CP1_CI_AS"
> > 11 SQLCHAR 0 0 "\";\"" 11 campo_10 "SQL_Latin1_General_CP1_CI_AS"
> > 12 SQLCHAR 0 0 "\";\"" 12 campo_11 "SQL_Latin1_General_CP1_CI_AS"
> > 13 SQLCHAR 0 0 \"" 13 campo_12 "SQL_Latin1_General_CP1_CI_AS"
> >
> >
> > I have these error:
> >
> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> > [Microsoft][ODBC SQL Server Driver][SQL Server]
> > error bulk load. The data file contains a column too long for the row
> > 1, column 13. Check the rowterminator and the field terminator.
> > (sorry, that is a translation from error message in other languages)
> >
> > I have SQL Server 2005 St. edition, and I don't have direct access to
> > the db server.
> >
> > Can you help me?
> >
> > Thank's!
> > Franc
> >|||I would guess that the data file is missing row terminators on some columns.
You may have to visually inspect and/or correct the data file.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Franc" <webmasterpoint@.gmail.com> wrote in message
news:1166518533.669439.310530@.t46g2000cwa.googlegroups.com...
> Hi,
> I need to load a data file with bulk load.
> I have these table:
> CREATE TABLE [dbo].[test_db_import](
> [campo_0] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_1] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_2] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_3] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_4] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_5] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_6] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_7] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_8] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> [campo_9] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_10] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_11] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_12] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> [tappo] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> the input file (a file .txt) is like that:
> "value1";"value2";"value3";"value4";"value5";"value6";"value7";"value8";"value9";"value10";"value11";"value12";"value13"
> 13 value, with text qualifier double quote, in one row
> and the format file:
> 9.0
> 13
> 1 SQLCHAR 0 0 "\"" 1 campo_0 "SQL_Latin1_General_CP1_CI_AS"
> 2 SQLCHAR 0 0 "\";\"" 2 campo_1 "SQL_Latin1_General_CP1_CI_AS"
> 3 SQLCHAR 0 0 "\";\"" 3 campo_2 "SQL_Latin1_General_CP1_CI_AS"
> 4 SQLCHAR 0 0 "\";\"" 4 campo_3 "SQL_Latin1_General_CP1_CI_AS"
> 5 SQLTEXT 0 0 "\";\"" 5 campo_4 "SQL_Latin1_General_CP1_CI_AS"
> 6 SQLCHAR 0 0 "\";\"" 6 campo_5 "SQL_Latin1_General_CP1_CI_AS"
> 7 SQLCHAR 0 0 "\";\"" 7 campo_6 "SQL_Latin1_General_CP1_CI_AS"
> 8 SQLCHAR 0 0 "\";\"" 8 campo_7 "SQL_Latin1_General_CP1_CI_AS"
> 9 SQLTEXT 0 0 "\";\"" 9 campo_8 "SQL_Latin1_General_CP1_CI_AS"
> 10 SQLCHAR 0 0 "\";\"" 10 campo_9 "SQL_Latin1_General_CP1_CI_AS"
> 11 SQLCHAR 0 0 "\";\"" 11 campo_10 "SQL_Latin1_General_CP1_CI_AS"
> 12 SQLCHAR 0 0 "\";\"" 12 campo_11 "SQL_Latin1_General_CP1_CI_AS"
> 13 SQLCHAR 0 0 \"" 13 campo_12 "SQL_Latin1_General_CP1_CI_AS"
>
> I have these error:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> error bulk load. The data file contains a column too long for the row
> 1, column 13. Check the rowterminator and the field terminator.
> (sorry, that is a translation from error message in other languages)
> I have SQL Server 2005 St. edition, and I don't have direct access to
> the db server.
> Can you help me?
> Thank's!
> Franc
>|||This looks 'odd' to me:
;""
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Franc" <webmasterpoint@.gmail.com> wrote in message
news:1166520934.333254.285360@.48g2000cwx.googlegroups.com...
> Hi,
> Thank's!
> I try with only one row:
> "10092itid2011";"Batterie Notebook e Computers Portatili HP,
> COMPAQ";"125.04";"EUR";""Cod CBI0823A; Casa 2-Power; Descrizione
> Batteria notebook 14.8v 4400mAh per HP Pavilion NX,
> XT1-XT2-XT3-XT4-XT5, ZE4000, 5000, ZE4300-5185, HP e Compaq Business
> Notebook NX9000-NX9005-NX9010, N1050V, Compaq Presario 1110,1115,1120,
> 2100, 2500
> serie,";"value";"658";"Accumulatori";"value";"value";"value";"value";"HP,
> COMPAQ"
> and import correctly.
> But I have a problem:
> in the import skip the first column of table (field: 'campo_0')
> and insert the last two field
> "value";"HP, COMPAQ"
> in 'campo_12' column
> Thank's!
> Franc
> Arnie Rowland ha scritto:
>> I would guess that the data file is missing row terminators on some
>> columns.
>> You may have to visually inspect and/or correct the data file.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to
>> the
>> top yourself.
>> - H. Norman Schwarzkopf
>>
>> "Franc" <webmasterpoint@.gmail.com> wrote in message
>> news:1166518533.669439.310530@.t46g2000cwa.googlegroups.com...
>> > Hi,
>> > I need to load a data file with bulk load.
>> >
>> > I have these table:
>> >
>> > CREATE TABLE [dbo].[test_db_import](
>> > [campo_0] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_1] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_2] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_3] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_4] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_5] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_6] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_7] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_8] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
>> > [campo_9] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_10] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_11] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_12] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
>> > [tappo] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL
>> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>> >
>> > the input file (a file .txt) is like that:
>> >
>> > "value1";"value2";"value3";"value4";"value5";"value6";"value7";"value8";"value9";"value10";"value11";"value12";"value13"
>> >
>> > 13 value, with text qualifier double quote, in one row
>> >
>> > and the format file:
>> >
>> > 9.0
>> > 13
>> > 1 SQLCHAR 0 0 "\"" 1 campo_0 "SQL_Latin1_General_CP1_CI_AS"
>> > 2 SQLCHAR 0 0 "\";\"" 2 campo_1 "SQL_Latin1_General_CP1_CI_AS"
>> > 3 SQLCHAR 0 0 "\";\"" 3 campo_2 "SQL_Latin1_General_CP1_CI_AS"
>> > 4 SQLCHAR 0 0 "\";\"" 4 campo_3 "SQL_Latin1_General_CP1_CI_AS"
>> > 5 SQLTEXT 0 0 "\";\"" 5 campo_4 "SQL_Latin1_General_CP1_CI_AS"
>> > 6 SQLCHAR 0 0 "\";\"" 6 campo_5 "SQL_Latin1_General_CP1_CI_AS"
>> > 7 SQLCHAR 0 0 "\";\"" 7 campo_6 "SQL_Latin1_General_CP1_CI_AS"
>> > 8 SQLCHAR 0 0 "\";\"" 8 campo_7 "SQL_Latin1_General_CP1_CI_AS"
>> > 9 SQLTEXT 0 0 "\";\"" 9 campo_8 "SQL_Latin1_General_CP1_CI_AS"
>> > 10 SQLCHAR 0 0 "\";\"" 10 campo_9 "SQL_Latin1_General_CP1_CI_AS"
>> > 11 SQLCHAR 0 0 "\";\"" 11 campo_10 "SQL_Latin1_General_CP1_CI_AS"
>> > 12 SQLCHAR 0 0 "\";\"" 12 campo_11 "SQL_Latin1_General_CP1_CI_AS"
>> > 13 SQLCHAR 0 0 \"" 13 campo_12 "SQL_Latin1_General_CP1_CI_AS"
>> >
>> >
>> > I have these error:
>> >
>> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
>> > [Microsoft][ODBC SQL Server Driver][SQL Server]
>> > error bulk load. The data file contains a column too long for the row
>> > 1, column 13. Check the rowterminator and the field terminator.
>> > (sorry, that is a translation from error message in other languages)
>> >
>> > I have SQL Server 2005 St. edition, and I don't have direct access to
>> > the db server.
>> >
>> > Can you help me?
>> >
>> > Thank's!
>> > Franc
>> >
>

BULK INSERT - error column too long

BULK INSERT - does it lock the file while reading?

Any suggestions or comments?
Thanks,
MichaelWHy not BCP into a "Different" table and then run a Stored Proc to Process
the records into the main table from the One you BCP'd into?
1) Step 2, where any locks would be created on the main table, will be
faster.
2) If there's still a concurrency issues, you can rewrite the Step 2 code to
process the records from one table into the main table in smaller batches...
reducint the batch size until the concurrency issue goes away.
"M Bourgon" wrote:

> Howdy, all. We have a (log-type) file that's constantly being written
> to. We need to grab the latest rows out of this file, insert them into
> a table, and then process them. We've found out the hard way that BCP
> locks the file while it's inserting, so rows can't be written to the
> file while the BCP is running.
> Our current workaround is to make a copy of the file (using ftp), but
> we're running into other problems.
> I'm trying to find a better way to do this. We've narrowed down a
> couple.
> 1) use TAIL or something similar to grab the records since we last ran.
> 2) use BULK INSERT, provided it doesn't lock the file.
> 3) get suggestions off of USENET.

>
> Any suggestions or comments?
> Thanks,
> Michael
>|||"M Bourgon" <bourgon@.gmail.com> wrote in message
news:1110928274.048339.317490@.f14g2000cwb.googlegroups.com...
> Howdy, all. We have a (log-type) file that's constantly being written
> to. We need to grab the latest rows out of this file, insert them into
> a table, and then process them. We've found out the hard way that BCP
> locks the file while it's inserting, so rows can't be written to the
> file while the BCP is running.
> Our current workaround is to make a copy of the file (using ftp), but
> we're running into other problems.
> I'm trying to find a better way to do this. We've narrowed down a
> couple.
> 1) use TAIL or something similar to grab the records since we last ran.
> 2) use BULK INSERT, provided it doesn't lock the file.
> 3) get suggestions off of USENET.

>
> Any suggestions or comments?
> Thanks,
> Michael
>
Would a half baked suggestion do?
Write an ActiveX Script to open the file shared read only ,
find your starting point and loop to the end doing either
1. a write out of the lines as is to a scratch file that you can then
BulkCopy in
2. a parse of the log file lines and then update the DB directly.|||The problem is that the file's still locked during that time, and I
can't afford to lose any rows.|||Not a bad idea, though I may just use tail for simplicity, assuming it
doesn't lock the file as well. Thanks|||Answering my own question - yes, bulk insert DOES lock the file -
couldn't append to the file, got "The process cannot access the file
because it is being used by another process.". Looks like I'll
tail/etc the file. Thanks, guys.

BULK INSERT - does it lock the file while reading?

Our current workaround is to make a copy of the file (using ftp), but
we're running into other problems.

I'm trying to find a better way to do this. We've narrowed down a
couple.
1) use TAIL or something similar to grab the records since we last ran.
2) use BULK INSERT, provided it doesn't lock the file.
3) get suggestions off of USENET. :)

Any suggestions or comments?
Thanks,
Michael"M Bourgon" <bourgon@.gmail.com> wrote in message
news:1110928274.048339.317490@.f14g2000cwb.googlegr oups.com...
> Howdy, all. We have a (log-type) file that's constantly being written
> to. We need to grab the latest rows out of this file, insert them into
> a table, and then process them. We've found out the hard way that BCP
> locks the file while it's inserting, so rows can't be written to the
> file while the BCP is running.
> Our current workaround is to make a copy of the file (using ftp), but
> we're running into other problems.
> I'm trying to find a better way to do this. We've narrowed down a
> couple.
> 1) use TAIL or something similar to grab the records since we last ran.
> 2) use BULK INSERT, provided it doesn't lock the file.
> 3) get suggestions off of USENET. :)
>
> Any suggestions or comments?
> Thanks,
> Michael

Would a half baked suggestion do?

Write an ActiveX Script to open the file shared read only ,
find your starting point and loop to the end doing either
1. a write out of the lines as is to a scratch file that you can then
BulkCopy in
2. a parse of the log file lines and then update the DB directly.|||Not a bad idea, though I may just use tail for simplicity, assuming it
doesn't lock the file as well. Thanks|||Answering my own question - yes, bulk insert DOES lock the file -
couldn't append to the file, got "The process cannot access the file
because it is being used by another process.". Looks like I'll
tail/etc the file. Thanks, guys.

BULK INSERT - does it lock the file while reading?

Howdy, all. We have a (log-type) file that's constantly being written
to. We need to grab the latest rows out of this file, insert them into
a table, and then process them. We've found out the hard way that BCP
locks the file while it's inserting, so rows can't be written to the
file while the BCP is running.
Our current workaround is to make a copy of the file (using ftp), but
we're running into other problems.
I'm trying to find a better way to do this. We've narrowed down a
couple.
1) use TAIL or something similar to grab the records since we last ran.
2) use BULK INSERT, provided it doesn't lock the file.
3) get suggestions off of USENET. :)
Any suggestions or comments?
Thanks,
MichaelWHy not BCP into a "Different" table and then run a Stored Proc to Process
the records into the main table from the One you BCP'd into?
1) Step 2, where any locks would be created on the main table, will be
faster.
2) If there's still a concurrency issues, you can rewrite the Step 2 code to
process the records from one table into the main table in smaller batches...
reducint the batch size until the concurrency issue goes away.
"M Bourgon" wrote:
> Howdy, all. We have a (log-type) file that's constantly being written
> to. We need to grab the latest rows out of this file, insert them into
> a table, and then process them. We've found out the hard way that BCP
> locks the file while it's inserting, so rows can't be written to the
> file while the BCP is running.
> Our current workaround is to make a copy of the file (using ftp), but
> we're running into other problems.
> I'm trying to find a better way to do this. We've narrowed down a
> couple.
> 1) use TAIL or something similar to grab the records since we last ran.
> 2) use BULK INSERT, provided it doesn't lock the file.
> 3) get suggestions off of USENET. :)
>
> Any suggestions or comments?
> Thanks,
> Michael
>|||"M Bourgon" <bourgon@.gmail.com> wrote in message
news:1110928274.048339.317490@.f14g2000cwb.googlegroups.com...
> Howdy, all. We have a (log-type) file that's constantly being written
> to. We need to grab the latest rows out of this file, insert them into
> a table, and then process them. We've found out the hard way that BCP
> locks the file while it's inserting, so rows can't be written to the
> file while the BCP is running.
> Our current workaround is to make a copy of the file (using ftp), but
> we're running into other problems.
> I'm trying to find a better way to do this. We've narrowed down a
> couple.
> 1) use TAIL or something similar to grab the records since we last ran.
> 2) use BULK INSERT, provided it doesn't lock the file.
> 3) get suggestions off of USENET. :)
>
> Any suggestions or comments?
> Thanks,
> Michael
>
Would a half baked suggestion do?
Write an ActiveX Script to open the file shared read only ,
find your starting point and loop to the end doing either
1. a write out of the lines as is to a scratch file that you can then
BulkCopy in
2. a parse of the log file lines and then update the DB directly.|||The problem is that the file's still locked during that time, and I
can't afford to lose any rows.|||Not a bad idea, though I may just use tail for simplicity, assuming it
doesn't lock the file as well. Thanks|||Answering my own question - yes, bulk insert DOES lock the file -
couldn't append to the file, got "The process cannot access the file
because it is being used by another process.". Looks like I'll
tail/etc the file. Thanks, guys.

Saturday, February 25, 2012

BULK INSERT - escape " - .fmt

BULK INSERT - error column too long

BULK INSERT - error column too long

BULK INSERT - error column too long

BULK INSERT - does it lock the file while reading?

BULK INSERT - does it lock the file while reading?

BULK INSERT - does it lock the file while reading?

build sql script

Blog Archive

About Me