July 8, 2005 at 2:24 am
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
July 8, 2005 at 8:16 am
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?
July 8, 2005 at 8:26 am
<<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
February 16, 2007 at 2:48 pm
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.
February 19, 2007 at 11:03 am
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
February 19, 2007 at 11:16 am
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.
February 27, 2007 at 11:42 am
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