bulk upload of files to table

i am using this code to upload file to a TempTable in the SQL SERVER :
"for %%f in ( F:\Temp\*.txt) do bcp ...."
the thing is that this code load all the file that in a given folder to the
table.
is it possible to some how (using a switch or anything else) that only 10
files each time will be uploaded?
thnaks in advance
peleg
On Tue, 19 Jun 2007 01:09:00 -0700, pelegk1
<pelegk1@.discussions.microsoft.com> wrote:

>i am using this code to upload file to a TempTable in the SQL SERVER :
>"for %%f in ( F:\Temp\*.txt) do bcp ...."
>the thing is that this code load all the file that in a given folder to the
>table.
>is it possible to some how (using a switch or anything else) that only 10
>files each time will be uploaded?
>thnaks in advance
>peleg
The FOR loop used in a bat file or at a command prompt has no
provision for working in batches of 10. Even if it somehow was able
to stop at ten, how would it know to start at number 11 the next time?
The simplest alternative I can think of is to write a program that
copies ten files at a time to a sub-folder, invokes the command as
above against the sub-folder, and then deletes the files from the
sub-folder.
Roy Harvey
Beacon Falls, CT
|||One method to call a separate command file that exits once the limit
reached. For example:
REM first command file
@.SET /a FileCount = 0
@.for %%f in ( * ) do CALL test1.cmd "%%f"
REM second command file
@.SET /a FileCount = %FileCount% + 1
@.IF %FileCount% GTR 10 GOTO Done
bcp ... "%1" in ...
:Done
However, I agree with Roy that you are better off with something more robust
than a command file. Perhaps a VBScript will suffice.
Hope this helps.
Dan Guzman
SQL Server MVP
"pelegk1" <pelegk1@.discussions.microsoft.com> wrote in message
news:2AFC6622-4C89-47F1-805C-33EDEE96BEA1@.microsoft.com...
>i am using this code to upload file to a TempTable in the SQL SERVER :
> "for %%f in ( F:\Temp\*.txt) do bcp ...."
> the thing is that this code load all the file that in a given folder to
> the
> table.
> is it possible to some how (using a switch or anything else) that only 10
> files each time will be uploaded?
> thnaks in advance
> peleg
>
sql

bulk upload of files to table

i am using this code to upload file to a TempTable in the SQL SERVER :
"for %%f in ( F:\Temp\*.txt) do bcp ...."
the thing is that this code load all the file that in a given folder to the
table.
is it possible to some how (using a switch or anything else) that only 10
files each time will be uploaded?
thnaks in advance
pelegOn Tue, 19 Jun 2007 01:09:00 -0700, pelegk1
<pelegk1@.discussions.microsoft.com> wrote:

>i am using this code to upload file to a TempTable in the SQL SERVER :
>"for %%f in ( F:\Temp\*.txt) do bcp ...."
>the thing is that this code load all the file that in a given folder to the
>table.
>is it possible to some how (using a switch or anything else) that only 10
>files each time will be uploaded?
>thnaks in advance
>peleg
The FOR loop used in a bat file or at a command prompt has no
provision for working in batches of 10. Even if it somehow was able
to stop at ten, how would it know to start at number 11 the next time?
The simplest alternative I can think of is to write a program that
copies ten files at a time to a sub-folder, invokes the command as
above against the sub-folder, and then deletes the files from the
sub-folder.
Roy Harvey
Beacon Falls, CT|||One method to call a separate command file that exits once the limit
reached. For example:
REM first command file
@.SET /a FileCount = 0
@.for %%f in ( * ) do CALL test1.cmd "%%f"
REM second command file
@.SET /a FileCount = %FileCount% + 1
@.IF %FileCount% GTR 10 GOTO Done
bcp ... "%1" in ...
:Done
However, I agree with Roy that you are better off with something more robust
than a command file. Perhaps a VBScript will suffice.
Hope this helps.
Dan Guzman
SQL Server MVP
"pelegk1" <pelegk1@.discussions.microsoft.com> wrote in message
news:2AFC6622-4C89-47F1-805C-33EDEE96BEA1@.microsoft.com...
>i am using this code to upload file to a TempTable in the SQL SERVER :
> "for %%f in ( F:\Temp\*.txt) do bcp ...."
> the thing is that this code load all the file that in a given folder to
> the
> table.
> is it possible to some how (using a switch or anything else) that only 10
> files each time will be uploaded?
> thnaks in advance
> peleg
>

bulk upload of files to table

i am using this code to upload file to a TempTable in the SQL SERVER :
"for %%f in ( F:\Temp\*.txt) do bcp ...."
the thing is that this code load all the file that in a given folder to the
table.
is it possible to some how (using a switch or anything else) that only 10
files each time will be uploaded?
thnaks in advance
pelegOn Tue, 19 Jun 2007 01:09:00 -0700, pelegk1
<pelegk1@.discussions.microsoft.com> wrote:
>i am using this code to upload file to a TempTable in the SQL SERVER :
>"for %%f in ( F:\Temp\*.txt) do bcp ...."
>the thing is that this code load all the file that in a given folder to the
>table.
>is it possible to some how (using a switch or anything else) that only 10
>files each time will be uploaded?
>thnaks in advance
>peleg
The FOR loop used in a bat file or at a command prompt has no
provision for working in batches of 10. Even if it somehow was able
to stop at ten, how would it know to start at number 11 the next time?
The simplest alternative I can think of is to write a program that
copies ten files at a time to a sub-folder, invokes the command as
above against the sub-folder, and then deletes the files from the
sub-folder.
Roy Harvey
Beacon Falls, CT|||One method to call a separate command file that exits once the limit
reached. For example:
REM first command file
@.SET /a FileCount = 0
@.for %%f in ( * ) do CALL test1.cmd "%%f"
REM second command file
@.SET /a FileCount = %FileCount% + 1
@.IF %FileCount% GTR 10 GOTO Done
bcp ... "%1" in ...
:Done
However, I agree with Roy that you are better off with something more robust
than a command file. Perhaps a VBScript will suffice.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"pelegk1" <pelegk1@.discussions.microsoft.com> wrote in message
news:2AFC6622-4C89-47F1-805C-33EDEE96BEA1@.microsoft.com...
>i am using this code to upload file to a TempTable in the SQL SERVER :
> "for %%f in ( F:\Temp\*.txt) do bcp ...."
> the thing is that this code load all the file that in a given folder to
> the
> table.
> is it possible to some how (using a switch or anything else) that only 10
> files each time will be uploaded?
> thnaks in advance
> peleg
>

BULK INSERTing UNICODE data with format files

Sorry but could you please show me how I convert (specifically the
separators) this bcp format file to unicode so that it works (with a unicod
e
file). I have tried various methods without success.
8.0
2
1 SQLNCHAR 2 100 "," 1 RecordId
Latin1_General_CI_AS
2 SQLNCHAR 4 0 "\r\n" 2 TheText
Latin1_General_CI_AS
Thanks
Peter Doyle
"Erland Sommarskog" wrote:

> Nitin M (nitin@.nowhere.com) writes:
> Ah! Glad to hear that you where able to find it out yourself.
>
> The obvious idea would be to save the format file as Unicode, but that
> does not work; you only get a message about unknown version. I tried in
> SQL 2005 as well, but SQL 2005 appears to think that a Unicode file must
> be an XML format file.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>Peter Doyle (PeterDoyle@.discussions.microsoft.com) writes:
> Sorry but could you please show me how I convert (specifically the
> separators) this bcp format file to unicode so that it works (with a
> unicode file). I have tried various methods without success.
> 8.0
> 2
> 1 SQLNCHAR 2 100 "," 1 RecordId
> Latin1_General_CI_AS
> 2 SQLNCHAR 4 0 "\r\n" 2 TheText
> Latin1_General_CI_AS
The above won't work for several reasons. You have specified a
prefix length, but prefix lengths is only for binary formats.
The problem with impoting Unicode files, is that the format file itself
must be a 8-bit file. At least I seem to recall that BCP freaked out
when I tried with a Unicode file. (Hm, did I try BULK INSERT as well+)
Anyway, with the format indicated by your format file, you don't any
format file at all. For a table like:
CREATE TABLE test (a nvarchar(100) NOT NULL,
b nvarchar(100) NOT NULL)
And this data (saved as Unicode):
Detta r lite data,Och detta r mer data.
Sedan kommer det n mer data hr,Och nu kommer det n mer
Trams!, Det r vad det r!
This command line will do:
bcp tempdb..test in slask.bcp -w -t, -T
-w specifies that the file a Unicode file.
But in case that your real case calls for a format file, here is one
that works with the above table and data:
8.0
2
1 SQLNCHAR 0 0 ",\0" 1 a ""
2 SQLNCHAR 0 0 "\r\0\n\0" 2 b ""
The tricky part is the \0 that must come aftet the character it belongs
to, due to endianness.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Bulk Insert XML?

