Bulk insert does nothing

  • 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

  • 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?

  • <<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

     

     

  • 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.

  • 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!

  • 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.

  • 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.

     

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

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