Hi,
I could not able to bulk insert into a temporary table. I am getting the fol
lowing error.
Server: Msg 8104, Level 16, State 2, Line 1
The current user is not the database or object owner of table '#TempTable'.
Cannot perform SET operation.
I am member of 'Bulk Insert Admin' role.
Pls help me.
Regards,
SanSounds like you may be running into the following issue:
BUG: Cannot Perform BULK INSERT with Bulkadmin Privileges
http://support.microsoft.com/?kbid=302621
-Sue
On Mon, 15 Mar 2004 02:21:05 -0800, San
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>I could not able to bulk insert into a temporary table. I am getting the fo
llowing error.
>Server: Msg 8104, Level 16, State 2, Line 1
>The current user is not the database or object owner of table '#TempTable'.
Cannot perform SET operation.
>I am member of 'Bulk Insert Admin' role.
>Pls help me.
>Regards,
>San
Showing posts with label current. Show all posts
Showing posts with label current. Show all posts
Thursday, March 8, 2012
Wednesday, March 7, 2012
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
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
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
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
Tuesday, February 14, 2012
Building Move
My current company is planning to undertake an enormous move late this week,
We have a very large SAN environment .
It has been decided that we change the Ip address of the new network at the
new building in order to make the transition as smooth as possible.
With the ip address changes on the new network what type of issues should we
look out for during this move. If any one has participated in a move that
included sql servers any help would greatly be appreciated.Hi
If you are not directly using the IP address then in general I think you
should be ok.
Write down the tests that you are going to do to verify the change before
hand and check them off as you go. You can then make sure that everything is
covered in your testing. Knowing the current systems data flows will help
make sure these are documented.
Make sure that the DNS's are correctly updated and replication of the new
records may require time to propogate.
Make sure that firewalls are correctly configured for any new ranges you are
using and rules relating to the old ranges changed or removed.
John
"JDS" wrote:
> My current company is planning to undertake an enormous move late this week,
> We have a very large SAN environment .
> It has been decided that we change the Ip address of the new network at the
> new building in order to make the transition as smooth as possible.
> With the ip address changes on the new network what type of issues should we
> look out for during this move. If any one has participated in a move that
> included sql servers any help would greatly be appreciated.
We have a very large SAN environment .
It has been decided that we change the Ip address of the new network at the
new building in order to make the transition as smooth as possible.
With the ip address changes on the new network what type of issues should we
look out for during this move. If any one has participated in a move that
included sql servers any help would greatly be appreciated.Hi
If you are not directly using the IP address then in general I think you
should be ok.
Write down the tests that you are going to do to verify the change before
hand and check them off as you go. You can then make sure that everything is
covered in your testing. Knowing the current systems data flows will help
make sure these are documented.
Make sure that the DNS's are correctly updated and replication of the new
records may require time to propogate.
Make sure that firewalls are correctly configured for any new ranges you are
using and rules relating to the old ranges changed or removed.
John
"JDS" wrote:
> My current company is planning to undertake an enormous move late this week,
> We have a very large SAN environment .
> It has been decided that we change the Ip address of the new network at the
> new building in order to make the transition as smooth as possible.
> With the ip address changes on the new network what type of issues should we
> look out for during this move. If any one has participated in a move that
> included sql servers any help would greatly be appreciated.
Friday, February 10, 2012
Build a report snapshot from SSIS
Hello,
On my current project we would like to create Report snapshots from SSIS.
I'm fairly sure we can do this by calling the RS web service using the SSIS Web Service Task - but I don't know what method I should be calling nor what the URL may be for the service. Can anyone help?
Thanks
Jamie
If you are creating history snapshots, you want the soap method CreateReportHistorySnapshot. For execution snapshots, use UpdateReportExecutionSnapshot. Documentation is available on these methods at http://msdn2.microsoft.com/en-us/library/ms155076(en-us,SQL.90).aspx
Subscribe to:
Posts (Atom)