Is there a way to import a large XML file into SQL Server 2000/2005 using
T-SQL?
Looking for some utility like Bulk Insert/bcp...
- RSQLXML 3 SP3.
Comes with a great utility called SQLXML Bulk Load. Should do exactly what
you're after.
HTH. Ryan
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:53153384-7C83-40A5-9885-7DF2833DB41C@.microsoft.com...
> Is there a way to import a large XML file into SQL Server 2000/2005 using
> T-SQL?
> Looking for some utility like Bulk Insert/bcp...
> - R|||In SQL 2005 the OPENROWSET function has been improved:
http://msdn2.microsoft.com/en-us/library/ms190312(SQL.90).aspx
Look at SINGLE_BLOB, SINGLE_CLOB and SINGLE_NLOB.
ML
http://milambda.blogspot.com/|||Use OPENXML. thats the best way for insert of XML through T-SQL.
Checkout in BOL for more info. Hope this helps.
--
"Rakesh" wrote:

> Is there a way to import a large XML file into SQL Server 2000/2005 using
> T-SQL?
> Looking for some utility like Bulk Insert/bcp...
> - R|||I have a couple of examples:
http://www.sqlservercentral.com/columnists/sholliday/
http://spaces.msn.com/sholliday/ 9/22/2005
..
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:76E7E737-C5F9-435A-9CBA-298429652D4C@.microsoft.com...
> Use OPENXML. thats the best way for insert of XML through T-SQL.
> Checkout in BOL for more info. Hope this helps.
> --
>
>
> "Rakesh" wrote:
>
using

Bulk Insert SQL Server in C#

I am searching for an efficient way to do
bulk inserts via C#. It seems there are three
ways of doing that:

1.) Copy values to file and call BCP or BULK INSERT in TSQL
2.) Call the IRowFastLoad Interface of the SQL OLEDB Provider
3.) bcp interface of the ODBC driver

I think 2.) and 3.) are the only viable solutions and I wanted
to ask if anybody out there has already experiences
with these interfaces and the way of invocating them in C#.

Thank you for your help.

Bernhard
------------------
Solution Architect
Management Factory
Vienna/AUSTRIA/EUROPE
http://www.mf.ag
Bernhard.Walter@.mf.agYou may want to look at using SQL-DMO with C#. I've not coded in C# however I have used SQL-DMO with VB, VBScript (in an ASP page) and Perl.|||Thanks for the help.

Using SQL-DMO seems to be quite the same than inserting into a
file and then calling BCP, right ?
What I am looking for is a more efficient approach (memory to
SQL Server), because my routine has to work for 1 record to
millions of records in a fast way.

Bernhard

Bulk Insert Problem

Hi

I am trying to do a bulk insert using Query Analyzer for SQL 2000.

I have a Comma delimited file and a format file that i genrated using bcp.exe

the CSV file looks like this

08009700000,23,01,2007,23012007,16:28:09,01413412066,01,FreeConnect,00:00:05,0:05,0.083,0.002,2006,X
08009700000,24,01,2007,24012007,12:51:55,01413412066,01,FreeConnect,00:00:14,0:14,0.233,0.006,2006,X
08009700000,24,01,2007,24012007,12:52:28,01413412066,01,FreeConnect,00:00:10,0:10,0.167,0.004,2006,X

the format file look like this

8.0
15
1 SQLNCHAR 2 510 "," 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 2 510 "," 2 Field2 Latin1_General_CI_AS
3 SQLNCHAR 2 510 "," 3 Field3 Latin1_General_CI_AS
4 SQLNCHAR 2 510 "," 4 Field4 Latin1_General_CI_AS
5 SQLNCHAR 2 510 "," 5 Field5 Latin1_General_CI_AS
6 SQLNCHAR 2 510 "," 6 Field6 Latin1_General_CI_AS
7 SQLNCHAR 2 510 "," 7 Field7 Latin1_General_CI_AS
8 SQLNCHAR 2 510 "," 8 Field8 Latin1_General_CI_AS
9 SQLNCHAR 2 510 "," 9 Field9 Latin1_General_CI_AS
10 SQLNCHAR 2 510 "," 10 Field10 Latin1_General_CI_AS
11 SQLNCHAR 2 510 "," 11 Field11 Latin1_General_CI_AS
12 SQLNCHAR 2 510 "," 12 Field12 Latin1_General_CI_AS
13 SQLNCHAR 2 510 "," 13 Field13 Latin1_General_CI_AS
14 SQLNCHAR 2 510 "," 14 Field14 Latin1_General_CI_AS
15 SQLNCHAR 2 510 "," 15 Field15 Latin1_General_CI_AS

and the T-SQL code that i am using is

bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt')

i have also tried

bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt',ROWTERMINATOR = '\r\n')

and

bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt',ROWTERMINATOR = '\r')

what ever i do i get the get the follow error message

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.

can any one help ?

I haven't read through your post completely, because I'm too busy today. But...

In Enterprise Manager (or Management Studio), use the 'Import Data' menu entry to go through a wizard to import your data. Use the option to save the package, or in MgtStudio, script it out. Then you can re-use the package/script when you need to. But hopefully you will find the answer to your problem when you step through the wizard and it tells you useful information like "I can't find the end of the line 10 lines in...".

Actually - having skimmed through, it sounds like it can't find the commas properly, or else your columns are too short. Probably the first though, because I imagine you would've noticed if your columns were short. The wizard should really help you here.

Hope this helps,

Rob|||

hi and thanks for the reply.

I have been playing around with this and i have now got round the error that i was getting.

The problem seems to be in the format file that i am using, which is odd cause it was genarated using the bcp.

I found 2 problems in this file

the first which caused the error about the first column being two long was the column prefix was set to 2, when i changed this to 0 for each column the error went away.

then then got a new error about unexpected end of line, I solved this buy removing the comma on the last line of the format file and replaceing it with \r\n

so my format file now looks like this

8.0

15
1 SQLNCHAR 0 510 "," 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 0 510 "," 2 Field2 Latin1_General_CI_AS

...

15 SQLNCHAR 0 510 "\r\n" 15 Field15 Latin1_General_CI_AS

it now imports all the data, however it also seems to corrupt the data, if i do a select statement in QA the data looks like it is in the wrong font as it is all white empty boxes.

any one know why this happens ?

|||Dagz,

You got two of three issues answered, it looks like (changing the 2 to 0 and changing the final field terminator). If your file is Unicode, though, which I'm guessing it is from the SQLNCHAR in the format file, I think you need to specify the field terminators differently. Try this as the format file (or try SQLCHAR instead of SQLNCHAR, if your file is not a Unicode file):

8.0
15
1 SQLNCHAR 0 510 ",\x00" 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 0 510 ",\x00" 2 Field2 Latin1_General_CI_AS
3 SQLNCHAR 0 510 ",\x00" 3 Field3 Latin1_General_CI_AS
4 SQLNCHAR 0 510 ",\x00" 4 Field4 Latin1_General_CI_AS
5 SQLNCHAR 0 510 ",\x00" 5 Field5 Latin1_General_CI_AS
6 SQLNCHAR 0 510 ",\x00" 6 Field6 Latin1_General_CI_AS
7 SQLNCHAR 0 510 ",\x00" 7 Field7 Latin1_General_CI_AS
8 SQLNCHAR 0 510 ",\x00" 8 Field8 Latin1_General_CI_AS
9 SQLNCHAR 0 510 ",\x00" 9 Field9 Latin1_General_CI_AS
10 SQLNCHAR 0 510 ",\x00" 10 Field10 Latin1_General_CI_AS
11 SQLNCHAR 0 510 ",\x00" 11 Field11 Latin1_General_CI_AS
12 SQLNCHAR 0 510 ",\x00" 12 Field12 Latin1_General_CI_AS
13 SQLNCHAR 0 510 ",\x00" 13 Field13 Latin1_General_CI_AS
14 SQLNCHAR 0 510 ",\x00" 14 Field14 Latin1_General_CI_AS
15 SQLNCHAR 0 510 "\x0D\x00\x0A\x00" 15 Field15 Latin1_General_CI_AS

