Problem with Bulk Insert - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Problem with Bulk Insert

Author

Topic

arukaru500
Starting Member

3 Posts

Posted - 2010-03-14 : 20:02:33

Hi All,

I am currently facing a problem with Bulk Insert. I am using the below code:

BULK
INSERT #connectivity
FROM 'F:\Conn\Conn.txt'
WITH
(
FIRSTROW=1,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '|'
)
GO

The first few lines of the file conn.txt are as follows:

DB-Library: Unable to connect: SQL Server is unavailable or does not exist. Unable to connect: SQL Server does not exist or network access denied.
Net-Library error 2: ConnectionOpen (Connect()).
|
XXXXXXXXX;Running;Running;XXXX;All online;Not Sent

|
YYYYYYYYYY;Running;Running;YYYYY;All Online;Not Sent

When I execute the Bulk Insert statement, it considers from the row YYYYYYYYY and skips the row XXXXXXXX..

Is there a way to import the first line XXXXXXX into the table?

Any help is appreciated.. Thanks..

arukaru500
Starting Member

3 Posts

Posted - 2010-03-14 : 20:15:32

I guess, Bulk Insert considers the below statement as one full row even though I have specified the row terminator (|). I wonder why?
Whereever, I specify the | symbol, Bulk Insert should consider it as the next row. Isn't it?

DB-Library: Unable to connect: SQL Server is unavailable or does not exist. Unable to connect: SQL Server does not exist or network access denied.
Net-Library error 2: ConnectionOpen (Connect()).
|
XXXXXXXXX;Running;Running;XXXX;All online;Not Sent

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-15 : 07:19:15

Are you sure that the f-drive is visible to the user running the sql server service account?

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-15 : 07:20:22

Put the file on the c-drive of the server just for testing purposes and see if that works first. If it in fact works then there is a problem with the access of the f-drive.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-15 : 07:23:37

Hm, I'm confused about what's really your problem since you keep describing something else than the error message points out.

Maybe your row delimiter should be "|\n"?

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

arukaru500
Starting Member

3 Posts

Posted - 2010-03-15 : 10:04:57

@ Lumbago. Thanks for your reply... I dont think its a problem with the access...

Actually, what happens is, I have a batch file which when executed connected to each server using OSQL utility, takes a SQL script as input file, executes it in the server and puts the result in a notepad.

I am trying to import the contents of the notepad to a table using Bulk Insert. Sometimes, some of the servers may not connect because of some issues because of which OSQL will write errors to the notepad.. This is where the problem is..

Consider the below scenario where OSQL was not able to connect to one of the server. So, the OSQL writes the below error to the notepad.

"DB-Library: Unable to connect: SQL Server is unavailable or does not exist. Unable to connect: SQL Server does not exist or network access denied.
Net-Library error 2: ConnectionOpen (Connect())."

Then, it connects to the second & third server using OSQL, which is successfull. So the below outputs appears in the notepad
|
XXXXXXXXX;Running;Running;XXXX;All online;Not Sent
|
YYYYYYYYYY;Running;Running;YYYYY;All Online;Not Sent

This is where the problem is... When i use Bulk Insert to import the contents of the notepad to the table, it reads the first line that starts with "DB Library........ " and it of course skips it because it does not have any delimiters... The problem is it also skips the line that starts with "XXXXXXX; Running......" (inspite of the row delimited |) and starts importing from the line "YYYYYYY;Running....".

Why is it skipping the line "XXXXXXXX; Running..."? The row delimiter is in place and it should import it to the table.

Thanks for your help and more help will be highly appreciated. Thanks...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources