build sql script: importing

Showing posts with label importing. Show all posts

Thursday, March 29, 2012

Bulk SQL Server 2000 Registration

Hi All,
I have about 60 or so SQL Servers to register. I don't have the option of
importing from another machine.
I think I maybe able to do this via SQL-DMO but not sure how to get started.
Does anyone have any ideas about doing mass SQl Server registrations?
Thanks.Check out:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_ob_r_62pg.asp
Haven't tried it but you might also be able to create a query and loop for
sp_addserver via QA or OSQL.
HTH
Jerry
"brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
news:8A49CC26-4F93-49C7-9407-8451F2C38648@.microsoft.com...
> Hi All,
> I have about 60 or so SQL Servers to register. I don't have the option of
> importing from another machine.
> I think I maybe able to do this via SQL-DMO but not sure how to get
> started.
> Does anyone have any ideas about doing mass SQl Server registrations?
> Thanks.|||Thanks. I will try this.
Will keep the community posted ( pun intended)!
"Jerry Spivey" wrote:
> Check out:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_ob_r_62pg.asp
> Haven't tried it but you might also be able to create a query and loop for
> sp_addserver via QA or OSQL.
> HTH
> Jerry
> "brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
> news:8A49CC26-4F93-49C7-9407-8451F2C38648@.microsoft.com...
> > Hi All,
> >
> > I have about 60 or so SQL Servers to register. I don't have the option of
> > importing from another machine.
> >
> > I think I maybe able to do this via SQL-DMO but not sure how to get
> > started.
> >
> > Does anyone have any ideas about doing mass SQl Server registrations?
> >
> > Thanks.
>
>|||OK. I have made a little progress.
I got the script to create the Server Groups in EM but it keeps crashing
with a memory error when I try to add the RegisteredServer object to the
RegisteredServers collection. In other words, I am unable to start
registering servers.
I have included the vbscript below:
' Declare variables
Const ForReading = 1, ForWriting = 2
Dim dmoApp, dmoServerGroup, dmoRegServer
Dim fso, MyFile, strServerName
on error resume Next
' Create a ref to the SQL Server Object
Set dmoApp = Wscript.CreateObject("SQLDMO.Application")
' Create a ServerGroup Object
' Set dmoServerGroup = CreateObject("SQLDMO.ServerGroup")
Set dmoServerGroup = Wscript.CreateObject("SQLDMO.ServerGroup")
' Add the CRAWLEY Server Group name
dmoServerGroup.Name = "CRAWLEY"
dmoApp.ServerGroups.Add(dmoServerGroup)
' Add the TAMPA Server Group name
dmoServerGroup.Name = "TAMPA"
dmoApp.ServerGroups.Add(dmoServerGroup)
' Register the TAMPA Servers
Set dmoRegServer = Wscript.CreateObject("SQLDMO.RegisteredServer")
dmoRegServer.UseTrustedConnection = 1
Set fso = Wscript.CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.OpenTextFile("C:\RWray\VBScripts\TampaServers.txt",
ForReading, True)
Do Until MyFile.AtEndOfStream
strServerName = MyFile.Readline
dmoRegServer.Name = strServerName
Wscript.Echo strServerName
dmoServerGroup.RegisteredServers.Add(dmoRegServer)
WScript.Echo Err.Description
Loop
MyFile.Close
Set dmoApp = Nothing
Set dmoServerGroup = Nothing
set dmoRegServer = Nothing
It crashes on the line
dmoServerGroup.RegisteredServers.Add(dmoRegServer).
Thanks.
"brawtaman" wrote:
> Thanks. I will try this.
> Will keep the community posted ( pun intended)!
> "Jerry Spivey" wrote:
> > Check out:
> >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_ob_r_62pg.asp
> >
> > Haven't tried it but you might also be able to create a query and loop for
> > sp_addserver via QA or OSQL.
> >
> > HTH
> >
> > Jerry
> > "brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
> > news:8A49CC26-4F93-49C7-9407-8451F2C38648@.microsoft.com...
> > > Hi All,
> > >
> > > I have about 60 or so SQL Servers to register. I don't have the option of
> > > importing from another machine.
> > >
> > > I think I maybe able to do this via SQL-DMO but not sure how to get
> > > started.
> > >
> > > Does anyone have any ideas about doing mass SQl Server registrations?
> > >
> > > Thanks.
> >
> >
> >

Sunday, March 25, 2012

Bulk Insert: One Row not importing in...

Ok so I got this working:

BULK INSERT

dbo.tbCheckPointTest

FROM 'c:\inetpub\wwwroot\upload\maxie_task_aging.csv'

WITH

(DATAFILETYPE = 'char', FIELDTERMINATOR = ',',

FIRSTROW = 2, ROWS_PER_BATCH = 6, ROWTERMINATOR = '\n')

My data file looks like this:

REPORT_EXECUTED_ON:20070403_170001 Research 306 263 2470 State 1031 7 0 Program 13 1 0 Program Tech 85 69 139

Total of 6 rows, so I indicate to start on Row 2, yet only the last three lines are getting into my table.

I have changed and manipulated the query varies counts, but still just the last three lines get imported.

Thoughts?

Thanks!

Hi,

From the looks of your data file, it is difficult to tell how many blank lines are associated with your data file.

It appears that "Research" may begin on line 4, in which case you would have only three rows of data: "Research", "State", and "Program".

I am confused by the fact that you get the last three rows: "State", "Program", and "Program Tech".

Is it possible that some rows are terminated by "0A0D" and others only by "0A" or "0D", and "\n" is being interpreted as strictly "0A0D" (hexadecimal)?

I also am confused by your use of "FIELDTERMINATOR = ',' ", since I don't see any commas in the data you have displayed.

Dan

|||

Sorry. I had the .csv open with excel when I copied and pasted it:

REPORT_EXECUTED_ON:20070404_110001

Research Specialist - Research , 340, 91, 2635
State Worker (Interim) , 918, 3, 0
Program Specialist - Quality Assurance Specialist , 16, 1, 0
Program Technician - Data Entry Hold , 82, 28, 160

I am going to see if anything you suggested will help.

|||

The problem seems to be that the BULK INSERT function seems to expect all the rows in the file to have the correct number of fields for the target table (even those being skipped). So it is ignoring the newlines until it has found enough commas (and then counts any other commas until it finds the row terminator as part of that field).

Try loading the file:

REPORT_EXECUTED_ON:20070404_110001,,,
,,,
Research Specialist - Research , 340, 91, 2635
State Worker (Interim) , 918, 3, 0
Program Specialist - Quality Assurance Specialist , 16, 1, 0
Program Technician - Data Entry Hold , 82, 28, 160

Though you will need a FIRSTROW = 3

If you put the last field into a varchar rather than an int then if you add commas to the end (e.g. 2635,,,,) then you will find that these commas end up in that field (if not then it will complain about unexpected characters when it tries to convert it to an int or whatever).

So any rows to be skipped should have the correct number of field separators in them (3) or they will not be included in the skip count.

|||

Thank you, that solved the issue. Now I have to get w/the programmer who delivers the file to me.

|||