Steve Kass
http://www.stevekass.com
sql

Bulk Insert Problem

Hi

I am trying to do a bulk insert using Query Analyzer for SQL 2000.

I have a Comma delimited file and a format file that i genrated using bcp.exe

the CSV file looks like this

08009700000,23,01,2007,23012007,16:28:09,01413412066,01,FreeConnect,00:00:05,0:05,0.083,0.002,2006,X
08009700000,24,01,2007,24012007,12:51:55,01413412066,01,FreeConnect,00:00:14,0:14,0.233,0.006,2006,X
08009700000,24,01,2007,24012007,12:52:28,01413412066,01,FreeConnect,00:00:10,0:10,0.167,0.004,2006,X

the format file look like this

8.0
15
1 SQLNCHAR 2 510 "," 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 2 510 "," 2 Field2 Latin1_General_CI_AS
3 SQLNCHAR 2 510 "," 3 Field3 Latin1_General_CI_AS
4 SQLNCHAR 2 510 "," 4 Field4 Latin1_General_CI_AS
5 SQLNCHAR 2 510 "," 5 Field5 Latin1_General_CI_AS
6 SQLNCHAR 2 510 "," 6 Field6 Latin1_General_CI_AS
7 SQLNCHAR 2 510 "," 7 Field7 Latin1_General_CI_AS
8 SQLNCHAR 2 510 "," 8 Field8 Latin1_General_CI_AS
9 SQLNCHAR 2 510 "," 9 Field9 Latin1_General_CI_AS
10 SQLNCHAR 2 510 "," 10 Field10 Latin1_General_CI_AS
11 SQLNCHAR 2 510 "," 11 Field11 Latin1_General_CI_AS
12 SQLNCHAR 2 510 "," 12 Field12 Latin1_General_CI_AS
13 SQLNCHAR 2 510 "," 13 Field13 Latin1_General_CI_AS
14 SQLNCHAR 2 510 "," 14 Field14 Latin1_General_CI_AS
15 SQLNCHAR 2 510 "," 15 Field15 Latin1_General_CI_AS

and the T-SQL code that i am using is

bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt')

i have also tried

bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt',ROWTERMINATOR = '\r\n')

and

bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt',ROWTERMINATOR = '\r')

what ever i do i get the get the follow error message

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.

can any one help ?

I haven't read through your post completely, because I'm too busy today. But...

In Enterprise Manager (or Management Studio), use the 'Import Data' menu entry to go through a wizard to import your data. Use the option to save the package, or in MgtStudio, script it out. Then you can re-use the package/script when you need to. But hopefully you will find the answer to your problem when you step through the wizard and it tells you useful information like "I can't find the end of the line 10 lines in...".

Actually - having skimmed through, it sounds like it can't find the commas properly, or else your columns are too short. Probably the first though, because I imagine you would've noticed if your columns were short. The wizard should really help you here.

Hope this helps,

Rob|||

hi and thanks for the reply.

I have been playing around with this and i have now got round the error that i was getting.

The problem seems to be in the format file that i am using, which is odd cause it was genarated using the bcp.

I found 2 problems in this file

the first which caused the error about the first column being two long was the column prefix was set to 2, when i changed this to 0 for each column the error went away.

then then got a new error about unexpected end of line, I solved this buy removing the comma on the last line of the format file and replaceing it with \r\n

so my format file now looks like this

8.0

15
1 SQLNCHAR 0 510 "," 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 0 510 "," 2 Field2 Latin1_General_CI_AS

...

15 SQLNCHAR 0 510 "\r\n" 15 Field15 Latin1_General_CI_AS

it now imports all the data, however it also seems to corrupt the data, if i do a select statement in QA the data looks like it is in the wrong font as it is all white empty boxes.

any one know why this happens ?

|||Dagz,

You got two of three issues answered, it looks like (changing the 2 to 0 and changing the final field terminator). If your file is Unicode, though, which I'm guessing it is from the SQLNCHAR in the format file, I think you need to specify the field terminators differently. Try this as the format file (or try SQLCHAR instead of SQLNCHAR, if your file is not a Unicode file):

8.0
15
1 SQLNCHAR 0 510 ",\x00" 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 0 510 ",\x00" 2 Field2 Latin1_General_CI_AS
3 SQLNCHAR 0 510 ",\x00" 3 Field3 Latin1_General_CI_AS
4 SQLNCHAR 0 510 ",\x00" 4 Field4 Latin1_General_CI_AS
5 SQLNCHAR 0 510 ",\x00" 5 Field5 Latin1_General_CI_AS
6 SQLNCHAR 0 510 ",\x00" 6 Field6 Latin1_General_CI_AS
7 SQLNCHAR 0 510 ",\x00" 7 Field7 Latin1_General_CI_AS
8 SQLNCHAR 0 510 ",\x00" 8 Field8 Latin1_General_CI_AS
9 SQLNCHAR 0 510 ",\x00" 9 Field9 Latin1_General_CI_AS
10 SQLNCHAR 0 510 ",\x00" 10 Field10 Latin1_General_CI_AS
11 SQLNCHAR 0 510 ",\x00" 11 Field11 Latin1_General_CI_AS
12 SQLNCHAR 0 510 ",\x00" 12 Field12 Latin1_General_CI_AS
13 SQLNCHAR 0 510 ",\x00" 13 Field13 Latin1_General_CI_AS
14 SQLNCHAR 0 510 ",\x00" 14 Field14 Latin1_General_CI_AS
15 SQLNCHAR 0 510 "\x0D\x00\x0A\x00" 15 Field15 Latin1_General_CI_AS

Steve Kass
http://www.stevekass.com

Bulk insert problem

Hi!
I have done bcp out using -V and -q switch from one table and using only -V
switch on another table on the server running on sql server 2000. the
datatype used is native i.e. -n switch.
When I am trying to import it using BCP IN in the server having same table
structures running on sql server 7.0, process is completing successfully. Bu
t
I want to use Bulk Insert and its not importing data on sql server 2000
rather throwing error.
ref:
truncate table rplctn_cnt_lg
Bulk insert rplctn_cnt_lg from 'c:\rpl\11072CNT.txt' with
(DATAFILETYPE='NATIVE')
Error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
The statement has been terminated.
Kindly help me with this solution. We have one project in hand where the
replication uses BCP and Bulk Insert combination. No of tables affected more
than 200. No. of sites more than 1000 where sql server 7.0/msde is there. On
e
master database running on sql server 2000.
TIA... Regards,
Piyush AgarwalHi
Assuming you are using the correct version number with the -V flags, you may
want to check out:
http://tinyurl.com/6fg44
John
"Piyush" wrote:

> Hi!
> I have done bcp out using -V and -q switch from one table and using only -
V
> switch on another table on the server running on sql server 2000. the
> datatype used is native i.e. -n switch.
> When I am trying to import it using BCP IN in the server having same table
> structures running on sql server 7.0, process is completing successfully.
But
> I want to use Bulk Insert and its not importing data on sql server 2000
> rather throwing error.
> ref:
> truncate table rplctn_cnt_lg
> Bulk insert rplctn_cnt_lg from 'c:\rpl\11072CNT.txt' with
> (DATAFILETYPE='NATIVE')
> Error
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> The statement has been terminated.
> Kindly help me with this solution. We have one project in hand where the
> replication uses BCP and Bulk Insert combination. No of tables affected mo
re
> than 200. No. of sites more than 1000 where sql server 7.0/msde is there.
One
> master database running on sql server 2000.
> TIA... Regards,
> Piyush Agarwal
>

BULK INSERT Performance with format files

