Exception while doing bulk insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Exception while doing bulk insert

Author

Topic

rameshkg
Starting Member

22 Posts

Posted - 2004-07-29 : 00:46:38

Hi,

I am trying to do a bulk insert of records into a table using my application. I am using prepared statement to achieve this. I am getting the following exception while doing bulk insert.

java.lang.NegativeArraySizeException

I am using SQL Server driver version 2000.80.380.00 for this. The database type chosen is JDBC-ODBC.

Your early response is appreciated.

Regards
Ramesh

nr
SQLTeam MVY

12543 Posts

Posted - 2004-07-29 : 06:02:56

Do the bulk insert from query analyser to see if it's a problem wit the data or the way you are calling it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-29 : 08:12:59

And if you are getting issues that way, post both the statement and the exect error message.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Celia
Starting Member

8 Posts

Posted - 2007-06-28 : 09:51:17

Hello.

I see this topic didn't have replies in a while. However, I'd like to ask something more about it, since I'm in a similar case at the moment.
I'm trying to import data from a file that is made in a Unix machine into a SQL Server 2000 database. I already know that I want to use BULK INSERT because it's the fastest method I found:

bulk insert Test.dbo.MYTESTTABLE
from 'C:\MyFolder\MyData.txt'
with
(
fieldterminator = '|~|',
rowterminator = '\n'

)

I'm using an ETL tool that uses JDBC. How can I make it work?

Thanks for your help

Celia

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-28 : 13:02:55

I think you want this instead:

rowterminator = char(10)

or perhaps:

rowterminator = char(13)

i can never remember which is \r and which is \n

elsasoft.org

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-06-28 : 14:28:09

Jezemine,

Just remember that on DOS it's Carriage Return/Line Feed, CRLF. CR = 13, LF = 10. :-)

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-28 : 14:43:57

I know the CRLF part. the part I can't seem to remember is that CR=13, LF=10.

perhaps I should bookmark this post.

elsasoft.org

Celia
Starting Member

8 Posts

Posted - 2007-06-29 : 02:58:52

Thanks for your interest and help.

I've already tried to execute:

bulk insert Test.dbo.MYTESTTABLE
from 'C:\MyFolder\MyData.txt'
with
(
   fieldterminator = '|~|',
   rowterminator = char(10)
)

It didn't work. Error is:

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'char'.

However, next code works pretty well:

bulk insert Test.dbo.MYTESTTABLE
from 'C:\MyFolder\MyData.txt'
with
(
   fieldterminator = '|~|',
   rowterminator = '\n'
)

I can make that statement work in Windows machines. However, I want to execute the BULK INSERT from an ETL utility (Oracle® Data Integrator), connect to SQL Server by a JDBC connection to be able to execute the statement, and connect to a Unix server with other JDBC connection because the file that it is the source of data is in a Unix server. I execute unix2dos on the file because it is generated in a Unix server, and then I want to import it into SQL Server. I'd like to avoid the FTP to move my file from Unix to Windows.

bulk insert Test.dbo.MYTESTTABLE
from '/root/MyDir/MyData.txt'    -- File in Unix
with
(
   fieldterminator = '|~|',
   rowterminator = '\n'
)

When I try to execute previous statement from Oracle® Data Integrator, I get next error:

Msg 4861, Level 16, State 1, Line 1, Sqlstate 01000
[MyWindowsServer]Could not bulk insert because file '/root/MyDir/MyData.txt' could not be opened. Operating system error code 3(The system can

I'd like to know if you think that I can do what I'd like, and if you think it is possible, how I can make it. If not, I'll have to make the FTP to move the file from Unix to Windows.

Regards,

Celia

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-29 : 10:16:58

the path you specify in the bulk insert statement needs to be visible to the sql server instance. that means it's either local to the server (like on the c: drive) or on a network share that's visible to the server, which you would normally specify with a UNC path.

I know nothing of how to get windows and unix to play nice together - but it sounds like your file is on a unix machine and the sql server instance can't find it. at a minimum it seems you'd have to specify some equivalent of a UNC path if it's on a different machine.

ps: sorry for leading you down the wrong path with the char(10) thing!

elsasoft.org

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-06-29 : 14:28:24

quote:

'/root/MyDir/MyData.txt' -- File in Unix

Your problem is with the '/root' in the path above. Oracle/SQLServer won't have access to log in as root on the Unix machine. You'll need to move the file to somewhere the database server can access.

Celia
Starting Member

8 Posts

Posted - 2007-07-02 : 05:19:45

Thanks for your help. I was trying to avoid to do the FTP, but it seems I'll have to do it.

Regards,

Celia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources