Hi
I am getting the following exception in Sql in bulk Insert.
I ma getting this error in
sqlCmd.ExecuteNonQuery();
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
The Stroed Procedure is :
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[PSGTMS].[tmsp_CustomerContactInsert_CN]') AND type in (N'P', N'PC'))
DROP PROCEDURE [PSGTMS].[tmsp_CustomerContactInsert_CN]
GO
--Exec PSGTMS.tmsp_CustomerContactInsert_CN '\\server65\CONBULKINSERT\CustomerContact.CSV',0
CREATE PROCEDURE [PSGTMS].[tmsp_CustomerContactInsert_CN]
(
@filename VARCHAR(255),
@retval INT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @stmt NVARCHAR(MAX)
SET @retval = 1
IF OBJECT_ID('tempdb.dbo.#CustomerContact_CN') is not null
BEGIN
DROP TABLE #CustomerContact_CN
END
CREATE TABLE #CustomerContact_CN (
CustomerNumber CHAR(10),
LastName VARCHAR(30),
FirstName VARCHAR(15),
Telephone VARCHAR(16),
Extension VARCHAR(6),
Fax VARCHAR(16),
EmailID CHAR(63),
[TYPE] CHAR(3)
)
SET @stmt = 'BULK INSERT #CustomerContact_CN FROM ' + '''' + @filename + '''' +
'WITH
(
DATAFILETYPE =''widenative'',
CODEPAGE = ''RAW'',
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n''
)'
EXEC SP_EXECUTESQL @stmt
BEGIN TRANSACTION
INSERT INTO PSGAR.ARCustomerContact1
(
CustomerNo,
LastName,
FirstName,
Telephone,
Extension,
Fax,
IN_EmailId,
[Source]
)
SELECT
C.CustomerNumber,
ISNULL(C.LastName,''),
ISNULL(C.FirstName,''),
ISNULL(C.Telephone,''),
ISNULL(C.Extension,''),
ISNULL(C.Fax,''),
ISNULL(C.EmailID,''),
ISNULL(C.[TYPE],'')
FROM #CustomerContact_CN C WITH(NOLOCK)
IF @@ERROR <> 0
BEGIN
SET @retval =0
ROLLBACK TRANSACTION
RETURN
END
SET @retval =1
COMMIT TRANSACTION
SET NOCOUNT OFF
END
The Sample CSV Data is:
000006 ,Payable,Accounts,3368012065,,9058126177,,AP
000006 ,McPherson,Peter,9058126084,,9058126018,,AP
000006 ,Payable,Accounts,,,6042640252,,AP
000008 ,Drunsic,Stephen,8023621516,7,,sdrunsic@nerr.com,EBL
000008 ,Scarlotta,Christine,8023621516,3,8023621918,,AP
000027 ,Freitas,Susan,,,9058791123,susan@vaughanwood.com,AP
000033 ,Cuthbertson,Gayle,8016492090,,,gaylec@energysolutions.com,EBL
000033 ,Olson,Milo,8016492274,,,molson@energysolutions.com,EBL
000042 ,Shivji,Hafiz,4032796267,101,,accounting@classicfoodsltd.com,EBL
000042 ,Payable,Accounts,,,4032796515,accounting@classicfoodsltd.com,AP
000042 ,SHIVJI,ABID,,,4032796515,,AP
000043 ,Clausen,Larry,7084582626,,,larry@combinedwarehouse.com,EBL
000043 ,Payable,Accounts,,,7084582691,,AP
000043 ,Castaldo,Skip,,,7084582691,skip@combinedwarehouse.com,AP
000045 ,Payable,Accounts,,,4032123697,,AP
000045 ,Payable,Accounts,4036404230,,4032123724,,AP
000046 ,Stelter,Rich,,,7084964158,,AP
000046 ,Payable,Accounts,7084962485,,7084964158,,AP
000049 ,Terrio,Sandra,5146316000,,,sterrio@advantex.ca,EBL
000049 ,Richard,Mike,5146316000,,,mrichard@advantex.ca,EBL