Hi,
We have a scenario where we want to bulk insert data into only a select
number of columns in a table.
We are currently generating a BCP file with
- data for all columns, NULL in case of columns which are not needed (These
columns are nullable in the table)
- column data is ordered as it is in the table.
We use the BULK INSERT statement.
Now, we want to use a format file and write data (into the bcp file) for
only those columns which we need. This will definitely give us a faster bcp
file creation process.
However we want to know whether using a format file will slow down the "bulk
insert" itself. Also, if there are any other issues with format files.
Thanks,
NitinAlternatively try creating a view on your
table that selects only the columns you want to insert into.
You should then be able to bulk insert directly into this view
without a format file.|||Alternatively try creating a view on your
table that selects only the columns you want to insert into.
You should then be able to bulk insert directly into this view
without a format file.|||"Nitin M" <nitin@.nowhere.com> wrote in message
news:ugKwd8UAGHA.264@.tk2msftngp13.phx.gbl...
> Hi,
> We have a scenario where we want to bulk insert data into only a select
> number of columns in a table.
> We are currently generating a BCP file with
> - data for all columns, NULL in case of columns which are not needed
> (These columns are nullable in the table)
> - column data is ordered as it is in the table.
> We use the BULK INSERT statement.
> Now, we want to use a format file and write data (into the bcp file) for
> only those columns which we need. This will definitely give us a faster
> bcp file creation process.
> However we want to know whether using a format file will slow down the
> "bulk insert" itself. Also, if there are any other issues with format
> files.
> Thanks,
> Nitin
>
We use this successfully where I work all of the time. I have a large flat
file (about 4 GB) that has each row with a length of about 2000 bytes. We
only need about 500 of the bytes from various positions within that file.
We use a format file and can pull in the 3 million records in about 15
minutes on our test system.
One note.. Setting a ROWS_PER_BATCH size to about 1000 seemed to speed up
the process a lot for our usage.
Rick Sawtell
MCT, MCSD, MCDBAsql

BULK INSERT old BCP format files in SQL 2005

I am not able to BULK INSERT data files in SQL 2005 because it is
complaining about the format of the format description file (.fmt) (it says
something about invalid xml character ...) I am aware of the new XML format
definition and obviously by default SQL assumes that the format file
specified in the BULK INSERT is the new XML one. Everything works fine if I
use command line BCP utility which allows me to specify the format of the
format file. How do I specify it with BULK INSERT statement?
//MishaYou can specify the format file with BULK INSERT statement.
Format description in BOL:
BULK INSERT
[ database_name . [ schema_name ] . | schema_name . ] [ table_name |
view_name ]
FROM 'data_file'
[ WITH
(
[ [ , ] BATCHSIZE = batch_size ]
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ [ , ] DATAFILETYPE = { 'char' | 'native'| 'widechar' | 'widenative' } ]
[ [ , ] FIELDTERMINATOR = 'field_terminator' ]
[ [ , ] FIRSTROW =first_row ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] KEEPIDENTITY ]
[ [ , ] KEEPNULLS ]
[ [ , ] KILOBYTES_PER_BATCH =kilobytes_per_batch ]
[ [ , ] LASTROW = last_row ]
[ [ , ] MAXERRORS = max_errors ]
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
[ [ , ] ROWS_PER_BATCH = rows_per_batch ]
[ [ , ] ROWTERMINATOR = 'row_terminator' ]
[ [ , ] TABLOCK ]
[ [ , ] ERRORFILE = 'file_name' ]
)]
Q
"Misha" wrote:
> I am not able to BULK INSERT data files in SQL 2005 because it is
> complaining about the format of the format description file (.fmt) (it says
> something about invalid xml character ...) I am aware of the new XML format
> definition and obviously by default SQL assumes that the format file
> specified in the BULK INSERT is the new XML one. Everything works fine if I
> use command line BCP utility which allows me to specify the format of the
> format file. How do I specify it with BULK INSERT statement?
> //Misha
>
>

bulk insert much to slower on SQL2k5 in compare with SQL2k.

To understand the problem that I have, I will exemplify but the problem it is general in the SQL2k5.

So any BCP insert it is slower with the SQL2k5 in compare with the SQL2k, doenst matter the edition of SQL2k5.

1. First test environment comparing Enterprise versions of SQL2k and SQL2k5 with memory seted 4GB RAM available on the computer it is 6GB Ram(awe enabled), Windows Server 2003 R2 SP1(this window version was special installed because I got the Info that can work better with the SQL2k5).Changing the allocated memory for the SQL Server makes no difference because the SQL Server need only maximum 200MB Ram for the import.

I was installing the system database from the both system on the same hardisk and the client database on the second harddisk.

I was creating a table with this query:

create table T02
(
autoid int identity (1, 1) not null, --identity (1, 1)
intValue int null,
floatValue float null,
stringValue varchar(255) null
) on [primary]
go

and I was generating a .dat file with 1 milion rows to be imported with the bulk insert Transact-SQL command.

1,1,0.002220,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 1
2,2,0.239019,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 2
3,3,1.219092,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 3
4,4,0.390967,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 4
5,5,1.102109,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 5
6,1,0.202700,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 6
7,2,1.383550,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 7
8,3,20.287169,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 8
9,4,0.250796,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 9
10,5,0.887969,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 10

This are the first 10 lines from the file. You can duplicate this line to obtain the milion lines file.

Now the next step was stopping SQL2k and starting new the SQL2k5 services(no cache, no memory already allocated).

Running the next import command:

bulk insert T02 from 'D:\bulk_insert.dat' with (fieldterminator=',')

obtaining the next time results: ~ 17 seconds.

The next step was stopping SQL2k5 and starting new the SQL2k services.

the same command the the time was about 11 seconds.

2. Second test environment comparing Standard versions of SQL2k and SQL2k5 with memory seted 2GB RAM available on the computer it is 4GB Ram, Windows 2000 SP4(a different computer as the first time).Changing the allocated memory for the SQL Server makes no difference because the SQL Server need only maximum 200MB Ram for the import.

I was repeting the tests before and getting the next results:

With SQL2k about 12 seconds.

With SQL2k5 about 20 seconds.

For me this performance it is not acceptable becuase some parts of my application are using the BCP Inserts as an optimize method and when this optimzed parts it is running slower the effect are very strong.

At the end the application it is about 25% slower with Sql2k5 as with Sql2k.

As I sayd at the beginning this problem it is general I can attach some tests programs thats are using the BCP Insert from the DB Library of OLE DB Consumer Templates from ATL. I've choosed this example just becuase it is easear to reproduce.

This programs are having the same procent of performance lost. As the Transact SQL Command the programs are about 50% slower with SQL2k5 as with SQL2k.

Please help me if you know any solution to optimize BCP Inserts on SQL2k5.

Vasile Mirea, Diplom Computer Science.

To what I already sayd I will add some remarks about the tests.

The Sql Servers 2005 had the Service Pack 1 already installed and the SQL2k the service pack 4.

About the tests using the Perfmon tool from windows I saw that the Sql2k5 it is using excesiv the hardware in compare with 2k that it is using only at the beggining strong and after that only little bit the hard work doing it the CPU's.

So seams that are important implementation difference between the versions in Sql Server but unfortunatly not positives ones(for the performance).

Another important fact was the memory used by the SQL Server 2k and SQL Server 2k5 the 2k it is using about 250MBRam and 2k5 it is using 150MBRam for the same BCP Import. This means that Microsoft tryed to improved the memory use but paying with performance.

I can send in an email the test data file and the print screens with the perfmon from the Sql Server.

So if everybody can help I wait your answear.

Thanks,

Vasile.

|||Vasile,

I can think of a few possibilities. #1: For SQL Server 2005, BULK INSERT enforces data validation more carefully, and this enhancement may result in a performance penalty:

http://msdn2.microsoft.com/en-us/library/ms188365.aspx
"In SQL Server 2005, BULK INSERT enforces new data validation and data checks

that could cause existing scripts to fail when they are executed on invalid data

in a data file."

#2: If your SQL Server 2000 and 2005 instances do not use the same character collation., perhaps code page translation is causing more work in 2005?

#3: This might be most noticeable for the first BULK INSERT after starting the server, if some library code must be loaded initially.

If you cannot improve BULK INSERT performance with the parameters available to you (ROWS_PER_BATCH, for example), you will have to decide how serious this slowdown is relative to the benefits of upgrading to 2005. Since you are not seeing a dramatic slowdown (you are not seeing the insert take twice as long or more), improvements in hardware in just a few months may overcome the difference.

Steve Kass
Drew University
www.stevekass.com|||

Hi Steve,

So I've checked the possibilities that you gave me.

