sqlsql-server-2005sql-server-2008bulkinsert

Issue with bulk insert

I am trying to insert the data from this link to my SQL server https://www.ian.com/affiliatecenter/include/V2/CityCoordinatesList.zip

I created the table

CREATE TABLE [dbo].[tblCityCoordinatesList](
    [RegionID] [int] NOT NULL,
    [RegionName] [nvarchar](255) NULL,
    [Coordinates] [nvarchar](4000) NULL
) ON [PRIMARY]

And I am running the following script to do the bulk insert

BULK INSERT tblCityCoordinatesList
FROM 'C:\data\CityCoordinatesList.txt' 
WITH 
( 
    FIRSTROW = 2, 
    MAXERRORS = 0, 
    FIELDTERMINATOR = '|', 
    ROWTERMINATOR = '\n'
)

But the bulk insert fails with following error

Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

When I google, I found several articles which says the issue may be with RowTerminator, but I tried everything like \n\r, \n etc, but nothing is working.

Could anyone please help me to insert this data into my database?

Solution

  • Try ROWTERMINATOR = '0x0a'. it should work.