Nice catch, Dhericean!

Dan

Bulk Insert/ one column

I have no problem importing a file.txt to my table (mehet).

Bulk Insert mehet From 'C:\test.txt'

With (DataFileType = 'char', FIELDTERMINATOR = ',')

But I would appreciated if someone could help me how to import only 1 or 2 columns.

instead of all columns.

Thanks.

juvan

hi,

you can perhaps have a look at format file support, that can be used by BULK operations..

define a file to be imported, say
d:\imp\data.txt like
[d:\imp\data.txt]
Andrea,Montanari,5
Juvan,Juvan,2
[/d:\imp\data.txt]

you can then use a format file to specify the required mappings... define a first one like
[fmt.txt]
9.0
3
1 SQLCHAR 0 10 "," 1 lName ""
2 SQLCHAR 0 10 "," 2 fName ""
3 SQLCHAR 0 7 "\r\n" 3 Id ""
[/fmt.txt]

define another fmt file like

[fmt2.txt]
9.0
3
1 SQLCHAR 0 10 "," 1 lName ""
2 SQLCHAR 0 10 "," 2 fName ""
3 SQLCHAR 0 7 "\r\n" 0 Id ""
[/fmt2.txt]

all included columns will be mapped to a column of the target table (see SQL script);

SET NOCOUNT ON;

USE tempdb;

CREATE TABLE dbo.Test (

fName varchar(10),

lName varchar(10) DEFAULT 'not set',

Id int DEFAULT -1000

);

BULK INSERT dbo.Test

FROM 'd:\imp\Data.txt'

WITH (formatfile='d:\imp\fmt.txt');

SELECT * FROM dbo.Test;

PRINT 'modify the fmt file putting 0 in the ID column as fmt2.txt';

BULK INSERT dbo.Test

FROM 'd:\imp\Data.txt'

WITH (formatfile='d:\imp\fmt2.txt');

SELECT * FROM dbo.Test;

PRINT 'just use an INSERT ... specifying the required colums';

SELECT lName

FROM OPENROWSET(BULK 'd:\imp\Data.txt',

FORMATFILE='d:\imp\fmt2.txt'

) AS t1;

INSERT INTO dbo.Test (fName)

SELECT lName

FROM OPENROWSET(BULK 'd:\imp\Data.txt',

FORMATFILE='d:\imp\fmt2.txt'

) AS t1;

SELECT * FROM dbo.Test;

DROP TABLE dbo.Test;

--<-

fName lName Id

- - --

Andrea Montanari 5

Juvan Juvan 2

modify the fmt file putting 0 in the ID column as fmt2.txt

fName lName Id

- - --

Andrea Montanari 5

Juvan Juvan 2

Andrea Montanari -1000

Juvan Juvan -1000

just use an INSERT ... specifying the required colums

lName

Andrea

Juvan

fName lName Id

- - --

Andrea Montanari 5

Juvan Juvan 2

Andrea Montanari -1000

Juvan Juvan -1000

Andrea not set -1000

Juvan not set -1000

as you can see, you can play with the fmt file as required, excluding unwanted colums at the retrival source time, or later if you just use an INSERT SELECT FROM OPENROWSET..

regards

|||

Hi Andrea,

Thanks for your help. I got it right thanks to you.

I have one more question, if you don't mind?

Sometimes I go out of town where there has no internet. It is possible to connect to my Database at home by using

phoneline direct? If there is a way, Can you let me know please.

Thanks.

Juvan

|||

hi,

you can connect over the net to your server from your home pc... but a direct SQL Server service open over the internet is not that secure... you should eventually read about V(irtual) P(rivate) N(etwork) connections to protect your server and not to allow someone to just open a connection over your TCP/IP xxx-xxx-x-xxx IP address ...

stay secure as you can

regards

|||

hi,

Yes, but as I have said that the area where I go to has no internet connection at all. So in this case what should I do?

Thanks.

Juvan

Bulk Insert with text Qualifier