#3 I've repeated the test until 10 times on every server the times stays the same(without restarting the server). So for the second time the libraryes must be already loaded. This new tests should exclude this possibilities.

#2 I've checked the collation and on the both servers was the same: Latin1_General_CI_AS the default collation for the west Europe. Collation excluded, for sure still can be that the collation operators are new implemented but this I dont think it is the reason because this should produce a performance problem for the select queryes with a order list too. So collation excluded.

#1 I think this one can be the problem as I supposed too, a different implementation. Not necesary a "new data validation " can be something else too.

But this should be a Microsoft problem. I hope somebody will find this performance not acceptable and in the SP2 from SQL Server 2005 will come with a fix.

Or with some option for BCP Insert like "BCP data validation inactive", exactly as it is for the log BCP.

50% less performant for a query like BCP Insert it should not be ok for Microsoft.

Still looking for a solution to improve the performance. In our case the application it runs about 12 hours so 20% it is a lot.

|||

Hi Steve,

I made some research of my own.

Asking one consultant from Microsoft that was having direct access to the SQL Server development team I got a nice answear.

It seams that in the SQL Server 2005 implementation are some new layers to optimize the BCP Insert.

But this new layers can slow a normal BCP Insert on a table with lets say 20 fields(The size of a row it is not big enought).

If the size of the row it is bigger then a limit(i dont know the limit exactly) then the BCP Insert with SQL Server 2005 can be faster then in the SQL Server 2000.

We opened a ticket to Microsoft-Support to be sure and we got the answear: Slow by Design! (For our case of course)

This means for a normal case for lets say normal tables that dont have images or bulk fields then the new implementation of the SQL Server 2005 bulk Insert it is slower.

Are my research results true ?

I wait your answear,

Thanks, Vasile.

|||

In SQL Server 2005 some of the bulk load code indeed was re-architected. However, for the most common scenarios it did not cause any measurable performance slowdowns and instead many 'normal' scenarios run faster than in SQL Server 2000. There are some corner cases though that may run slower, however your scenario certainly doesn't look like one that should experience performance problems when moving from SQL Server 2000 to SQL Server 2005 provided that you make use of the bulk load optimizations.

Please make sure that your database recovery mode is either SIMPLE or BULK_LOGGED and make sure that you specify the TABLOCK option when issuing the BULK INSERT statement. You may as well want to consider using the CODEPAGE='RAW' to bypass the CP translation code if you know that it would be safe for the particular data you are loading.

Please also note that the actual performance of the BULK INSERT statement may depend on the specifics of the test scenario that expose some other differences in the SQL Server 2005 behavior - for instance: do you truncate or drop/re-create the table before repeating the bulk loads or you just keep appending the data to the table? Do you issue a checkpoint before/after the BULK INSERT statement?

bulk insert much to slower on SQL2k5 in compare with SQL2k.

To understand the problem that I have, I will exemplify but the problem it is general in the SQL2k5.

So any BCP insert it is slower with the SQL2k5 in compare with the SQL2k, doenst matter the edition of SQL2k5.

1. First test environment comparing Enterprise versions of SQL2k and SQL2k5 with memory seted 4GB RAM available on the computer it is 6GB Ram(awe enabled), Windows Server 2003 R2 SP1(this window version was special installed because I got the Info that can work better with the SQL2k5).Changing the allocated memory for the SQL Server makes no difference because the SQL Server need only maximum 200MB Ram for the import.

I was installing the system database from the both system on the same hardisk and the client database on the second harddisk.

I was creating a table with this query:

create table T02
(
autoid int identity (1, 1) not null, --identity (1, 1)
intValue int null,
floatValue float null,
stringValue varchar(255) null
) on [primary]
go

and I was generating a .dat file with 1 milion rows to be imported with the bulk insert Transact-SQL command.

1,1,0.002220,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 1
2,2,0.239019,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 2
3,3,1.219092,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 3
4,4,0.390967,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 4
5,5,1.102109,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 5
6,1,0.202700,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 6
7,2,1.383550,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 7
8,3,20.287169,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 8
9,4,0.250796,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 9
10,5,0.887969,Dies ist eine nicht ganz so zuf?llige Zufallszahl: 10

This are the first 10 lines from the file. You can duplicate this line to obtain the milion lines file.

Now the next step was stopping SQL2k and starting new the SQL2k5 services(no cache, no memory already allocated).

Running the next import command:

bulk insert T02 from 'D:\bulk_insert.dat' with (fieldterminator=',')

obtaining the next time results: ~ 17 seconds.

The next step was stopping SQL2k5 and starting new the SQL2k services.

the same command the the time was about 11 seconds.

2. Second test environment comparing Standard versions of SQL2k and SQL2k5 with memory seted 2GB RAM available on the computer it is 4GB Ram, Windows 2000 SP4(a different computer as the first time).Changing the allocated memory for the SQL Server makes no difference because the SQL Server need only maximum 200MB Ram for the import.

I was repeting the tests before and getting the next results:

With SQL2k about 12 seconds.

With SQL2k5 about 20 seconds.

For me this performance it is not acceptable becuase some parts of my application are using the BCP Inserts as an optimize method and when this optimzed parts it is running slower the effect are very strong.

At the end the application it is about 25% slower with Sql2k5 as with Sql2k.

As I sayd at the beginning this problem it is general I can attach some tests programs thats are using the BCP Insert from the DB Library of OLE DB Consumer Templates from ATL. I've choosed this example just becuase it is easear to reproduce.

This programs are having the same procent of performance lost. As the Transact SQL Command the programs are about 50% slower with SQL2k5 as with SQL2k.

Please help me if you know any solution to optimize BCP Inserts on SQL2k5.

Vasile Mirea, Diplom Computer Science.

To what I already sayd I will add some remarks about the tests.

The Sql Servers 2005 had the Service Pack 1 already installed and the SQL2k the service pack 4.

About the tests using the Perfmon tool from windows I saw that the Sql2k5 it is using excesiv the hardware in compare with 2k that it is using only at the beggining strong and after that only little bit the hard work doing it the CPU's.

So seams that are important implementation difference between the versions in Sql Server but unfortunatly not positives ones(for the performance).

Another important fact was the memory used by the SQL Server 2k and SQL Server 2k5 the 2k it is using about 250MBRam and 2k5 it is using 150MBRam for the same BCP Import. This means that Microsoft tryed to improved the memory use but paying with performance.

I can send in an email the test data file and the print screens with the perfmon from the Sql Server.

So if everybody can help I wait your answear.

Thanks,

Vasile.

|||Vasile,

I can think of a few possibilities. #1: For SQL Server 2005, BULK INSERT enforces data validation more carefully, and this enhancement may result in a performance penalty:

http://msdn2.microsoft.com/en-us/library/ms188365.aspx
"In SQL Server 2005, BULK INSERT enforces new data validation and data checks

that could cause existing scripts to fail when they are executed on invalid data

in a data file."

#2: If your SQL Server 2000 and 2005 instances do not use the same character collation., perhaps code page translation is causing more work in 2005?

#3: This might be most noticeable for the first BULK INSERT after starting the server, if some library code must be loaded initially.

If you cannot improve BULK INSERT performance with the parameters available to you (ROWS_PER_BATCH, for example), you will have to decide how serious this slowdown is relative to the benefits of upgrading to 2005. Since you are not seeing a dramatic slowdown (you are not seeing the insert take twice as long or more), improvements in hardware in just a few months may overcome the difference.

Steve Kass
Drew University
www.stevekass.com|||

Hi Steve,

So I've checked the possibilities that you gave me.

#3 I've repeated the test until 10 times on every server the times stays the same(without restarting the server). So for the second time the libraryes must be already loaded. This new tests should exclude this possibilities.

#2 I've checked the collation and on the both servers was the same: Latin1_General_CI_AS the default collation for the west Europe. Collation excluded, for sure still can be that the collation operators are new implemented but this I dont think it is the reason because this should produce a performance problem for the select queryes with a order list too. So collation excluded.

#1 I think this one can be the problem as I supposed too, a different implementation. Not necesary a "new data validation " can be something else too.

But this should be a Microsoft problem. I hope somebody will find this performance not acceptable and in the SP2 from SQL Server 2005 will come with a fix.

Or with some option for BCP Insert like "BCP data validation inactive", exactly as it is for the log BCP.

