Bulk insert does nothing

Question

Bulk insert does nothing

David Gilkinson

Ten Centuries

Points: 1334
More actions
July 8, 2005 at 2:24 am

#164302

Does anyone know the inner workings of the bulk insert command ?
I have an odd case of a bulk insert command working on all but one of several "identical" windows 2000 servers ( sql server 2000 sp4 )..
On the offending server,
BULK INSERT t_BulkLoad FROM 'some file' returns successfully but with 0 error code, 0 rowcount, no rows copied
There are no errors in the event log or sql server log.
DTS BuLK insert behaves in the same manner
bcp works on the same file.
Permissions are okay ( an error thrown when not correct )
The filename is being ignored as no error is thrown when a non existent file is entered in the command line.
Any help on dlls, versions, registry entries to check would be appreciated.
Thanks... Dave

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

David,

That's really weird. AKAIK, BULK INSERT is a wrapper for the same code as bcp, but apparently that doesn't seem to be the case. All the servers have SP4? Are you sure this couldn't be an SP4 bug?

David Gilkinson Ten Centuries Points: 1334 More actions · Answer 2

<<That's really weird>>

Hi Steve... weird indeed ... All the servers are on sp4.. my local server ( on xp ) is sp4 as well and that works fine...

I am guessing that the wrapper is the problem and that the odbcbcp lib is not being loaded for some reason.. I don't know how the sql server exe loads up the library...any ideas ??

Thanks Dave

Paul Jackson-273930 SSC Enthusiast Points: 160 More actions · Answer 3

Has anyone ever figured this one out? I found the problem posted several times in various forumns, but no solution.

I can even put in a filename that doesn't exist and it still says that it completed successfully!

On most servers it works correctly, and displays appropriate errors when the query is changed to be wrong. On other servers, it ALWAYS returns Command(s) completed successfully.

It seems to happen on SQL7 or 2000.

Lowell SSC Guru Points: 323518 More actions · Answer 4

what is the source of the file...i had an issue before where the source was UNIX based text files, so the terminator is not \n, but another...\r (just Cr) ? i think;

i ended up making a progrma that reformatted it to PCbased, so the row terminators wore \n (crlf) until i discovered the other row terminator items and could use the same script.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Paul Jackson-273930 SSC Enthusiast Points: 160 More actions · Answer 5

As a test, I used bcp to create a file and verified that it could be read into a 'good' server database both via bcp or using

BULK INSERT TxDB000001.dbo.Rez_Desc

FROM 'C:\SHARES\testbcp.bcp'

WITH ( CodePage = 'RAW', DataFileType='native', KEEPIDENTITY)

using Query Analyzer logged in as sa.

I copied the file to various machines and could still BULK INSERT reading it over a lan.

If I put in a bad filename I get an error saying so.

On the 'bad' server, I can put in a bad filename and it will actually say it complete successfully.

If I put in the good filename it still says completed successfully, but it will not insert any records.

If I put in a bad table name it will generate an error, so it checks that much.

Paul Jackson-403385 SSC Journeyman Points: 87 More actions · Answer 6

A co-worker tracked down the problem to one of the DLLs in C:\Program Files\Common Files\System\Ole DB becoming unregistered. He reregistered all of the DLLs and the problem was resolved. Don't know which one in particular it was.