Hi,
I am tring to Bulk Insert a file that has " as text Qualifier. When I bulk
insert this is importing the quotes. I would like to be able to import
without the quotes.
Please let me know if you know how..
Thank you
Sambluefish (bluefish@.discussions.microsoft.com) writes:
> I am tring to Bulk Insert a file that has " as text Qualifier. When I bulk
> insert this is importing the quotes. I would like to be able to import
> without the quotes.
You probably need to use a format file. Without further information,
about your table and data, I can only refer to read about format files
in Books Onlines.
Since the odds are good that Books Online will leave you in a maze
(the topic on format files is not easily digested), you may need further
assistance. In such case, post your table definition and a sample file.
(If the file is wide, over 70 chars wide, please place it as an
attachment, so it does not get wrecked in transport.)
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|||Thank you very much for your help in this. I have posted a sample table and
a
data file.
Table:
CREATE TABLE [dbo].[TEST] (
[Col1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[col2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Contenet in my FMT file:
8.0
2
1 SQLCHAR 0 10 "," 1 Col1 SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 0 10 "\r\n" 2 Col2 SQL_Latin1_General_Cp437_BIN
And my data file has following data
"My","World"
I am still unable to get the data in without ". I have checked the format
file sections in books online and google- but without much luck.
Your help in this is appreciated.
Sam
"Erland Sommarskog" wrote:

> bluefish (bluefish@.discussions.microsoft.com) writes:
> You probably need to use a format file. Without further information,
> about your table and data, I can only refer to read about format files
> in Books Onlines.
> Since the odds are good that Books Online will leave you in a maze
> (the topic on format files is not easily digested), you may need further
> assistance. In such case, post your table definition and a sample file.
> (If the file is wide, over 70 chars wide, please place it as an
> attachment, so it does not get wrecked in transport.)
>
> --
> 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
>|||bluefish (bluefish@.discussions.microsoft.com) writes:
> Thank you very much for your help in this. I have posted a sample table
> and a data file.
> Table:
> CREATE TABLE [dbo].[TEST] (
> [Col1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [col2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Contenet in my FMT file:
> 8.0
> 2
> 1 SQLCHAR 0 10 "," 1 Col1 SQL_Latin1_General_Cp437_BIN
> 2 SQLCHAR 0 10 "\r\n" 2 Col2 SQL_Latin1_General_Cp437_BIN
> And my data file has following data
> "My","World"
> I am still unable to get the data in without ". I have checked the format
> file sections in books online and google- but without much luck.
Hehe. That's certainly not easily digestable material.
You can do it, but I have to thank former SQL Server MVP Linda W for
learning me the trick. Here is how you format file should look like:
8.0
3
1 SQLCHAR 0 0 "\"" 0 "" ""
2 SQLCHAR 0 0 "\",\"" 1 Col1 SQL_Latin1_General_Cp437_BIN
3 SQLCHAR 0 0 "\"\r\n" 2 Col2 SQL_Latin1_General_Cp437_BIN
First of all, you need to include the " in the delimiter definition. But
the real trick is the addition of a third empty field before the first
". Not that I have a 0 in the database-column number, this column is
not to be imported.
Note: I didn't test this, as the hour is late here. But I hope it
works.
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|||This worked! Thank you very much!!
Please also convey my thanks to Ms. Linda also.
Sam
"Erland Sommarskog" wrote:

> bluefish (bluefish@.discussions.microsoft.com) writes:
> Hehe. That's certainly not easily digestable material.
> You can do it, but I have to thank former SQL Server MVP Linda W for
> learning me the trick. Here is how you format file should look like:
> 8.0
> 3
> 1 SQLCHAR 0 0 "\"" 0 "" ""
> 2 SQLCHAR 0 0 "\",\"" 1 Col1 SQL_Latin1_General_Cp437_BIN
> 3 SQLCHAR 0 0 "\"\r\n" 2 Col2 SQL_Latin1_General_Cp437_BIN
> First of all, you need to include the " in the delimiter definition. But
> the real trick is the addition of a third empty field before the first
> ". Not that I have a 0 in the database-column number, this column is
> not to be imported.
>
> Note: I didn't test this, as the hour is late here. But I hope it
> works.
> --
> 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 vs. Data Flow Task (different row results using flat file source)

I'm importing a large csv file two different ways - one with Bulk Import Task and the other way with the Data Flow Task (flat file source -> OLE DB destination).

With the Bulk Import Task I'm putting all the csv rows in one column. With the Data Flow Task I'm mapping each csv value to it's own column in the SQL table.

I used two different flat file sources and got the following:

Flat file 1: Bulk Import Task = 12,649,499 rows; Data Flow Task = 4,215,817 rows
Flat file 2: Bulk Import Task = 3,403,254 rows; Data Flow Task = 1,134,359 rows

Anyone have any guess as to why this is happening?

It seems that there's a factor of 3 in between the two components, which could mean that Flat File Connection Manager is interpereting 3 rows as 1. This might be related with the row delimiter in the Flat File connection manager.

Can you try to use preview in the Flat File connection manager, and see if rows show up correctly?

|||Yeah, thanks for the reply. I've checked the preview and it looks fine. I've also tracked down some of the skipped rows and there's nothing signficant about them. They look exactly the same as all the others. Perhaps there is a hidden character or something? Is this possible?|||

Can you compare the rows imported by FlatFile with the rows imported by Bulk insert task and see if there is any difference between them?

Thanks,
Ovidiu Burlacu

|||yes, i can do that and the resolution is that they look exactly the same. ug. there really is no indication i can see to why a rows get dropped. they are not the same type, they don't have rows before them or after them with any significant difference. it's really very puzzling.|||

Can you put a data viewer in your pipeline and see what rows pass through at execution time?

Thanks,
Ovidiu Burlacu

Thursday, March 22, 2012

BULK INSERT silent failure on one SQL7 server - Help!

quote:

> On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query that's
> importing a simple tab-tab-return text file says that the query completed
> successfully, but doesn't import any rows. The failure happens when

running

quote:

> the query via Query Analyzer running on the same machine, on and also
> through ColdFusion, itself running with full privileges.
> An sp_dboption query and Enterprise Mgr both say the 'select

into/bulkcopy'

quote:

> option is on, and this behavior happens even when logged in as the user

sa,

quote:

> with full privileges. SQL Server has been completely uninstalled and
> reinstalled, and all known service packs have been installed, up to SP4.

quote:

> is Windows 2000 Pro.
> I don't know if this is related or not, but the 'Text file' data source
> option is missing from the DTS import wizard, as it appears on that

machine,

quote:

> and also on another client machine on that network.
> Does anyone have any idea what can be done to fix the BULK INSERT problem?
> It's a serious blocking issue that needs to be resolved.
> At this point, my only ideas are to upgrade to SQL 2000, or to wipe and
> rebuild the whole machine from scratch. Both those would consume admin

time

quote:

> and take the server down far more than would be good.
> Help!
> Dave
>

|||Dave,
I have successfully run the bulk insert. This is what returned from the
select *.
Wellesley Hills MA 02481
Claremont NH 03743
Peabody MA 01960
Quincy MA 02169
Scituate MA 02066
Melrose MA 02176
West Wareham MA 02576
Lexington MA 02421
Lexington MA 02420
Springfield VA 22152
The only time I got (0 row(s) affected) is when I rollback the transaction.
So, check to see if you have a trigger that rollback the transaction.
e.g.
if exists (select * from sysobjects where id =
object_id(N'bulk_insert_test') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table bulk_insert_test
CREATE TABLE bulk_insert_test (
city varchar(2000),
state varchar(2000),
zip varchar(2000)
)
go
SET NOCOUNT ON
go
begin tran
BULK INSERT bulk_insert_test
FROM 'C:\tab_rtn_test.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
MAXERRORS = 0
)
select @.@.ROWCOUNT as row_count
rollback tran
go
select * from bulk_insert_test
go
--
-oj
http://www.rac4sql.net
"Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
news:%23RZHozG3DHA.2308@.TK2MSFTNGP11.phx.gbl...

quote:

> Thanks oj, here you go, assuming attachments are allowed here.
> Put the file tab_rtn_test.txt on the root of C, then run the .sql code in
> query analyzer in grid mode. It will create a table called

bulk_insert_test,

quote:

> dropping it first if it exists, BULK INSERT the data from the file into

it,

quote:

> and show the resulting data. In QA, result tab 1 should show the number of
> rows reported by BULK INSERT (10), tab 2 should show the data in the file,
> and Messages should show '(10 row(s) affected)'.
> Note that the test table doesn't get dropped, so you can examine it any
> other way you want, but you'll want to kill it when you're done.
> Any ideas would be hugely appreciated.
> Thanks again,
> Dave
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:Oqit0fG3DHA.3936@.TK2MSFTNGP11.phx.gbl...
> to
> that's
> completed
user[QUOTE]
SP4.[QUOTE]
source[QUOTE]
> problem?
and[QUOTE]
>
>

|||Thanks for working with me on this oj.
I don't get what you're after by checking if there's a trigger to roll it
back. The table is was created today, specifically for this test, and no
such trigger was ever designed. In fact, the table is dropped and recreated
on the fly by the test code, so there couldn't be a trigger referring to it,
right?
What I don't get is that this works fine on two other servers, but does this
weird silent failure on just this one. No separate development on this test
table has ever been done, just the code I sent, so it's very unlikely that
there's any specific trigger or other code-level difference.
Seems like it must be some kind of configuration thing I haven't thought of,
or a haunted SQL install. Fear of haunting is why we completely reinstalled,
but it made no difference.
Help!
Dave
"oj" <nospam_ojngo@.home.com> wrote in message
news:eDWHKbI3DHA.2136@.TK2MSFTNGP12.phx.gbl...

quote:

> Dave,
> I have successfully run the bulk insert. This is what returned from the
> select *.
> Wellesley Hills MA 02481
> Claremont NH 03743
> Peabody MA 01960
> Quincy MA 02169
> Scituate MA 02066
> Melrose MA 02176
> West Wareham MA 02576
> Lexington MA 02421
> Lexington MA 02420
> Springfield VA 22152
> The only time I got (0 row(s) affected) is when I rollback the

transaction.

quote:

> So, check to see if you have a trigger that rollback the transaction.
> e.g.
> if exists (select * from sysobjects where id =
> object_id(N'bulk_insert_test') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table bulk_insert_test
> CREATE TABLE bulk_insert_test (
> city varchar(2000),
> state varchar(2000),
> zip varchar(2000)
> )
> go
> SET NOCOUNT ON
> go
> begin tran
> BULK INSERT bulk_insert_test
> FROM 'C:\tab_rtn_test.txt'
> WITH (
> FIRSTROW = 2,
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n',
> MAXERRORS = 0
> )
> select @.@.ROWCOUNT as row_count
> rollback tran
> go
> select * from bulk_insert_test
> go
> --
> -oj
> http://www.rac4sql.net
>
> "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> news:%23RZHozG3DHA.2308@.TK2MSFTNGP11.phx.gbl...
in[QUOTE]
> bulk_insert_test,
> it,
of[QUOTE]
file,[QUOTE]
able[QUOTE]
best[QUOTE]
also[QUOTE]
> user
> SP4.
> source
> and
admin[QUOTE]
>

|||Dave,
Try it with bcp and see if the data is committed. Also, try specifying the
object owner in the test script. Perhaps, there are multiple objects with
the same name.
-oj
http://www.rac4sql.net
"Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
news:%23TvF%230I3DHA.2460@.TK2MSFTNGP10.phx.gbl...

quote:

> Thanks for working with me on this oj.
> I don't get what you're after by checking if there's a trigger to roll it
> back. The table is was created today, specifically for this test, and no
> such trigger was ever designed. In fact, the table is dropped and

recreated

quote:

> on the fly by the test code, so there couldn't be a trigger referring to

it,

quote:

> right?
> What I don't get is that this works fine on two other servers, but does

this

quote:

> weird silent failure on just this one. No separate development on this

test

quote:

> table has ever been done, just the code I sent, so it's very unlikely that
> there's any specific trigger or other code-level difference.
> Seems like it must be some kind of configuration thing I haven't thought

of,

quote:

> or a haunted SQL install. Fear of haunting is why we completely

reinstalled,

quote:

> but it made no difference.
> Help!
> Dave
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:eDWHKbI3DHA.2136@.TK2MSFTNGP12.phx.gbl...
> transaction.
1)[QUOTE]
> in
into[QUOTE]
number[QUOTE]
> of
> file,
any[QUOTE]
> able
> best
when[QUOTE]
> also
and[QUOTE]
to[QUOTE]
that[QUOTE]
wipe[QUOTE]
> admin
>

sql

BULK INSERT silent failure on one SQL7 server - Help!

On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query that's
importing a simple tab-tab-return text file says that the query completed
successfully, but doesn't import any rows. The failure happens when running
the query via Query Analyzer running on the same machine, on and also
through ColdFusion, itself running with full privileges.
An sp_dboption query and Enterprise Mgr both say the 'select into/bulkcopy'
option is on, and this behavior happens even when logged in as the user sa,
with full privileges. SQL Server has been completely uninstalled and
reinstalled, and all known service packs have been installed, up to SP4. OS
is Windows 2000 Pro.
I don't know if this is related or not, but the 'Text file' data source
option is missing from the DTS import wizard, as it appears on that machine,
and also on another client machine on that network.
Does anyone have any idea what can be done to fix the BULK INSERT problem?
It's a serious blocking issue that needs to be resolved.
At this point, my only ideas are to upgrade to SQL 2000, or to wipe and
rebuild the whole machine from scratch. Both those would consume admin time
and take the server down far more than would be good.
Help!
DaveDave,
If you post the ddl+sample data+your bulk insert query, we might be able to
help. If this is really critical, a call to MS PSS might prove the best
option.
--
-oj
http://www.rac4sql.net
"Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
news:eqzkjZF3DHA.3468@.TK2MSFTNGP11.phx.gbl...
> On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query that's
> importing a simple tab-tab-return text file says that the query completed
> successfully, but doesn't import any rows. The failure happens when
running
> the query via Query Analyzer running on the same machine, on and also
> through ColdFusion, itself running with full privileges.
> An sp_dboption query and Enterprise Mgr both say the 'select
into/bulkcopy'
> option is on, and this behavior happens even when logged in as the user
sa,
> with full privileges. SQL Server has been completely uninstalled and
> reinstalled, and all known service packs have been installed, up to SP4.
OS
> is Windows 2000 Pro.
> I don't know if this is related or not, but the 'Text file' data source
> option is missing from the DTS import wizard, as it appears on that
machine,
> and also on another client machine on that network.
> Does anyone have any idea what can be done to fix the BULK INSERT problem?
> It's a serious blocking issue that needs to be resolved.
> At this point, my only ideas are to upgrade to SQL 2000, or to wipe and
> rebuild the whole machine from scratch. Both those would consume admin
time
> and take the server down far more than would be good.
> Help!
> Dave
>|||Thanks oj, here you go, assuming attachments are allowed here.
Put the file tab_rtn_test.txt on the root of C, then run the .sql code in
query analyzer in grid mode. It will create a table called bulk_insert_test,
dropping it first if it exists, BULK INSERT the data from the file into it,
and show the resulting data. In QA, result tab 1 should show the number of
rows reported by BULK INSERT (10), tab 2 should show the data in the file,
and Messages should show '(10 row(s) affected)'.
Note that the test table doesn't get dropped, so you can examine it any
other way you want, but you'll want to kill it when you're done.
Any ideas would be hugely appreciated.
Thanks again,
Dave
"oj" <nospam_ojngo@.home.com> wrote in message
news:Oqit0fG3DHA.3936@.TK2MSFTNGP11.phx.gbl...
> Dave,
> If you post the ddl+sample data+your bulk insert query, we might be able
to
> help. If this is really critical, a call to MS PSS might prove the best
> option.
> --
> -oj
> http://www.rac4sql.net
>
> "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> news:eqzkjZF3DHA.3468@.TK2MSFTNGP11.phx.gbl...
> > On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query
that's
> > importing a simple tab-tab-return text file says that the query
completed
> > successfully, but doesn't import any rows. The failure happens when
> running
> > the query via Query Analyzer running on the same machine, on and also
> > through ColdFusion, itself running with full privileges.
> >
> > An sp_dboption query and Enterprise Mgr both say the 'select
> into/bulkcopy'
> > option is on, and this behavior happens even when logged in as the user
> sa,
> > with full privileges. SQL Server has been completely uninstalled and
> > reinstalled, and all known service packs have been installed, up to SP4.
> OS
> > is Windows 2000 Pro.
> >
> > I don't know if this is related or not, but the 'Text file' data source
> > option is missing from the DTS import wizard, as it appears on that
> machine,
> > and also on another client machine on that network.
> >
> > Does anyone have any idea what can be done to fix the BULK INSERT
problem?
> > It's a serious blocking issue that needs to be resolved.
> >
> > At this point, my only ideas are to upgrade to SQL 2000, or to wipe and
> > rebuild the whole machine from scratch. Both those would consume admin
> time
> > and take the server down far more than would be good.
> >
> > Help!
> >
> > Dave
> >
> >
>
begin 666 tab_rtn_test.txt
M0VET>0E3=&%T90E::7 -"E=E;&QE<VQE>2!(:6QL<PE-00DP,C0X,0T*0VQA
M<F5M;VYT"4Y("3 S-S0S#0I096%B;V1Y"4U!"3 Q.38P#0I1=6EN8WD)34$)
M,#(Q-CD-"E-C:71U871E"4U!"3 R,#8V#0I-96QR;W-E"4U!"3 R,3<V#0I7
M97-T(%=A<F5H86T)34$),#(U-S8-"DQE>&EN9W1O;@.E-00DP,C0R,0T*3&5X
G:6YG=&]N"4U!"3 R-#(P#0I3<')I;F=F:65L9 E600DR,C$U,@.T*
`
end
begin 666 bulk insert test.sql
M:68@.97AI<W1S("AS96QE8W0@.*B!F<F]M('-Y<V]B:F5C=',@.=VAE<F4@.:60@.
M/2!O8FIE8W1?:60H3B=B=6QK7VEN<V5R=%]T97-T)RD@.86YD($]"2D5#5%!2
M3U!%4E19*&ED+"!.)TES57-E<E1A8FQE)RD@./2 Q*0T*"61R;W @.=&%B;&4@.
M8G5L:U]I;G-E<G1?=&5S= T*#0I#4D5!5$4@.5$%"3$4@.8G5L:U]I;G-E<G1?
M=&5S=" H#0H)8VET>2!V87)C:&%R*#(P,# I+ T*"7-T871E('9A<F-H87(H
M,C P,"DL#0H)>FEP('9A<F-H87(H,C P,"D-"BD-"E-%5"!.3T-/54Y4($].
M#0H-"D)53$L@.24Y315)4(&)U;&M?:6YS97)T7W1E<W0-"D923TT@.)T,Z7'1A
M8E]R=&Y?=&5S="YT>'0G#0I7251(("@.-"@.E&25)35%)/5R ](#(L#0H)1DE%
M3$1415)-24Y!5$]2(#T@.)UQT)RP-"@.E23U=415)-24Y!5$]2(#T@.)UQN)RP-
M"@.E-05A%4E)/4E,@./2 P#0HI#0IS96QE8W0@.0$!23U=#3U5.5"!A<R!R;W=?
M8V]U;G0-"E-%5"!.3T-/54Y4($]&1@.T*#0IS96QE8W0@.*B!F<F]M(&)U;&M?
-:6YS97)T7W1E<W0-"@.``
`
end|||Dave,
I have successfully run the bulk insert. This is what returned from the
select *.
Wellesley Hills MA 02481
Claremont NH 03743
Peabody MA 01960
Quincy MA 02169
Scituate MA 02066
Melrose MA 02176
West Wareham MA 02576
Lexington MA 02421
Lexington MA 02420
Springfield VA 22152
The only time I got (0 row(s) affected) is when I rollback the transaction.
So, check to see if you have a trigger that rollback the transaction.
e.g.
if exists (select * from sysobjects where id =object_id(N'bulk_insert_test') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table bulk_insert_test
CREATE TABLE bulk_insert_test (
city varchar(2000),
state varchar(2000),
zip varchar(2000)
)
go
SET NOCOUNT ON
go
begin tran
BULK INSERT bulk_insert_test
FROM 'C:\tab_rtn_test.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
MAXERRORS = 0
)
select @.@.ROWCOUNT as row_count
rollback tran
go
select * from bulk_insert_test
go
--
-oj
http://www.rac4sql.net
"Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
news:%23RZHozG3DHA.2308@.TK2MSFTNGP11.phx.gbl...
> Thanks oj, here you go, assuming attachments are allowed here.
> Put the file tab_rtn_test.txt on the root of C, then run the .sql code in
> query analyzer in grid mode. It will create a table called
bulk_insert_test,
> dropping it first if it exists, BULK INSERT the data from the file into
it,
> and show the resulting data. In QA, result tab 1 should show the number of
> rows reported by BULK INSERT (10), tab 2 should show the data in the file,
> and Messages should show '(10 row(s) affected)'.
> Note that the test table doesn't get dropped, so you can examine it any
> other way you want, but you'll want to kill it when you're done.
> Any ideas would be hugely appreciated.
> Thanks again,
> Dave
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:Oqit0fG3DHA.3936@.TK2MSFTNGP11.phx.gbl...
> > Dave,
> >
> > If you post the ddl+sample data+your bulk insert query, we might be able
> to
> > help. If this is really critical, a call to MS PSS might prove the best
> > option.
> >
> > --
> > -oj
> > http://www.rac4sql.net
> >
> >
> > "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> > news:eqzkjZF3DHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query
> that's
> > > importing a simple tab-tab-return text file says that the query
> completed
> > > successfully, but doesn't import any rows. The failure happens when
> > running
> > > the query via Query Analyzer running on the same machine, on and also
> > > through ColdFusion, itself running with full privileges.
> > >
> > > An sp_dboption query and Enterprise Mgr both say the 'select
> > into/bulkcopy'
> > > option is on, and this behavior happens even when logged in as the
user
> > sa,
> > > with full privileges. SQL Server has been completely uninstalled and
> > > reinstalled, and all known service packs have been installed, up to
SP4.
> > OS
> > > is Windows 2000 Pro.
> > >
> > > I don't know if this is related or not, but the 'Text file' data
source
> > > option is missing from the DTS import wizard, as it appears on that
> > machine,
> > > and also on another client machine on that network.
> > >
> > > Does anyone have any idea what can be done to fix the BULK INSERT
> problem?
> > > It's a serious blocking issue that needs to be resolved.
> > >
> > > At this point, my only ideas are to upgrade to SQL 2000, or to wipe
and
> > > rebuild the whole machine from scratch. Both those would consume admin
> > time
> > > and take the server down far more than would be good.
> > >
> > > Help!
> > >
> > > Dave
> > >
> > >
> >
> >
>
>|||Thanks for working with me on this oj.
I don't get what you're after by checking if there's a trigger to roll it
back. The table is was created today, specifically for this test, and no
such trigger was ever designed. In fact, the table is dropped and recreated
on the fly by the test code, so there couldn't be a trigger referring to it,
right?
What I don't get is that this works fine on two other servers, but does this
weird silent failure on just this one. No separate development on this test
table has ever been done, just the code I sent, so it's very unlikely that
there's any specific trigger or other code-level difference.
Seems like it must be some kind of configuration thing I haven't thought of,
or a haunted SQL install. Fear of haunting is why we completely reinstalled,
but it made no difference.
Help!
Dave
"oj" <nospam_ojngo@.home.com> wrote in message
news:eDWHKbI3DHA.2136@.TK2MSFTNGP12.phx.gbl...
> Dave,
> I have successfully run the bulk insert. This is what returned from the
> select *.
> Wellesley Hills MA 02481
> Claremont NH 03743
> Peabody MA 01960
> Quincy MA 02169
> Scituate MA 02066
> Melrose MA 02176
> West Wareham MA 02576
> Lexington MA 02421
> Lexington MA 02420
> Springfield VA 22152
> The only time I got (0 row(s) affected) is when I rollback the
transaction.
> So, check to see if you have a trigger that rollback the transaction.
> e.g.
> if exists (select * from sysobjects where id => object_id(N'bulk_insert_test') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table bulk_insert_test
> CREATE TABLE bulk_insert_test (
> city varchar(2000),
> state varchar(2000),
> zip varchar(2000)
> )
> go
> SET NOCOUNT ON
> go
> begin tran
> BULK INSERT bulk_insert_test
> FROM 'C:\tab_rtn_test.txt'
> WITH (
> FIRSTROW = 2,
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n',
> MAXERRORS = 0
> )
> select @.@.ROWCOUNT as row_count
> rollback tran
> go
> select * from bulk_insert_test
> go
> --
> -oj
> http://www.rac4sql.net
>
> "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> news:%23RZHozG3DHA.2308@.TK2MSFTNGP11.phx.gbl...
> > Thanks oj, here you go, assuming attachments are allowed here.
> >
> > Put the file tab_rtn_test.txt on the root of C, then run the .sql code
in
> > query analyzer in grid mode. It will create a table called
> bulk_insert_test,
> > dropping it first if it exists, BULK INSERT the data from the file into
> it,
> > and show the resulting data. In QA, result tab 1 should show the number
of
> > rows reported by BULK INSERT (10), tab 2 should show the data in the
file,
> > and Messages should show '(10 row(s) affected)'.
> >
> > Note that the test table doesn't get dropped, so you can examine it any
> > other way you want, but you'll want to kill it when you're done.
> >
> > Any ideas would be hugely appreciated.
> >
> > Thanks again,
> >
> > Dave
> >
> >
> > "oj" <nospam_ojngo@.home.com> wrote in message
> > news:Oqit0fG3DHA.3936@.TK2MSFTNGP11.phx.gbl...
> > > Dave,
> > >
> > > If you post the ddl+sample data+your bulk insert query, we might be
able
> > to
> > > help. If this is really critical, a call to MS PSS might prove the
best
> > > option.
> > >
> > > --
> > > -oj
> > > http://www.rac4sql.net
> > >
> > >
> > > "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> > > news:eqzkjZF3DHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > > On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query
> > that's
> > > > importing a simple tab-tab-return text file says that the query
> > completed
> > > > successfully, but doesn't import any rows. The failure happens when
> > > running
> > > > the query via Query Analyzer running on the same machine, on and
also
> > > > through ColdFusion, itself running with full privileges.
> > > >
> > > > An sp_dboption query and Enterprise Mgr both say the 'select
> > > into/bulkcopy'
> > > > option is on, and this behavior happens even when logged in as the
> user
> > > sa,
> > > > with full privileges. SQL Server has been completely uninstalled and
> > > > reinstalled, and all known service packs have been installed, up to
> SP4.
> > > OS
> > > > is Windows 2000 Pro.
> > > >
> > > > I don't know if this is related or not, but the 'Text file' data
> source
> > > > option is missing from the DTS import wizard, as it appears on that
> > > machine,
> > > > and also on another client machine on that network.
> > > >
> > > > Does anyone have any idea what can be done to fix the BULK INSERT
> > problem?
> > > > It's a serious blocking issue that needs to be resolved.
> > > >
> > > > At this point, my only ideas are to upgrade to SQL 2000, or to wipe
> and
> > > > rebuild the whole machine from scratch. Both those would consume
admin
> > > time
> > > > and take the server down far more than would be good.
> > > >
> > > > Help!
> > > >
> > > > Dave
> > > >
> > > >
> > >
> > >
> >
> >
> >
>|||Dave,
Try it with bcp and see if the data is committed. Also, try specifying the
object owner in the test script. Perhaps, there are multiple objects with
the same name.
--
-oj
http://www.rac4sql.net
"Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
news:%23TvF%230I3DHA.2460@.TK2MSFTNGP10.phx.gbl...
> Thanks for working with me on this oj.
> I don't get what you're after by checking if there's a trigger to roll it
> back. The table is was created today, specifically for this test, and no
> such trigger was ever designed. In fact, the table is dropped and
recreated
> on the fly by the test code, so there couldn't be a trigger referring to
it,
> right?
> What I don't get is that this works fine on two other servers, but does
this
> weird silent failure on just this one. No separate development on this
test
> table has ever been done, just the code I sent, so it's very unlikely that
> there's any specific trigger or other code-level difference.
> Seems like it must be some kind of configuration thing I haven't thought
of,
> or a haunted SQL install. Fear of haunting is why we completely
reinstalled,
> but it made no difference.
> Help!
> Dave
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:eDWHKbI3DHA.2136@.TK2MSFTNGP12.phx.gbl...
> > Dave,
> >
> > I have successfully run the bulk insert. This is what returned from the
> > select *.
> >
> > Wellesley Hills MA 02481
> > Claremont NH 03743
> > Peabody MA 01960
> > Quincy MA 02169
> > Scituate MA 02066
> > Melrose MA 02176
> > West Wareham MA 02576
> > Lexington MA 02421
> > Lexington MA 02420
> > Springfield VA 22152
> >
> > The only time I got (0 row(s) affected) is when I rollback the
> transaction.
> > So, check to see if you have a trigger that rollback the transaction.
> >
> > e.g.
> > if exists (select * from sysobjects where id => > object_id(N'bulk_insert_test') and OBJECTPROPERTY(id, N'IsUserTable') =1)
> > drop table bulk_insert_test
> >
> > CREATE TABLE bulk_insert_test (
> > city varchar(2000),
> > state varchar(2000),
> > zip varchar(2000)
> > )
> > go
> > SET NOCOUNT ON
> > go
> > begin tran
> >
> > BULK INSERT bulk_insert_test
> > FROM 'C:\tab_rtn_test.txt'
> > WITH (
> > FIRSTROW = 2,
> > FIELDTERMINATOR = '\t',
> > ROWTERMINATOR = '\n',
> > MAXERRORS = 0
> > )
> > select @.@.ROWCOUNT as row_count
> >
> > rollback tran
> > go
> > select * from bulk_insert_test
> > go
> > --
> > -oj
> > http://www.rac4sql.net
> >
> >
> > "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> > news:%23RZHozG3DHA.2308@.TK2MSFTNGP11.phx.gbl...
> > > Thanks oj, here you go, assuming attachments are allowed here.
> > >
> > > Put the file tab_rtn_test.txt on the root of C, then run the .sql code
> in
> > > query analyzer in grid mode. It will create a table called
> > bulk_insert_test,
> > > dropping it first if it exists, BULK INSERT the data from the file
into
> > it,
> > > and show the resulting data. In QA, result tab 1 should show the
number
> of
> > > rows reported by BULK INSERT (10), tab 2 should show the data in the
> file,
> > > and Messages should show '(10 row(s) affected)'.
> > >
> > > Note that the test table doesn't get dropped, so you can examine it
any
> > > other way you want, but you'll want to kill it when you're done.
> > >
> > > Any ideas would be hugely appreciated.
> > >
> > > Thanks again,
> > >
> > > Dave
> > >
> > >
> > > "oj" <nospam_ojngo@.home.com> wrote in message
> > > news:Oqit0fG3DHA.3936@.TK2MSFTNGP11.phx.gbl...
> > > > Dave,
> > > >
> > > > If you post the ddl+sample data+your bulk insert query, we might be
> able
> > > to
> > > > help. If this is really critical, a call to MS PSS might prove the
> best
> > > > option.
> > > >
> > > > --
> > > > -oj
> > > > http://www.rac4sql.net
> > > >
> > > >
> > > > "Dave Merrill" <dmerrillq@.usaq.netq> wrote in message
> > > > news:eqzkjZF3DHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > > > On one of 3 tested SQL7 servers, a plain vanilla BULK INSERT query
> > > that's
> > > > > importing a simple tab-tab-return text file says that the query
> > > completed
> > > > > successfully, but doesn't import any rows. The failure happens
when
> > > > running
> > > > > the query via Query Analyzer running on the same machine, on and
> also
> > > > > through ColdFusion, itself running with full privileges.
> > > > >
> > > > > An sp_dboption query and Enterprise Mgr both say the 'select
> > > > into/bulkcopy'
> > > > > option is on, and this behavior happens even when logged in as the
> > user
> > > > sa,
> > > > > with full privileges. SQL Server has been completely uninstalled
and
> > > > > reinstalled, and all known service packs have been installed, up
to
> > SP4.
> > > > OS
> > > > > is Windows 2000 Pro.
> > > > >
> > > > > I don't know if this is related or not, but the 'Text file' data
> > source
> > > > > option is missing from the DTS import wizard, as it appears on
that
> > > > machine,
> > > > > and also on another client machine on that network.
> > > > >
> > > > > Does anyone have any idea what can be done to fix the BULK INSERT
> > > problem?
> > > > > It's a serious blocking issue that needs to be resolved.
> > > > >
> > > > > At this point, my only ideas are to upgrade to SQL 2000, or to
wipe
> > and
> > > > > rebuild the whole machine from scratch. Both those would consume
> admin
> > > > time
> > > > > and take the server down far more than would be good.
> > > > >
> > > > > Help!
> > > > >
> > > > > Dave
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
>

Monday, March 19, 2012

Bulk Insert not importing all records

Hi,
I am using the bulk insert to import over 26,000,000 records about 55 bytes
long. The insert seems to only want to import 1,780,000 records. I'm not
getting any errors and this works fine on smaller files. I have increased my
commandtimout to 260000 so I don't think that is the problem. Any help would
be appreciated.
Thanks,
EllieI don't know what your problem is, but I would be inclined to try
running multiple passes, using FIRSTROW and LASTROW to load a million
at a time. There is no guarantee that the same problem, whatever it
is, will not show up anyway though.
Roy Harvey
Beacon Falls, CT
On Tue, 2 May 2006 15:51:45 -0400, "Ellie" <nospam@.nospam.net> wrote:

>Hi,
>I am using the bulk insert to import over 26,000,000 records about 55 bytes
>long. The insert seems to only want to import 1,780,000 records. I'm not
>getting any errors and this works fine on smaller files. I have increased m
y
>commandtimout to 260000 so I don't think that is the problem. Any help woul
d
>be appreciated.
>Thanks,
>Ellie
>|||Ellie (nospam@.nospam.net) writes:
> I am using the bulk insert to import over 26,000,000 records about 55
> bytes long. The insert seems to only want to import 1,780,000 records.
> I'm not getting any errors and this works fine on smaller files. I have
> increased my commandtimout to 260000 so I don't think that is the
> problem. Any help would be appreciated.
In additions to Roy's suggestion, try the -e option. This option generates
an error file, this may reveal something.
Rather than running multiple passes with -F and -L, you can set the
batch size with -b.
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|||Here is my bulk insert command. It works fine in query analyzer but not in
my VB6 application.
BULK INSERT dataconflict.dbo.tblTemp from 'c:\output\tt060302.txt'
WITH (BATCHSIZE=10000, FORMATFILE='c:\temp\Temp.fmt')
Where would I put the -e option?
Thank you for your help.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B81E3E4CCAYazorman@.127.0.0.1...
> Ellie (nospam@.nospam.net) writes:
> In additions to Roy's suggestion, try the -e option. This option generates
> an error file, this may reveal something.
> Rather than running multiple passes with -F and -L, you can set the
> batch size with -b.
>
> --
> 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|||Ellie (nospam@.nospam.net) writes:
> Here is my bulk insert command. It works fine in query analyzer but not in
> my VB6 application.
Hmmm. It's not that the VB6 application times out? The default timeout
from client APIs is 30 secounds.

> BULK INSERT dataconflict.dbo.tblTemp from 'c:\output\tt060302.txt'
> WITH (BATCHSIZE=10000, FORMATFILE='c:\temp\Temp.fmt')
> Where would I put the -e option?
Sorry, I did not observer that you were using BULK INSERT. My advice was
for command-line BCP.
BULK INSERT does not seem to have any option corresponding to -e. Anyway, if
it works from Query Analyzer, I don't think there are any errors in the
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|||Not sure what you mean by that. How would I change it? I made a change to
the commandtimeout to 0 so it is unlimited.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B8A74A8E5A5Yazorman@.127.0.0.1...
> Ellie (nospam@.nospam.net) writes:
> Hmmm. It's not that the VB6 application times out? The default timeout
> from client APIs is 30 secounds.
>
> Sorry, I did not observer that you were using BULK INSERT. My advice was
> for command-line BCP.
> BULK INSERT does not seem to have any option corresponding to -e. Anyway,
> if
> it works from Query Analyzer, I don't think there are any errors in the
> 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|||Ellie (nospam@.nospam.net) writes:
> Not sure what you mean by that. How would I change it? I made a change to
> the commandtimeout to 0 so it is unlimited.
If you already have set the commandtimeout to 0, I'm afraid that I have
no idea what is going on.
If you have the proper access on the server, you could set up a Profiler
trace, and include the events in the Errors and Exception group, to
see if this brings up something.
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|||Hi Erland,
Thanks for your help. I have managed to get all of the records imported by
changing the batchsize from 10000 to 100000. I don't know why that would
make a difference though.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B917BE14B6Yazorman@.127.0.0.1...
> Ellie (nospam@.nospam.net) writes:
> If you already have set the commandtimeout to 0, I'm afraid that I have
> no idea what is going on.
> If you have the proper access on the server, you could set up a Profiler
> trace, and include the events in the Errors and Exception group, to
> see if this brings up something.
> --
> 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|||Ellie (nospam@.nospam.net) writes:
> Thanks for your help. I have managed to get all of the records imported by
> changing the batchsize from 10000 to 100000. I don't know why that would
> make a difference though.
Neither have I!
I'm glad to hear that you got it working.
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

Sunday, March 11, 2012

Bulk Insert Import Question

Hi:
I am importing data from text files in sql tables using bulk insert object
in dts package. First row of text files contains headings. For some text
files, headings are also imported in the tables and for some headings are no
t
imported. I am not sure why is that happening.
I do not want to import headings. Is there anyway to tell bulk insert
object to not import first row (headings)? Please let me know.
Thanks a lotBULK INSERT [YourTable] FROM 'c:\yourfile.txt'
WITH (FIRSTROW = 5)
Where 5 indicates "start importing from the 5th row of text". Adjust for
your import as necessary. Don't use quotation marks around the number.
"Mike" wrote:

> Hi:
> I am importing data from text files in sql tables using bulk insert object
> in dts package. First row of text files contains headings. For some text
> files, headings are also imported in the tables and for some headings are
not
> imported. I am not sure why is that happening.
> I do not want to import headings. Is there anyway to tell bulk insert
> object to not import first row (headings)? Please let me know.
> Thanks a lot|||Thank you very much
"Mark Williams" wrote:
> BULK INSERT [YourTable] FROM 'c:\yourfile.txt'
> WITH (FIRSTROW = 5)
> Where 5 indicates "start importing from the 5th row of text". Adjust for
> your import as necessary. Don't use quotation marks around the number.
> --
>
> "Mike" wrote:
>

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

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

Bulk insert and Double Quotes

Hi,

i have been struggling with this problem for a few weeks so i am really hoping that some one can help me.

i have a system to in importing large amounts of data from text files in the a SQL 2005 DB. work fine 90% of the time but there are 2 files that are causign me really problems.

the delimiter in the fil is "," and there are commas with out quotes in some fo the fields so i can not ignor the quotes.

this command will import the files fine

Bulk Insert [RawCallData] From '\\Gci-linc-srv06\Billing\SQL Filters\GCI\Test.csv'

WITH

(FIELDTERMINATOR = '","')

but as not all the fields in [Raw Call Data] are used all of the time i need to use format files.

so my question is simple who do i do this with a format file ?

Code Snippet

Bulk Insert [RawCallData] From '\\Gci-linc-srv06\Billing\SQL Filters\GCI\Test.csv'

WITH

(FIELDTERMINATOR = '","'

, FORMATFILE = 'format_file_path')

|||

that will work, but i have to use a format file as there are 50 fields in raw call data and i am only use 19 for this file, other files that are imported use all 50.

I have found the solution my self by accident

you can use the following format file to do it

9.0
19
1 SQLCHAR 0 255 "\",\"" 1 Field1 Latin1_General_CI_AS
2 SQLCHAR 0 255 "\",\"" 2 Field2 Latin1_General_CI_AS
3 SQLCHAR 0 255 "\",\"" 3 Field3 Latin1_General_CI_AS
4 SQLCHAR 0 255 "\",\"" 4 Field4 Latin1_General_CI_AS
5 SQLCHAR 0 255 "\",\"" 5 Field5 Latin1_General_CI_AS
6 SQLCHAR 0 255 "\",\"" 6 Field6 Latin1_General_CI_AS
7 SQLCHAR 0 255 "\",\"" 7 Field7 Latin1_General_CI_AS
8 SQLCHAR 0 255 "\",\"" 8 Field8 Latin1_General_CI_AS
9 SQLCHAR 0 255 "\",\"" 9 Field9 Latin1_General_CI_AS
10 SQLCHAR 0 255 "\",\"" 10 Field10 Latin1_General_CI_AS
11 SQLCHAR 0 255 "\",\"" 11 Field11 Latin1_General_CI_AS
12 SQLCHAR 0 255 "\",\"" 12 Field12 Latin1_General_CI_AS
13 SQLCHAR 0 255 "\",\"" 13 Field13 Latin1_General_CI_AS
14 SQLCHAR 0 255 "\",\"" 14 Field14 Latin1_General_CI_AS
15 SQLCHAR 0 255 "\",\"" 15 Field15 Latin1_General_CI_AS
16 SQLCHAR 0 255 "\",\"" 16 Field16 Latin1_General_CI_AS
17 SQLCHAR 0 255 "," 17 Field17 Latin1_General_CI_AS
18 SQLCHAR 0 255 "," 18 Field18 Latin1_General_CI_AS
19 SQLCHAR 0 255 "\r\n" 19 Field19 Latin1_General_CI_AS

Saturday, February 25, 2012

BULK INSERT - insert sequence

Hello,

I am importing some tab-delimited text files into a Table. I would like to import the rows in the same sequence as displayed in the text file. BULK INSERT will do this sometimes and not other times.

Here are my test commands:

Code Snippet

deletefrom SampleReport

BULKINSERT SampleReport FROM'C:\Sample.tab'

Select*from SampleReport

As I run these commands over-and-over I notice that the insert is the same as the source file on an alternating basis.

Is this normal? Is there a way to have it Insert in the same sequence every time?

Thanks

Tom

Your query doesn't use any order by cluase. So you can't ensure wheater it is ordered properly are not.

The order sequence may change when the clustered indexed used in your table.

To ensure the order you can use the Identity column.

Here the sample,

Code Snippet

Create table #Data

(

Id int primary Key,

OrderSeq Int Identity(1,1)

)

Insert Into #Data Values (3)

Insert Into #Data Values (1)

Insert Into #Data Values (2)

Insert Into #Data Values (5)

Insert Into #Data Values (4)

--In Your Case here it is BULK INSERT

Select* from #Data Order By OrderSeq --Your Expected Order

Select* from #Data --Unexpected Order, using the Clustered Index

Drop Table #Data

|||

A table, by definition, is an unordered set of rows. There is no such thing as inserting and storing the rows in any given order. The only way you can get rows back in the order you desire is by using an ORDER BY clause on your select statement.

It may seem at times that you "always" get rows back in order of a clustered index when you do a select statement without an ORDER BY clause. however, it is never guaranteed so you should not depend on it.

|||

Thank you for taking time to answer. Too bad we cannot just add rows and get them back in the same order without adding a key.

|||

If you have a clustered index on your destination table and it's the same ordering as the input data, then you can add the ORDER hint to BULK INSERT to possibly improve performance on the load.

FROM BOL:

ORDER ( { column [ ASC | DESC ] } [ ,... n ] )

Specifies how the data in the data file is sorted. Bulk load operation performance is improved if the data loaded is sorted according to the clustered index on the table. If the data file is sorted in a different order, or there is no clustered index on the table, the ORDER clause is ignored. The column names supplied must be valid columns in the destination table. By default, the bulk insert operation assumes the data file is unordered.

Thursday, March 29, 2012

Bulk SQL Server 2000 Registration

Sunday, March 25, 2012

Bulk Insert: One Row not importing in...

Bulk Insert/ one column

Bulk Insert with text Qualifier

Bulk Insert vs. Data Flow Task (different row results using flat file source)

Thursday, March 22, 2012

BULK INSERT silent failure on one SQL7 server - Help!

BULK INSERT silent failure on one SQL7 server - Help!

Monday, March 19, 2012

Bulk Insert not importing all records

Sunday, March 11, 2012

Bulk Insert Import Question

Wednesday, March 7, 2012

Bulk Insert Bug?

Bulk Insert Bug?

Bulk Insert Bug?

Bulk insert and Double Quotes

Saturday, February 25, 2012

BULK INSERT - insert sequence

build sql script

Blog Archive

About Me