50% less performant for a query like BCP Insert it should not be ok for Microsoft.

Still looking for a solution to improve the performance. In our case the application it runs about 12 hours so 20% it is a lot.

|||

Hi Steve,

I made some research of my own.

Asking one consultant from Microsoft that was having direct access to the SQL Server development team I got a nice answear.

It seams that in the SQL Server 2005 implementation are some new layers to optimize the BCP Insert.

But this new layers can slow a normal BCP Insert on a table with lets say 20 fields(The size of a row it is not big enought).

If the size of the row it is bigger then a limit(i dont know the limit exactly) then the BCP Insert with SQL Server 2005 can be faster then in the SQL Server 2000.

We opened a ticket to Microsoft-Support to be sure and we got the answear: Slow by Design! (For our case of course)

This means for a normal case for lets say normal tables that dont have images or bulk fields then the new implementation of the SQL Server 2005 bulk Insert it is slower.

Are my research results true ?

I wait your answear,

Thanks, Vasile.

|||

In SQL Server 2005 some of the bulk load code indeed was re-architected. However, for the most common scenarios it did not cause any measurable performance slowdowns and instead many 'normal' scenarios run faster than in SQL Server 2000. There are some corner cases though that may run slower, however your scenario certainly doesn't look like one that should experience performance problems when moving from SQL Server 2000 to SQL Server 2005 provided that you make use of the bulk load optimizations.

Please make sure that your database recovery mode is either SIMPLE or BULK_LOGGED and make sure that you specify the TABLOCK option when issuing the BULK INSERT statement. You may as well want to consider using the CODEPAGE='RAW' to bypass the CP translation code if you know that it would be safe for the particular data you are loading.

Please also note that the actual performance of the BULK INSERT statement may depend on the specifics of the test scenario that expose some other differences in the SQL Server 2005 behavior - for instance: do you truncate or drop/re-create the table before repeating the bulk loads or you just keep appending the data to the table? Do you issue a checkpoint before/after the BULK INSERT statement?

BULK INSERT messes up IDENTITY value

I use bcp to copy out a table and BULK INSERT..WITH (KEEP IDENTITY) to copy
in after restoring the database anew and truncate that table. The identity
column is numeric (9, 0) IDENTITY (1, 1). This column also has CONSTRAINT
PRIMARY KEY CLUSTERED.
The transfer was complete, the last row was 4 digit; but when new rows are
inserted values jump to 9 digit.
Help! Gracias.
LaEsmeralda
Ok, if the Identity is not used as reference to Other Tables , i mean
in Query to check data across tables and if there is not harm in
deleting the column, then Drop and identity Column and Recreate it
again then apply the identity to it and LO! it all back again.
Maninder
MCDBA
On Jan 23, 11:43 am, LaEsmeralda
<LaEsmera...@.discussions.microsoft.com> wrote:
> I use bcp to copy out a table and BULK INSERT..WITH (KEEP IDENTITY) to copy
> in after restoring the database anew and truncate that table. The identity
> column is numeric (9, 0) IDENTITY (1, 1). This column also has CONSTRAINT
> PRIMARY KEY CLUSTERED.
> The transfer was complete, the last row was 4 digit; but when new rows are
> inserted values jump to 9 digit.
> Help! Gracias.
> LaEsmeralda

BULK INSERT messes up IDENTITY value

I use bcp to copy out a table and BULK INSERT..WITH (KEEP IDENTITY) to copy
in after restoring the database anew and truncate that table. The identity
column is numeric (9, 0) IDENTITY (1, 1). This column also has CONSTRAINT
PRIMARY KEY CLUSTERED.
The transfer was complete, the last row was 4 digit; but when new rows are
inserted values jump to 9 digit.
Help! Gracias.
LaEsmeraldaOk, if the Identity is not used as reference to Other Tables , i mean
in Query to check data across tables and if there is not harm in
deleting the column, then Drop and identity Column and Recreate it
again then apply the identity to it and LO! it all back again.
Maninder
MCDBA
On Jan 23, 11:43 am, LaEsmeralda
<LaEsmera...@.discussions.microsoft.com> wrote:
> I use bcp to copy out a table and BULK INSERT..WITH (KEEP IDENTITY) to cop
y
> in after restoring the database anew and truncate that table. The identit
y
> column is numeric (9, 0) IDENTITY (1, 1). This column also has CONSTRAINT
> PRIMARY KEY CLUSTERED.
> The transfer was complete, the last row was 4 digit; but when new rows are
> inserted values jump to 9 digit.
> Help! Gracias.
> LaEsmeralda

BULK INSERT in 2005 does not understand old BCP format file (cretaed in 2000)

