BULK INSERT skips a row

Hi. Not sure if this is a bug with bulk insert or standard behaviour.
We have a flat file with a header record. A simplified example follows:
HEADER
John,Smith
Fred,Bloggs
But when we set bulk insert to skip the first row it actually skips the
John,Smith row as well. Run the script below after saving the CSV data out
to a file called user.csv...
create table [User]
(
FirstName varchar ( 32 ),
LastName varchar ( 32 )
)
bulk insert [User] from 'user.csv'
with
(
FIRSTROW = 2,
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
select * from [user] -- only one record
Please help!!
McGy
[url]http://mcgy.blogspot.com[/url]Anyone with ideas on how to get around this limitation?
McGy
[url]http://mcgy.blogspot.com[/url]
"McGy" <anon@.anon.com> wrote in message
news:#1#7JPAVGHA.5332@.tk2msftngp13.phx.gbl...
> Hi. Not sure if this is a bug with bulk insert or standard behaviour.
> We have a flat file with a header record. A simplified example follows:
> HEADER
> John,Smith
> Fred,Bloggs
> But when we set bulk insert to skip the first row it actually skips the
> John,Smith row as well. Run the script below after saving the CSV data out
> to a file called user.csv...
> create table [User]
> (
> FirstName varchar ( 32 ),
> LastName varchar ( 32 )
> )
> bulk insert [User] from 'user.csv'
> with
> (
> FIRSTROW = 2,
> DATAFILETYPE = 'char',
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )
> select * from [user] -- only one record
> Please help!!
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
>|||Some solutions are
1. Pre-process the file to remove the header line (use a DOS
or Unix-like utility, for example).
2. Bulk insert the data into a staging table with one column,
then parse the data into columns using SQL instead of the
bulk insert process.
3. Use SQL Server Integration Services. I'm no expert, but I believe
it can skip (or import into a different table) rows based on the
contents of the row, which bulk insert cannot do.
The FIRSTROW parameter for bulk insert, as you have discovered,
is not a FIRST_LINE_OF_THE_TEXT_FILE parameter.
Steve Kass
Drew University
McGy wrote:

>Anyone with ideas on how to get around this limitation?
>--
>McGy
>[url]http://mcgy.blogspot.com[/url]
>
>"McGy" <anon@.anon.com> wrote in message
>news:#1#7JPAVGHA.5332@.tk2msftngp13.phx.gbl...
>
>
>|||McGy (anon@.anon.com) writes:
Alas, this is standard behaviour.
BULK INSERT and BCP live in a very squared world. They don't think in
terms of lines, they think in terms of records and fields. (But calls them
rows and columns to matters.)
The first field is from the beginning of file until the terminator for
the first field. (For terminated fields. Fields can also be fix-length,
or include a length-prefix.) The second field lasts until the terminator
for that field. And so on, up to the last field which lasts until the
terminator for that field. That terminator is called a "row terminator",
but as long as BCP is not reading the last field, the terminator carries
no meaning. Once the last field has been read, bulk copy starts with
the first field again.
This has some advantages and divantages. On the flip side, is that
you can handle embedded row terminators, for instance embedded newlines
(except in the last field). The bad part is that you can't skip a header
which does not conform with the file. Because as far as BCP is concerned,
that header is part of the first field.
The workaround is to run a program first that strips the header, which
for a large file is not very appealing.
I know I have subumitted a suggestion for an improvement to bulk copy
for a future release of SQL Server, so that you should be able to describe
a header to be skipped. The suggestion is on
http://lab.msdn.microsoft.com/productfeedback/ but alas, the site
appears to experience some problems right now, so I cannot give the
exact link. But when it's back, search for by name, "bulk" and SQL 2005
to find it and vote for it.
--
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 Problem(Bug) in SQL Server 2005

I can not bulk insert a network file onto a remote SQL server 2005 machine
I get this error when run from Query analyser in remote machine
Cannot bulk load because the file <File Name> could not be opened. Operating
system error code 5(Access is denied.)
It used to work in SQL 2000 We just upgradted to SQL 2005.
The same command works fine when run from the local machine hosting the SQL
server it can access the remote file and can bulk isert but when run from
remote machine it does not work. All accounts are admin accounts and we use
Windows authentication and are using valid UNC paths.
Thanks
Kiran
I have this same exact problem, and have been fighting it for three
days. Even when I remove the lines that it says are causing the
problem, when I run it again, it just picks some new lines to fail on.
Check out this thread, where someone posts how they proved that the
problem only exists when using SQL Server 2005 to import the data:
http://forums.microsoft.com/MSDN/Sho...41512&SiteID=1
I'm convinced there is some kind of bug here, and I'm about to give up
on trying to use bulk insert altogether and just write a program to
import the file myself. SQL 2000 DTS can import the same file with no
problem. It's pretty clear there's nothing wrong with the file.
FYI, in an SSIS context, this same exact thing happens when using the
Bulk Insert task and when using the Import Column in a data flow task.
I can reproduce the problem by executing a BULK insert command in a
query window, taking SSIS out of the picture altogether.
If anyone from Microsoft is reading this and is aware of this issue,
please advise. There are clearly multiple people out here fighting it.
Dan
sql

Bulk insert Problem(Bug) in SQL Server 2005

I can not bulk insert a network file onto a remote SQL server 2005 machine
I get this error when run from Query analyser in remote machine
Cannot bulk load because the file <File Name> could not be opened. Operating
system error code 5(Access is denied.)
It used to work in SQL 2000 We just upgradted to SQL 2005.
The same command works fine when run from the local machine hosting the SQL
server it can access the remote file and can bulk isert but when run from
remote machine it does not work. All accounts are admin accounts and we use
Windows authentication and are using valid UNC paths.
Thanks
KiranI have this same exact problem, and have been fighting it for three
days. Even when I remove the lines that it says are causing the
problem, when I run it again, it just picks some new lines to fail on.
Check out this thread, where someone posts how they proved that the
problem only exists when using SQL Server 2005 to import the data:
http://forums.microsoft.com/MSDN/Sh...141512&SiteID=1
I'm convinced there is some kind of bug here, and I'm about to give up
on trying to use bulk insert altogether and just write a program to
import the file myself. SQL 2000 DTS can import the same file with no
problem. It's pretty clear there's nothing wrong with the file.
FYI, in an SSIS context, this same exact thing happens when using the
Bulk Insert task and when using the Import Column in a data flow task.
I can reproduce the problem by executing a BULK insert command in a
query window, taking SSIS out of the picture altogether.
If anyone from Microsoft is reading this and is aware of this issue,
please advise. There are clearly multiple people out here fighting it.
Dan

Bulk insert Problem(Bug) in SQL Server 2005

I can not bulk insert a network file onto a remote SQL server 2005 machine
I get this error when run from Query analyser in remote machine
Cannot bulk load because the file <File Name> could not be opened. Operating
system error code 5(Access is denied.)
It used to work in SQL 2000 We just upgradted to SQL 2005.
The same command works fine when run from the local machine hosting the SQL
server it can access the remote file and can bulk isert but when run from
remote machine it does not work. All accounts are admin accounts and we use
Windows authentication and are using valid UNC paths.
Thanks
KiranI have this same exact problem, and have been fighting it for three
days. Even when I remove the lines that it says are causing the
problem, when I run it again, it just picks some new lines to fail on.
Check out this thread, where someone posts how they proved that the
problem only exists when using SQL Server 2005 to import the data:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=141512&SiteID=1
I'm convinced there is some kind of bug here, and I'm about to give up
on trying to use bulk insert altogether and just write a program to
import the file myself. SQL 2000 DTS can import the same file with no
problem. It's pretty clear there's nothing wrong with the file.
FYI, in an SSIS context, this same exact thing happens when using the
Bulk Insert task and when using the Import Column in a data flow task.
I can reproduce the problem by executing a BULK insert command in a
query window, taking SSIS out of the picture altogether.
If anyone from Microsoft is reading this and is aware of this issue,
please advise. There are clearly multiple people out here fighting it.
Dan

Bulk Insert Bug?

Dear Anyone,
I had been using BI Accelerator 1.1 for 2 years now. In
our current project, I encountered the following error
during the importing phase of BI.
I traced the problem to the bulk insert statement that BI
Accelerator is executing which is ...
BULK INSERT [Project88_Staging]..[S_Fact_BrdcastAlert]
FROM 'C:\Program Files\Microsoft SQL Server Accelerator
for BI\data\Project88\Import\S_Fact_BrdcastAlert.txt'
WITH ( FORMATFILE = 'C:\Program Files\Microsoft SQL
Server Accelerator for BI\data\Project88
\DTS\Import\Format\S_Fact_BrdcastAlert.fmt' ,
DATAFILETYPE = 'widechar', BATCHSIZE = 100000 ,
MAXERRORS = 100 )
The error that it generates is as follows:
Bulk Insert: DataFileType was incorrectly specified as
widechar. DataFileType will be assumed to be char because
the data file does not have a Unicode signature.
We export data to text files that BI Accelerator picks
up. All other files imports properly imported without
problems except for one fact table text file. We use the
same format file that BI Accelerator uses to export to
file the data to make sure that there would be no
incompatabilities. File size is about 2.3GB++, which is a
standard size with our other fact table files that doesnt
have any errors.
We also checked for data error which we found none. We
also checked the txt file we generate, notepad confirms
that it is a unicode file.
Can anyone please shed a light in what is happening. Is
this a bug? As much as possible I dont want to place a
workaround on this one since our entire framework for
loading data is all the same and has already been set up.
Any help would be apreciated.
Thank You.
I'm seeing the same problem.
It seems that DTS is assuming we made a mistake in specifying
"wideChar" when it doesn't see the BOM in the Unicode file. BOM being
the first two bytes of the file being FF FE or FE FF indicating the
byte order of the file.
My problem is I'm exporting the data using SQL Server's bcp as Wide
Char data and bcp doesn't put the BOM in the file. bcp will import the
data if you tell it that it is WideChar data however.
Does anyone know how to get bcp to include the BOM or get DTS to trust
me that the data is in fact Little-Endian Unicode data?
Thanks,
Jim
|||Joseph,
Can you look at the file in a hex editor to see if the error message
is true? Does the file have FFFE or FFFF as its first two bytes? If it
does not, can you post the bcp statement or otherwise indicate how you
exported the data to the file? Can you also post the format file you're
using? If you're getting this error and the file does indeed have FFFE
or FFFF at the beginning, I'll try to see what else I can find.
Steve Kass
Drew University
Joseph Ollero wrote:

>Dear Anyone,
>I had been using BI Accelerator 1.1 for 2 years now. In
>our current project, I encountered the following error
>during the importing phase of BI.
>I traced the problem to the bulk insert statement that BI
>Accelerator is executing which is ...
>BULK INSERT [Project88_Staging]..[S_Fact_BrdcastAlert]
>FROM 'C:\Program Files\Microsoft SQL Server Accelerator
>for BI\data\Project88\Import\S_Fact_BrdcastAlert.txt'
>WITH ( FORMATFILE = 'C:\Program Files\Microsoft SQL
>Server Accelerator for BI\data\Project88
>\DTS\Import\Format\S_Fact_BrdcastAlert.fmt' ,
>DATAFILETYPE = 'widechar', BATCHSIZE = 100000 ,
>MAXERRORS = 100 )
>
>The error that it generates is as follows:
>Bulk Insert: DataFileType was incorrectly specified as
>widechar. DataFileType will be assumed to be char because
>the data file does not have a Unicode signature.
>
>We export data to text files that BI Accelerator picks
>up. All other files imports properly imported without
>problems except for one fact table text file. We use the
>same format file that BI Accelerator uses to export to
>file the data to make sure that there would be no
>incompatabilities. File size is about 2.3GB++, which is a
>standard size with our other fact table files that doesnt
>have any errors.
>We also checked for data error which we found none. We
>also checked the txt file we generate, notepad confirms
>that it is a unicode file.
>Can anyone please shed a light in what is happening. Is
>this a bug? As much as possible I dont want to place a
>workaround on this one since our entire framework for
>loading data is all the same and has already been set up.
>Any help would be apreciated.
>
>Thank You.
>
>
|||Jim,
Can you provide a bcp statement where the FFFE or FEFF isn't
specified? This shouldn't happen, and doesn't with the simple examples
I've tried.
If you don't have any choice but to work with a bad Unicode file
(missing the BOM), you can add the BOM yourself. The Cygwin Unix shell
for Windows contains an excellent set of utilities for text file
processing, and I'm sure there are good hex editors that can do this.
Steve Kass
Drew University
Jim O'Donnell wrote:

>I'm seeing the same problem.
>It seems that DTS is assuming we made a mistake in specifying
>"wideChar" when it doesn't see the BOM in the Unicode file. BOM being
>the first two bytes of the file being FF FE or FE FF indicating the
>byte order of the file.
>My problem is I'm exporting the data using SQL Server's bcp as Wide
>Char data and bcp doesn't put the BOM in the file. bcp will import the
>data if you tell it that it is WideChar data however.
>Does anyone know how to get bcp to include the BOM or get DTS to trust
>me that the data is in fact Little-Endian Unicode data?
>Thanks,
>Jim
>
|||I did use a hex editor, that's how I concluded that was the problem. I
inserted the missing FFFE to test the problem. Same file with the BOM
has no problem being imported using DTS.
The problem seems to be with bcp on two machines.
I am using SQLDMO.BulkCopy2 View.ExportData through VB6 to create the
WideChar file. (and, yes, I did set .ExportWideChar = True)
I used command line bcp to test though:
bcp "DB.dbo.View" out "C:\bcpCmdTest.txt" -V"sa" -P"*****"
-S"ServerName" -w -t"|~" -r"\r\n"
Both with SQLDMO and command line bcp the files had no BOM
I just tried it on a third machine. The command line seems to create
the file with the BOM but the SQLDMO.BulkCopy2 still does it without
the BOM.
|||Jim,
Any chance you have something like bcp.SetCodePage (SQLDMOBCP RAW) in
your code? The RAW specification will override the -w switch, by
implying that the data is not text, but native binary format. The
thread is here:
http://groups.google.com/groups?thre...0a% 40phx.gbl
SK
Jim O'Donnell wrote:

>I did use a hex editor, that's how I concluded that was the problem. I
>inserted the missing FFFE to test the problem. Same file with the BOM
>has no problem being imported using DTS.
>The problem seems to be with bcp on two machines.
>I am using SQLDMO.BulkCopy2 View.ExportData through VB6 to create the
>WideChar file. (and, yes, I did set .ExportWideChar = True)
>I used command line bcp to test though:
>bcp "DB.dbo.View" out "C:\bcpCmdTest.txt" -V"sa" -P"*****"
>-S"ServerName" -w -t"|~" -r"\r\n"
>Both with SQLDMO and command line bcp the files had no BOM
>I just tried it on a third machine. The command line seems to create
>the file with the BOM but the SQLDMO.BulkCopy2 still does it without
>the BOM.
>
|||Nope, this is the set up of the BulkCopy2 object:
Dim objBCP As New SQLDMO.BulkCopy2
..
..
With objBCP
.DataFilePath = FileName
'Set ExportWideChar to True for Unicode data file
.ExportWideChar = True
'Set up Column and Row delimeters for file
.ColumnDelimiter = ColumnDelim
.RowDelimiter = vbCrLf
.DataFileType = SQLDMODataFile_SpecialDelimitedChar
End With
..
..
(ColumnDelim is a string parameter of the function)
Thanks for your attention.
Jim

Bulk Insert Bug?

Dear Anyone,
I had been using BI Accelerator 1.1 for 2 years now. In
our current project, I encountered the following error
during the importing phase of BI.
I traced the problem to the bulk insert statement that BI
Accelerator is executing which is ...
BULK INSERT [Project88_Staging]..[S_Fact_BrdcastAlert]
FROM 'C:\Program Files\Microsoft SQL Server Accelerator
for BI\data\Project88\Import\S_Fact_BrdcastAlert.txt'
WITH ( FORMATFILE = 'C:\Program Files\Microsoft SQL
Server Accelerator for BI\data\Project88
\DTS\Import\Format\S_Fact_BrdcastAlert.fmt' ,
DATAFILETYPE = 'widechar', BATCHSIZE = 100000 ,
MAXERRORS = 100 )
The error that it generates is as follows:
Bulk Insert: DataFileType was incorrectly specified as
widechar. DataFileType will be assumed to be char because
the data file does not have a Unicode signature.
We export data to text files that BI Accelerator picks
up. All other files imports properly imported without
problems except for one fact table text file. We use the
same format file that BI Accelerator uses to export to
file the data to make sure that there would be no
incompatabilities. File size is about 2.3GB++, which is a
standard size with our other fact table files that doesnt
have any errors.
We also checked for data error which we found none. We
also checked the txt file we generate, notepad confirms
that it is a unicode file.
Can anyone please shed a light in what is happening. Is
this a bug? As much as possible I dont want to place a
workaround on this one since our entire framework for
loading data is all the same and has already been set up.
Any help would be apreciated.
Thank You.I'm seeing the same problem.
It seems that DTS is assuming we made a mistake in specifying
"wideChar" when it doesn't see the BOM in the Unicode file. BOM being
the first two bytes of the file being FF FE or FE FF indicating the
byte order of the file.
My problem is I'm exporting the data using SQL Server's bcp as Wide
Char data and bcp doesn't put the BOM in the file. bcp will import the
data if you tell it that it is WideChar data however.
Does anyone know how to get bcp to include the BOM or get DTS to trust
me that the data is in fact Little-Endian Unicode data?
Thanks,
Jim|||Joseph,
Can you look at the file in a hex editor to see if the error message
is true? Does the file have FFFE or FFFF as its first two bytes? If it
does not, can you post the bcp statement or otherwise indicate how you
exported the data to the file? Can you also post the format file you're
using? If you're getting this error and the file does indeed have FFFE
or FFFF at the beginning, I'll try to see what else I can find.
Steve Kass
Drew University
Joseph Ollero wrote:
>Dear Anyone,
>I had been using BI Accelerator 1.1 for 2 years now. In
>our current project, I encountered the following error
>during the importing phase of BI.
>I traced the problem to the bulk insert statement that BI
>Accelerator is executing which is ...
>BULK INSERT [Project88_Staging]..[S_Fact_BrdcastAlert]
>FROM 'C:\Program Files\Microsoft SQL Server Accelerator
>for BI\data\Project88\Import\S_Fact_BrdcastAlert.txt'
>WITH ( FORMATFILE = 'C:\Program Files\Microsoft SQL
>Server Accelerator for BI\data\Project88
>\DTS\Import\Format\S_Fact_BrdcastAlert.fmt' ,
>DATAFILETYPE = 'widechar', BATCHSIZE = 100000 ,
>MAXERRORS = 100 )
>
>The error that it generates is as follows:
>Bulk Insert: DataFileType was incorrectly specified as
>widechar. DataFileType will be assumed to be char because
>the data file does not have a Unicode signature.
>
>We export data to text files that BI Accelerator picks
>up. All other files imports properly imported without
>problems except for one fact table text file. We use the
>same format file that BI Accelerator uses to export to
>file the data to make sure that there would be no
>incompatabilities. File size is about 2.3GB++, which is a
>standard size with our other fact table files that doesnt
>have any errors.
>We also checked for data error which we found none. We
>also checked the txt file we generate, notepad confirms
>that it is a unicode file.
>Can anyone please shed a light in what is happening. Is
>this a bug? As much as possible I dont want to place a
>workaround on this one since our entire framework for
>loading data is all the same and has already been set up.
>Any help would be apreciated.
>
>Thank You.
>
>|||Jim,
Can you provide a bcp statement where the FFFE or FEFF isn't
specified? This shouldn't happen, and doesn't with the simple examples
I've tried.
If you don't have any choice but to work with a bad Unicode file
(missing the BOM), you can add the BOM yourself. The Cygwin Unix shell
for Windows contains an excellent set of utilities for text file
processing, and I'm sure there are good hex editors that can do this.
Steve Kass
Drew University
Jim O'Donnell wrote:
>I'm seeing the same problem.
>It seems that DTS is assuming we made a mistake in specifying
>"wideChar" when it doesn't see the BOM in the Unicode file. BOM being
>the first two bytes of the file being FF FE or FE FF indicating the
>byte order of the file.
>My problem is I'm exporting the data using SQL Server's bcp as Wide
>Char data and bcp doesn't put the BOM in the file. bcp will import the
>data if you tell it that it is WideChar data however.
>Does anyone know how to get bcp to include the BOM or get DTS to trust
>me that the data is in fact Little-Endian Unicode data?
>Thanks,
>Jim
>|||I did use a hex editor, that's how I concluded that was the problem. I
inserted the missing FFFE to test the problem. Same file with the BOM
has no problem being imported using DTS.
The problem seems to be with bcp on two machines.
I am using SQLDMO.BulkCopy2 View.ExportData through VB6 to create the
WideChar file. (and, yes, I did set .ExportWideChar = True)
I used command line bcp to test though:
bcp "DB.dbo.View" out "C:\bcpCmdTest.txt" -V"sa" -P"*****"
-S"ServerName" -w -t"|~" -r"\r\n"
Both with SQLDMO and command line bcp the files had no BOM
I just tried it on a third machine. The command line seems to create
the file with the BOM but the SQLDMO.BulkCopy2 still does it without
the BOM.|||Jim,
Any chance you have something like bcp.SetCodePage (SQLDMOBCP RAW) in
your code? The RAW specification will override the -w switch, by
implying that the data is not text, but native binary format. The
thread is here:
http://groups.google.com/groups?threadm=03a201c25dd5%240e6013f0%242ae2c90a%40phx.gbl
SK
Jim O'Donnell wrote:
>I did use a hex editor, that's how I concluded that was the problem. I
>inserted the missing FFFE to test the problem. Same file with the BOM
>has no problem being imported using DTS.
>The problem seems to be with bcp on two machines.
>I am using SQLDMO.BulkCopy2 View.ExportData through VB6 to create the
>WideChar file. (and, yes, I did set .ExportWideChar = True)
>I used command line bcp to test though:
>bcp "DB.dbo.View" out "C:\bcpCmdTest.txt" -V"sa" -P"*****"
>-S"ServerName" -w -t"|~" -r"\r\n"
>Both with SQLDMO and command line bcp the files had no BOM
>I just tried it on a third machine. The command line seems to create
>the file with the BOM but the SQLDMO.BulkCopy2 still does it without
>the BOM.
>|||Nope, this is the set up of the BulkCopy2 object:
Dim objBCP As New SQLDMO.BulkCopy2
.
.
With objBCP
.DataFilePath = FileName
'Set ExportWideChar to True for Unicode data file
.ExportWideChar = True
'Set up Column and Row delimeters for file
.ColumnDelimiter = ColumnDelim
.RowDelimiter = vbCrLf
.DataFileType = SQLDMODataFile_SpecialDelimitedChar
End With
.
.
(ColumnDelim is a string parameter of the function)
Thanks for your attention.
Jim

Bulk Insert Bug?

Dear Anyone,
I had been using BI Accelerator 1.1 for 2 years now. In
our current project, I encountered the following error
during the importing phase of BI.
I traced the problem to the bulk insert statement that BI
Accelerator is executing which is ...
BULK INSERT [Project88_Staging]..[S_Fact_BrdcastAlert]
FROM 'C:\Program Files\Microsoft SQL Server Accelerator
for BI\data\Project88\Import\S_Fact_BrdcastA
lert.txt'
WITH ( FORMATFILE = 'C:\Program Files\Microsoft SQL
Server Accelerator for BI\data\Project88
\DTS\Import\Format\S_Fact_BrdcastAlert.fmt' ,
DATAFILETYPE = 'widechar', BATCHSIZE = 100000 ,
MAXERRORS = 100 )
The error that it generates is as follows:
Bulk Insert: DataFileType was incorrectly specified as
widechar. DataFileType will be assumed to be char because
the data file does not have a Unicode signature.
We export data to text files that BI Accelerator picks
up. All other files imports properly imported without
problems except for one fact table text file. We use the
same format file that BI Accelerator uses to export to
file the data to make sure that there would be no
incompatabilities. File size is about 2.3GB++, which is a
standard size with our other fact table files that doesnt
have any errors.
We also checked for data error which we found none. We
also checked the txt file we generate, notepad confirms
that it is a unicode file.
Can anyone please shed a light in what is happening. Is
this a bug? As much as possible I dont want to place a
workaround on this one since our entire framework for
loading data is all the same and has already been set up.
Any help would be apreciated.
Thank You.I'm seeing the same problem.
It seems that DTS is assuming we made a mistake in specifying
"wideChar" when it doesn't see the BOM in the Unicode file. BOM being
the first two bytes of the file being FF FE or FE FF indicating the
byte order of the file.
My problem is I'm exporting the data using SQL Server's bcp as Wide
Char data and bcp doesn't put the BOM in the file. bcp will import the
data if you tell it that it is WideChar data however.
Does anyone know how to get bcp to include the BOM or get DTS to trust
me that the data is in fact Little-Endian Unicode data?
Thanks,
Jim|||Joseph,
Can you look at the file in a hex editor to see if the error message
is true? Does the file have FFFE or FFFF as its first two bytes? If it
does not, can you post the bcp statement or otherwise indicate how you
exported the data to the file? Can you also post the format file you're
using? If you're getting this error and the file does indeed have FFFE
or FFFF at the beginning, I'll try to see what else I can find.
Steve Kass
Drew University
Joseph Ollero wrote:

>Dear Anyone,
>I had been using BI Accelerator 1.1 for 2 years now. In
>our current project, I encountered the following error
>during the importing phase of BI.
>I traced the problem to the bulk insert statement that BI
>Accelerator is executing which is ...
>BULK INSERT [Project88_Staging]..[S_Fact_BrdcastAlert]
>FROM 'C:\Program Files\Microsoft SQL Server Accelerator
>for BI\data\Project88\Import\S_Fact_BrdcastA
lert.txt'
>WITH ( FORMATFILE = 'C:\Program Files\Microsoft SQL
>Server Accelerator for BI\data\Project88
>\DTS\Import\Format\S_Fact_BrdcastAlert.fmt' ,
>DATAFILETYPE = 'widechar', BATCHSIZE = 100000 ,
>MAXERRORS = 100 )
>
>The error that it generates is as follows:
>Bulk Insert: DataFileType was incorrectly specified as
>widechar. DataFileType will be assumed to be char because
>the data file does not have a Unicode signature.
>
>We export data to text files that BI Accelerator picks
>up. All other files imports properly imported without
>problems except for one fact table text file. We use the
>same format file that BI Accelerator uses to export to
>file the data to make sure that there would be no
>incompatabilities. File size is about 2.3GB++, which is a
>standard size with our other fact table files that doesnt
>have any errors.
>We also checked for data error which we found none. We
>also checked the txt file we generate, notepad confirms
>that it is a unicode file.
>Can anyone please shed a light in what is happening. Is
>this a bug? As much as possible I dont want to place a
>workaround on this one since our entire framework for
>loading data is all the same and has already been set up.
>Any help would be apreciated.
>
>Thank You.
>
>|||Jim,
Can you provide a bcp statement where the FFFE or FEFF isn't
specified? This shouldn't happen, and doesn't with the simple examples
I've tried.
If you don't have any choice but to work with a bad Unicode file
(missing the BOM), you can add the BOM yourself. The Cygwin Unix shell
for Windows contains an excellent set of utilities for text file
processing, and I'm sure there are good hex editors that can do this.
Steve Kass
Drew University
Jim O'Donnell wrote:

>I'm seeing the same problem.
>It seems that DTS is assuming we made a mistake in specifying
>"wideChar" when it doesn't see the BOM in the Unicode file. BOM being
>the first two bytes of the file being FF FE or FE FF indicating the
>byte order of the file.
>My problem is I'm exporting the data using SQL Server's bcp as Wide
>Char data and bcp doesn't put the BOM in the file. bcp will import the
>data if you tell it that it is WideChar data however.
>Does anyone know how to get bcp to include the BOM or get DTS to trust
>me that the data is in fact Little-Endian Unicode data?
>Thanks,
>Jim
>|||I did use a hex editor, that's how I concluded that was the problem. I
inserted the missing FFFE to test the problem. Same file with the BOM
has no problem being imported using DTS.
The problem seems to be with bcp on two machines.
I am using SQLDMO.BulkCopy2 View.ExportData through VB6 to create the
WideChar file. (and, yes, I did set .ExportWideChar = True)
I used command line bcp to test though:
bcp "DB.dbo.View" out "C:\bcpCmdTest.txt" -V"sa" -P"*****"
-S"ServerName" -w -t"|~" -r"\r\n"
Both with SQLDMO and command line bcp the files had no BOM
I just tried it on a third machine. The command line seems to create
the file with the BOM but the SQLDMO.BulkCopy2 still does it without
the BOM.|||Jim,
Any chance you have something like bcp.SetCodePage (SQLDMOBCP RAW) in
your code? The RAW specification will override the -w switch, by
implying that the data is not text, but native binary format. The
thread is here:
0a%40phx.g
bl" target="_blank">http://groups.google.com/groups? th...phx.g
bl
SK
Jim O'Donnell wrote:

>I did use a hex editor, that's how I concluded that was the problem. I
>inserted the missing FFFE to test the problem. Same file with the BOM
>has no problem being imported using DTS.
>The problem seems to be with bcp on two machines.
>I am using SQLDMO.BulkCopy2 View.ExportData through VB6 to create the
>WideChar file. (and, yes, I did set .ExportWideChar = True)
>I used command line bcp to test though:
>bcp "DB.dbo.View" out "C:\bcpCmdTest.txt" -V"sa" -P"*****"
>-S"ServerName" -w -t"|~" -r"\r\n"
>Both with SQLDMO and command line bcp the files had no BOM
>I just tried it on a third machine. The command line seems to create
>the file with the BOM but the SQLDMO.BulkCopy2 still does it without
>the BOM.
>|||Nope, this is the set up of the BulkCopy2 object:
Dim objBCP As New SQLDMO.BulkCopy2
.
.
With objBCP
.DataFilePath = FileName
'Set ExportWideChar to True for Unicode data file
.ExportWideChar = True
'Set up Column and Row delimeters for file
.ColumnDelimiter = ColumnDelim
.RowDelimiter = vbCrLf
.DataFileType = SQLDMODataFile_SpecialDelimitedChar
End With
.
.
(ColumnDelim is a string parameter of the function)
Thanks for your attention.
Jim