I am not able to BULK INSERT data files in SQL 2005 because it is
complaining about the format of the format description file (.fmt) (it says
something about invalid xml character ...) I am aware of the new XML format
definition and obviously by default SQL assumes that the format file
specified in the BULK INSERT is the new XML one. Everything works fine if I
use command line BCP utility which allows me to specify the format of the
format file. How do I specify it with BULK INSERT statement?
//MishaMisha,
Could you please show the first 5 lines of the format file? Maybe seeing it
would give a clue.
RLF
"Misha" <mishka_mishka@.inbox.ru> wrote in message
news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>I am not able to BULK INSERT data files in SQL 2005 because it is
> complaining about the format of the format description file (.fmt) (it
> says
> something about invalid xml character ...) I am aware of the new XML
> format
> definition and obviously by default SQL assumes that the format file
> specified in the BULK INSERT is the new XML one. Everything works fine if
> I
> use command line BCP utility which allows me to specify the format of the
> format file. How do I specify it with BULK INSERT statement?
> //Misha
>|||Here are the first 5 lines of my .fmt file:
--cut here--
8.0
26
1 SQLCHAR 0 4 " " 0 Timestamp ""
2 SQLCHAR 0 8000 " " 1 Company ""
--cut here--
The same lines in HEX:
00000000: 38 2E 30 0D 0A 32 36 0D ? 0A 31 09 53 51 4C 43 48
8.0
26
1 SQLCH
00000010: 41 52 09 30 09 34 09 22 ? 07 22 09 30 09 54 69 6D
AR 0 4 " " 0 Tim
00000020: 65 73 74 61 6D 70 09 22 ? 22 0D 0A 32 09 53 51 4C
estamp ""
2 SQL
00000030: 43 48 41 52 09 30 09 38 ? 30 30 30 09 22 07 22 09
CHAR 0 8000 " "
00000040: 31 09 43 6F 6D 70 61 6E ? 79 09 22 22 0D 0A 33 09
1 Company ""
3
//Misha
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23vOhYw8dIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Misha,
> Could you please show the first 5 lines of the format file? Maybe seeing
> it would give a clue.
> RLF
> "Misha" <mishka_mishka@.inbox.ru> wrote in message
> news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I am not able to BULK INSERT data files in SQL 2005 because it is
>> complaining about the format of the format description file (.fmt) (it
>> says
>> something about invalid xml character ...) I am aware of the new XML
>> format
>> definition and obviously by default SQL assumes that the format file
>> specified in the BULK INSERT is the new XML one. Everything works fine if
>> I
>> use command line BCP utility which allows me to specify the format of the
>> format file. How do I specify it with BULK INSERT statement?
>> //Misha
>|||Misha,
I really do not know the answer. I also get XML errors on "invalid
character", but when I replace your separator character with a "," it all
works. (I tried saving your .fmt file as both ANSI and UNICODE. I get
different errors, but I still get errors.)
It makes me wonder if the format file, when being read is turned into
internal XML and the XML does not allow your separator character (which is
0x07) to exist.
Can you readily change to another separator character?
FWIW,
RLF
"Misha" <mishka_mishka@.inbox.ru> wrote in message
news:%23VY38aEeIHA.5984@.TK2MSFTNGP06.phx.gbl...
> Here are the first 5 lines of my .fmt file:
> --cut here--
> 8.0
> 26
> 1 SQLCHAR 0 4 " " 0 Timestamp ""
> 2 SQLCHAR 0 8000 " " 1 Company ""
> --cut here--
> The same lines in HEX:
> 00000000: 38 2E 30 0D 0A 32 36 0D ? 0A 31 09 53 51 4C 43 48 8.0
> 26
> 1 SQLCH
> 00000010: 41 52 09 30 09 34 09 22 ? 07 22 09 30 09 54 69 6D AR 0 4 " " 0
> Tim
> 00000020: 65 73 74 61 6D 70 09 22 ? 22 0D 0A 32 09 53 51 4C estamp ""
> 2 SQL
> 00000030: 43 48 41 52 09 30 09 38 ? 30 30 30 09 22 07 22 09 CHAR 0 8000
> " " 00000040: 31 09 43 6F 6D 70 61 6E ? 79 09 22 22 0D 0A 33 09 1 Company
> ""
> 3
> //Misha
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23vOhYw8dIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Misha,
>> Could you please show the first 5 lines of the format file? Maybe seeing
>> it would give a clue.
>> RLF
>> "Misha" <mishka_mishka@.inbox.ru> wrote in message
>> news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I am not able to BULK INSERT data files in SQL 2005 because it is
>> complaining about the format of the format description file (.fmt) (it
>> says
>> something about invalid xml character ...) I am aware of the new XML
>> format
>> definition and obviously by default SQL assumes that the format file
>> specified in the BULK INSERT is the new XML one. Everything works fine
>> if I
>> use command line BCP utility which allows me to specify the format of
>> the
>> format file. How do I specify it with BULK INSERT statement?
>> //Misha
>>
>|||Thanks a lot for your help Russell,
I have switched the application onto the new XML format file format and it
works fine. Obviously MS has a bug in SQL 2005 with the old fmt files.
//Misha
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23%23jgRkJeIHA.288@.TK2MSFTNGP02.phx.gbl...
> Misha,
> I really do not know the answer. I also get XML errors on "invalid
> character", but when I replace your separator character with a "," it all
> works. (I tried saving your .fmt file as both ANSI and UNICODE. I get
> different errors, but I still get errors.)
> It makes me wonder if the format file, when being read is turned into
> internal XML and the XML does not allow your separator character (which is
> 0x07) to exist.
> Can you readily change to another separator character?
> FWIW,
> RLF
>
> "Misha" <mishka_mishka@.inbox.ru> wrote in message
> news:%23VY38aEeIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Here are the first 5 lines of my .fmt file:
>> --cut here--
>> 8.0
>> 26
>> 1 SQLCHAR 0 4 " " 0 Timestamp ""
>> 2 SQLCHAR 0 8000 " " 1 Company ""
>> --cut here--
>> The same lines in HEX:
>> 00000000: 38 2E 30 0D 0A 32 36 0D ? 0A 31 09 53 51 4C 43 48 8.0
>> 26
>> 1 SQLCH
>> 00000010: 41 52 09 30 09 34 09 22 ? 07 22 09 30 09 54 69 6D AR 0 4 " " 0
>> Tim
>> 00000020: 65 73 74 61 6D 70 09 22 ? 22 0D 0A 32 09 53 51 4C estamp ""
>> 2 SQL
>> 00000030: 43 48 41 52 09 30 09 38 ? 30 30 30 09 22 07 22 09 CHAR 0 8000
>> " " 00000040: 31 09 43 6F 6D 70 61 6E ? 79 09 22 22 0D 0A 33 09 1
>> Company ""
>> 3
>> //Misha
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:%23vOhYw8dIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Misha,
>> Could you please show the first 5 lines of the format file? Maybe
>> seeing it would give a clue.
>> RLF
>> "Misha" <mishka_mishka@.inbox.ru> wrote in message
>> news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I am not able to BULK INSERT data files in SQL 2005 because it is
>> complaining about the format of the format description file (.fmt) (it
>> says
>> something about invalid xml character ...) I am aware of the new XML
>> format
>> definition and obviously by default SQL assumes that the format file
>> specified in the BULK INSERT is the new XML one. Everything works fine
>> if I
>> use command line BCP utility which allows me to specify the format of
>> the
>> format file. How do I specify it with BULK INSERT statement?
>> //Misha
>>
>>
>

BULK INSERT in 2005 does not understand old BCP format file (cretaed in 2000)

I am not able to BULK INSERT data files in SQL 2005 because it is
complaining about the format of the format description file (.fmt) (it says
something about invalid xml character ...) I am aware of the new XML format
definition and obviously by default SQL assumes that the format file
specified in the BULK INSERT is the new XML one. Everything works fine if I
use command line BCP utility which allows me to specify the format of the
format file. How do I specify it with BULK INSERT statement?
//Misha
Misha,
Could you please show the first 5 lines of the format file? Maybe seeing it
would give a clue.
RLF
"Misha" <mishka_mishka@.inbox.ru> wrote in message
news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>I am not able to BULK INSERT data files in SQL 2005 because it is
> complaining about the format of the format description file (.fmt) (it
> says
> something about invalid xml character ...) I am aware of the new XML
> format
> definition and obviously by default SQL assumes that the format file
> specified in the BULK INSERT is the new XML one. Everything works fine if
> I
> use command line BCP utility which allows me to specify the format of the
> format file. How do I specify it with BULK INSERT statement?
> //Misha
>
|||Here are the first 5 lines of my .fmt file:
--cut here--
8.0
26
1 SQLCHAR 0 4 " " 0 Timestamp ""
2 SQLCHAR 0 8000 " " 1 Company ""
--cut here--
The same lines in HEX:
00000000: 38 2E 30 0D 0A 32 36 0D 0A 31 09 53 51 4C 43 48
8.0
26
1SQLCH
00000010: 41 52 09 30 09 34 09 22 07 22 09 30 09 54 69 6D
AR04" "0Tim
00000020: 65 73 74 61 6D 70 09 22 22 0D 0A 32 09 53 51 4C
estamp""
2SQL
00000030: 43 48 41 52 09 30 09 38 30 30 30 09 22 07 22 09
CHAR08000" "
00000040: 31 09 43 6F 6D 70 61 6E 79 09 22 22 0D 0A 33 09
1Company""
3
//Misha
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23vOhYw8dIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Misha,
> Could you please show the first 5 lines of the format file? Maybe seeing
> it would give a clue.
> RLF
> "Misha" <mishka_mishka@.inbox.ru> wrote in message
> news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>
|||Misha,
I really do not know the answer. I also get XML errors on "invalid
character", but when I replace your separator character with a "," it all
works. (I tried saving your .fmt file as both ANSI and UNICODE. I get
different errors, but I still get errors.)
It makes me wonder if the format file, when being read is turned into
internal XML and the XML does not allow your separator character (which is
0x07) to exist.
Can you readily change to another separator character?
FWIW,
RLF
"Misha" <mishka_mishka@.inbox.ru> wrote in message
news:%23VY38aEeIHA.5984@.TK2MSFTNGP06.phx.gbl...
> Here are the first 5 lines of my .fmt file:
> --cut here--
> 8.0
> 26
> 1 SQLCHAR 0 4 " " 0 Timestamp ""
> 2 SQLCHAR 0 8000 " " 1 Company ""
> --cut here--
> The same lines in HEX:
> 00000000: 38 2E 30 0D 0A 32 36 0D 0A 31 09 53 51 4C 43 48 8.0
> 26
> 1 SQLCH
> 00000010: 41 52 09 30 09 34 09 22 07 22 09 30 09 54 69 6D AR 0 4 " " 0
> Tim
> 00000020: 65 73 74 61 6D 70 09 22 22 0D 0A 32 09 53 51 4C estamp ""
> 2 SQL
> 00000030: 43 48 41 52 09 30 09 38 30 30 30 09 22 07 22 09 CHAR 0 8000
> " " 00000040: 31 09 43 6F 6D 70 61 6E 79 09 22 22 0D 0A 33 09 1 Company
> ""
> 3
> //Misha
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23vOhYw8dIHA.3756@.TK2MSFTNGP06.phx.gbl...
>
|||Thanks a lot for your help Russell,
I have switched the application onto the new XML format file format and it
works fine. Obviously MS has a bug in SQL 2005 with the old fmt files.
//Misha
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23%23jgRkJeIHA.288@.TK2MSFTNGP02.phx.gbl...
> Misha,
> I really do not know the answer. I also get XML errors on "invalid
> character", but when I replace your separator character with a "," it all
> works. (I tried saving your .fmt file as both ANSI and UNICODE. I get
> different errors, but I still get errors.)
> It makes me wonder if the format file, when being read is turned into
> internal XML and the XML does not allow your separator character (which is
> 0x07) to exist.
> Can you readily change to another separator character?
> FWIW,
> RLF
>
> "Misha" <mishka_mishka@.inbox.ru> wrote in message
> news:%23VY38aEeIHA.5984@.TK2MSFTNGP06.phx.gbl...
>

BULK INSERT from a saved recordset?

Is there any way to copy data from a previous saved recordset using "BULK INSERT" or bcp ? (the recordset is supposed to be saved using adPersistADTG or adPersistXML)look into adodb.stream object.|||See also ADO command type "file" for a recordset.

Bulk Insert for non admins

Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a local
instance of SQL on my desktop. I create an SQL Login named BITest and allow
him into my Pubs DB. I then add him to the BulkAdmin fixed server role. When
I have him try to Bulk Insert, I get:
Server: Msg 8104, Level 16, State 2, Line 1
The current user is not the database or object owner of table
'pubs.dbo.t_access'. Cannot perform SET operation.
Which sort of indicates he needs to be in the db_owner role? Even though I
think thats crap, I add him to db_owner, and then try to run my Bulk Insert
again. This time I get:
Server: Msg 4834, Level 16, State 1, Line 1
You do not have permission to use the BULK INSERT statement.
So, not only does it really appear that he nees to be part of db_owner, it
still doesnt work. What gives?
TIA,
ChrisR
Chris,
From BOL:
Permissions
Only members of the sysadmin and bulkadmin fixed server roles can execute
BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
> Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a local
> instance of SQL on my desktop. I create an SQL Login named BITest and
> allow
> him into my Pubs DB. I then add him to the BulkAdmin fixed server role.
> When
> I have him try to Bulk Insert, I get:
> Server: Msg 8104, Level 16, State 2, Line 1
> The current user is not the database or object owner of table
> 'pubs.dbo.t_access'. Cannot perform SET operation.
> Which sort of indicates he needs to be in the db_owner role? Even though
> I
> think thats crap, I add him to db_owner, and then try to run my Bulk
> Insert
> again. This time I get:
> Server: Msg 4834, Level 16, State 1, Line 1
> You do not have permission to use the BULK INSERT statement.
> So, not only does it really appear that he nees to be part of db_owner, it
> still doesnt work. What gives?
> --
> TIA,
> ChrisR
|||Jerry, as indicated in my post, I added him to that role.
TIA,
ChrisR
"Jerry Spivey" wrote:

> Chris,
> From BOL:
> Permissions
> Only members of the sysadmin and bulkadmin fixed server roles can execute
> BULK INSERT.
> HTH
> Jerry
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
>
>
|||Chris,
I got it to work with BULKADMIN and DB_OWNER. How many records to you have?
Do you really need to use BCP/BULK INSERT or would DTS possibly work?
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...[vbcol=seagreen]
> Jerry, as indicated in my post, I added him to that role.
> --
> TIA,
> ChrisR
>
> "Jerry Spivey" wrote:
|||I have 48 reconds. DTS may work, but I'd really like to try to get this
going. You're user is NOT a member of either SA of Admins, right?
TIA,
ChrisR
"Jerry Spivey" wrote:

> Chris,
> I got it to work with BULKADMIN and DB_OWNER. How many records to you have?
> Do you really need to use BCP/BULK INSERT or would DTS possibly work?
> HTH
> Jerry
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...
>
>
|||I'd recommend DTS but it is your choice. No...created new login and user to
test for your example.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:63A4B6D4-EABD-409A-BD49-E39B0CC7FF6F@.microsoft.com...[vbcol=seagreen]
>I have 48 reconds. DTS may work, but I'd really like to try to get this
> going. You're user is NOT a member of either SA of Admins, right?
> --
> TIA,
> ChrisR
>
> "Jerry Spivey" wrote:
|||Curious, why DTS? Also, Im running SQL2K, and you?
TIA,
ChrisR
"Jerry Spivey" wrote:

> I'd recommend DTS but it is your choice. No...created new login and user to
> test for your example.
> HTH
> Jerry
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:63A4B6D4-EABD-409A-BD49-E39B0CC7FF6F@.microsoft.com...
>
>
|||Its fast, friendly (permissions issue in your case) and more functional.
SQL 2000 as well.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B4925F9E-6BBB-477E-B4B7-9817D10CB89B@.microsoft.com...[vbcol=seagreen]
> Curious, why DTS? Also, Im running SQL2K, and you?
> --
> TIA,
> ChrisR
>
> "Jerry Spivey" wrote:

Bulk Insert for non admins

Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a local
instance of SQL on my desktop. I create an SQL Login named BITest and allow
him into my Pubs DB. I then add him to the BulkAdmin fixed server role. Whe
n
I have him try to Bulk Insert, I get:
Server: Msg 8104, Level 16, State 2, Line 1
The current user is not the database or object owner of table
'pubs.dbo.t_access'. Cannot perform SET operation.
Which sort of indicates he needs to be in the db_owner role? Even though I
think thats crap, I add him to db_owner, and then try to run my Bulk Insert
again. This time I get:
Server: Msg 4834, Level 16, State 1, Line 1
You do not have permission to use the BULK INSERT statement.
So, not only does it really appear that he nees to be part of db_owner, it
still doesnt work. What gives?
TIA,
ChrisRChris,
From BOL:
Permissions
Only members of the sysadmin and bulkadmin fixed server roles can execute
BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
> Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a local
> instance of SQL on my desktop. I create an SQL Login named BITest and
> allow
> him into my Pubs DB. I then add him to the BulkAdmin fixed server role.
> When
> I have him try to Bulk Insert, I get:
> Server: Msg 8104, Level 16, State 2, Line 1
> The current user is not the database or object owner of table
> 'pubs.dbo.t_access'. Cannot perform SET operation.
> Which sort of indicates he needs to be in the db_owner role? Even though
> I
> think thats crap, I add him to db_owner, and then try to run my Bulk
> Insert
> again. This time I get:
> Server: Msg 4834, Level 16, State 1, Line 1
> You do not have permission to use the BULK INSERT statement.
> So, not only does it really appear that he nees to be part of db_owner, it
> still doesnt work. What gives?
> --
> TIA,
> ChrisR|||Jerry, as indicated in my post, I added him to that role.
--
TIA,
ChrisR
"Jerry Spivey" wrote:

> Chris,
> From BOL:
> Permissions
> Only members of the sysadmin and bulkadmin fixed server roles can execute
> BULK INSERT.
> HTH
> Jerry
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
>
>|||Chris,
I got it to work with BULKADMIN and DB_OWNER. How many records to you have?
Do you really need to use BCP/BULK INSERT or would DTS possibly work?
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...[vbcol=seagreen]
> Jerry, as indicated in my post, I added him to that role.
> --
> TIA,
> ChrisR
>
> "Jerry Spivey" wrote:
>|||I have 48 reconds. DTS may work, but I'd really like to try to get this
going. You're user is NOT a member of either SA of Admins, right?
--
TIA,
ChrisR
"Jerry Spivey" wrote:

> Chris,
> I got it to work with BULKADMIN and DB_OWNER. How many records to you hav
e?
> Do you really need to use BCP/BULK INSERT or would DTS possibly work?
> HTH
> Jerry
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...
>
>|||I'd recommend DTS but it is your choice. No...created new login and user to
test for your example.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:63A4B6D4-EABD-409A-BD49-E39B0CC7FF6F@.microsoft.com...[vbcol=seagreen]
>I have 48 reconds. DTS may work, but I'd really like to try to get this
> going. You're user is NOT a member of either SA of Admins, right?
> --
> TIA,
> ChrisR
>
> "Jerry Spivey" wrote:
>|||Curious, why DTS? Also, Im running SQL2K, and you?
--
TIA,
ChrisR
"Jerry Spivey" wrote:

> I'd recommend DTS but it is your choice. No...created new login and user
to
> test for your example.
> HTH
> Jerry
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:63A4B6D4-EABD-409A-BD49-E39B0CC7FF6F@.microsoft.com...
>
>|||Its fast, friendly (permissions issue in your case) and more functional.
SQL 2000 as well.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B4925F9E-6BBB-477E-B4B7-9817D10CB89B@.microsoft.com...[vbcol=seagreen]
> Curious, why DTS? Also, Im running SQL2K, and you?
> --
> TIA,
> ChrisR
>
> "Jerry Spivey" wrote:
>