?
Solved

BULK INSERT data conversion error (type mismatch)

Posted on 2009-05-12
22
Medium Priority
?
2,086 Views
Last Modified: 2013-07-20
v2000, I have a job which execs a proc;  the end result is a number of files are bulk inserted.  I walk thru a directory, throw all filenames into a working table, and then cursor thru each file name, bulk inserting each file.

This has been in places for ages, last Friday it failed, and it failed yesterday.  My logging points to a specific file (see below).  Last Friday I told my datasource people to check the file, but they couldn't find anything.  Yesterday's job failed again (same file), and yesterday's file was of zero bytes in size. (empty)

Blank files don't cause me to puke.  I simply log it as '0 records bulk inserted from filename'.

This is last Friday's error:  (I include actions before and after just for the visual)
-----------------------------------------------------------------------------------
0 Records bulk inserted to dbo.tablename from goodfile.csv.                                                        
-----------------------------------------------------------------------------------
42 Records bulk inserted to dbo.tablename from failingfile.csv.
Msg 4864, Sev 16: Bulk insert data conversion error (type mismatch) for row 6561, column 5 (FirmPosition). [SQLSTATE 42000]                                                                  
-----------------------------------------------------------------------------------
14998 Records bulk inserted to dbo.tablename from anothergoodfile.csv.


This is yesterday's error:
--------------------------------------------------------------------------------------
0 Records bulk inserted to dbo.tablename from goodfile.csv.                        
--------------------------------------------------------------------------------------
0 Records bulk inserted to dbo.tablename from failingfile.csv.
Msg 4864, Sev 16: Bulk insert data conversion error (type mismatch) for row 11675, column 5 (FirmPosition). [SQLSTATE 42000]
Msg 4864, Sev 16: Bulk insert data conversion error (type mismatch) for row 11676, column 5 (FirmPosition). [SQLSTATE 42000]
--------------------------------------------------------------------------------------
11674 Records bulk inserted to dbo.tablename from anothergoodfile.csv.

The failingfile was the same file, both times.  Again, 0 byte files don't cause failure, and yesterday's file was zero bytes in size.  It logged it properly -- '0 records bulk inserted.....'   but then it errored out with the bulk insert data conversion error.  The file was empty;  there was no conversion to perform.

Here's my format file --- though I don't think it's necessary.  As I said, this job has been in place for ages.  This is the first failure of this nature.  I was sure it must be the file content/format, but yesterday's file was zero bytes in size.  

8.0
10
1      SQLCHAR      0      1      ","      3      msgtype      SQL_Latin1_General_CP1_CI_AS
2      SQLCHAR      0      16      ","      4      orderno      SQL_Latin1_General_CP1_CI_AS
3      SQLCHAR      0      16      ","      5      clorid      SQL_Latin1_General_CP1_CI_AS
4      SQLCHAR      0      1      ","      6      ordermarking      SQL_Latin1_General_CP1_CI_AS
5      SQLCHAR      0      8      ","      7      firmposition      SQL_Latin1_General_CP1_CI_AS
6      SQLCHAR      0      8      ","      8      desiredquantity      SQL_Latin1_General_CP1_CI_AS
7      SQLCHAR      0      8      ","      9      enteredquantity      SQL_Latin1_General_CP1_CI_AS
8      SQLCHAR       0      32      ","      10       splitid      SQL_Latin1_General_CP1_CI_AS
9      SQLCHAR      0      8      ","      11      source      SQL_Latin1_General_CP1_CI_AS
10      SQLCHAR      0      8      "\n"      12      ordertime      SQL_Latin1_General_CP1_CI_AS

And here is the relevant portion of the procedure, specific to the bulk insert:
DECLARE @rowcount1 INT
SET @SQL = 'BULK INSERT dbo.tablename FROM ' + CHAR(39) + @Path + @FileName + CHAR(39) + ' WITH(FORMATFILE = ''E:\MSSQL\Tools\formatfilename.fmt'',FIELDTERMINATOR='','',ROWTERMINATOR=''\r'',FIRSTROW=1,TABLOCK,ROWS_PER_BATCH=100000)'
EXECUTE (@SQL)


I don't get it.  Does anybody else?
0
Comment
Question by:dbaSQL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 6
22 Comments
 
LVL 6

Expert Comment

by:openshac
ID: 24363411
Are you having any problems with disk space here?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24363426
nope.  that particular disk has near 2 TB available
the oddity, in my opinion, is the fact that yesterday's file was empty.  why the failure on an empty file?!!!
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24363447
i hit a target directory, throw all filenames into a working table, and then cursor thru them, bulk inserting each.

there are two other files yesterday that were zero bytes, i walked thru them without error.
again, it's not the zero byte file.  

there were 15 files in total -- i walked thru them all, as i always do, but this is the only one that is crying, and it's not new.  this particular file has been part of the datasource for 6 months easy.  

friday, it had data.  yesterday it was empty
same file, same error upon each failure.  

i just don't see what would be causing this
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 17

Author Comment

by:dbaSQL
ID: 24363537
and, i just now attempted the same bulk insert myself, into a temp table, same file as yesterday's failure.  
it worked fine

nothing was written, due to the empty file, but it did not issue the error

this makes little sense
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24363627
same precise code that is running within the procedure, just now on last friday's file.  it, too, went into my temp table without error.

and, each execution -- last friday and yesterday -- both logs show the files loaded fine each time, before the error message is issued

42 Records bulk inserted to dbo.tablename from failingfile.csv.   <<<<<loaded
Msg 4864, Sev 16: Bulk insert data conversion error (type mismatch) for row 6561, column 5 (FirmPosition). [SQLSTATE 42000]      


0 Records bulk inserted to dbo.tablename from failingfile.csv.  <<<nothing loaded, 0 bytes
Msg 4864, Sev 16: Bulk insert data conversion error (type mismatch) for row 11675, column 5 (FirmPosition). [SQLSTATE 42000]
Msg 4864, Sev 16: Bulk insert data conversion error (type mismatch) for row 11676, column 5 (FirmPosition). [SQLSTATE 42000]


why the error?  hmmmm.....
0
 
LVL 6

Expert Comment

by:openshac
ID: 24363812
What are the offending vaues in FirmPosition?

e.g. row 6561, column 5 (FirmPosition)

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24363924
but friday's failure, which reported row 6561, only had 42 records in the csv.  AND those 42 records were loaded just fine.  see my logging... in both cases, the number of records loaded is logged, then i issue error

i just don't understand what is causing this.
AND i executed precisely the same statement now, manually, into a temp table -- each file was processed fine, without error
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24363945
and that second one, on yesterday's file which was 0 bytes in size --- how the hell did it get to row 11675 when there were no rows in the file?!

0 Records bulk inserted to dbo.tablename from failingfile.csv.
Msg 4864, Sev 16: Bulk insert data conversion error (type mismatch) for row 11675, column 5 (FirmPosition). [SQLSTATE 42000]
Msg 4864, Sev 16: Bulk insert data conversion error (type mismatch) for row 11676, column 5 (FirmPosition). [SQLSTATE 42000]
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24364021
this is seriously an odd one.  previously, i'd simply ran the bulk insert statement from the procedure, against both versions of the failing file (friday, yesterday), and it processed into my temp table just fine, without error.

i just now duped the proc.  exactly the same code, only it is going into my temp table.  this table, mind you, is identical the the normal table.  i just recreated it as #2

the procedure execution against those two files  in query analyzer processed just fine, no errors retrurned

Friday's file:
0 Records bulk inserted to dbo.tablename from filename.csv

Yesterday's file:
42 Records bulk inserted to dbo.tablename from filename.csv

what the hell
0
 
LVL 6

Expert Comment

by:openshac
ID: 24364049
Seems strange, can't be sure where the underlying problem actually is.

What sort of values does column 5(FirmPosition) contain.

I am sure you have already looked but is there some way you are looking at the wrong files?  Or the proc is somehow looking at the wrong files.  As I said, I'm sure you've checked but we're all fallible to make seemingly obvious errors.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24364100
yep, i've checked.  remember, my logging returns the filename each time, along with the records loaded.  it's definitely looking at the right file, and each failure has been on the same file
BUT the error msg is returned AFTER the records in the specific file have been loaded fine.

my simulation of the execution now, however, in query analyzer, processes everything just fine, without error.

firmposition is simply numeric values;
3182
0
-42935
3200
-71057
etc.
0
 
LVL 6

Expert Comment

by:openshac
ID: 24364183
v2000, I have a job which execs a proc;
Can you post the proc?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24364321
yes.  give me just a minute to genericize a bit.  but ....

>>>>>>>
i just now duped the proc.  exactly the same code, only it is going into my temp table.  this table, mind you, is identical the the normal table.  i just recreated it as #2

the procedure execution against those two files  in query analyzer processed just fine, no errors retrurned
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24364452
this is a genericized version of the procedure.  i honestly don't think it's there, though.  the proc right now in QA processed both versions of the failing file without error



CREATE PROC [dbo].[usp_procname2]
AS
SET NOCOUNT ON
/* Allows for the bulk insert of the eod filename.csv's, which is done by the 1st step of the 'JOB NAME' SQLAgent job.
 - Any/all filename%.csv filenames found at \SHARENAME are loaded first to dbo.filenametable.
 - This table is then referenced for the subsequent bulkload into WorkingTable.  
 - Any/All dupes are flagged in dbo.WorkingTable;  all non-flagged data is then loaded in batches to dbo.TargetTable.
 - The flagged violations are then written to dbo.TargetTableDupes, and dbo.WorkingTable is truncated.
 - Thus, we've now avoided the recurring failures due to dupes found in the filename file(s) because of problems upstream at the source(s).
 
EXEC dbo.procname2 */
BEGIN
	DECLARE @FileName varchar(500), @SQL varchar(3000), @Path varchar(200)
 
	/* 1.  Clean out/reload dbo.filenametable. */
	DELETE dbo.filenametable
	SET @Path = '\\servername\sharename\'
	EXECUTE dbo.usp_ListFiles @Path,'dbo.filenametable','%filename%.csv',NULL,0
 
	/* 2. IF EXISTS, CURSOR through tbl, load all files (filenames) from step one to dbo.WorkingTable. */
	IF EXISTS(SELECT 1 FROM dbo.filenametable)
	BEGIN
		DECLARE db_cursor CURSOR FOR  
		SELECT FileName FROM dbo.filenametable
		
		OPEN db_cursor   
		FETCH NEXT FROM db_cursor INTO @FileName   
 
		WHILE @@FETCH_STATUS = 0   
		BEGIN   
			DECLARE @rowcount1 INT
			SET @SQL = 'BULK INSERT dbo.WorkingTable FROM ' + CHAR(39) + @Path + @FileName + CHAR(39) + ' WITH(FORMATFILE = ''E:\MSSQL\Tools\TargetTable.fmt'',FIELDTERMINATOR='','',ROWTERMINATOR=''\r'',FIRSTROW=1,TABLOCK,ROWS_PER_BATCH=100000)'
			EXECUTE (@SQL) 
 
			SET @rowcount1 = @@ROWCOUNT
			SELECT CONVERT(VARCHAR,@rowcount1) + ' Records bulk inserted to dbo.WorkingTable from ' +@FileName +'.'
 
	        FETCH NEXT FROM db_cursor INTO @FileName  
		END
			CLOSE db_cursor   
			DEALLOCATE db_cursor 
	END
	ELSE
		BEGIN
		PRINT 'The filename files have not been located.'
		EXEC master..xp_sendmail @recipients ='emailaddresses',@subject='JOB NAME',@message='The filename .csv''s have not been located.'
		SELECT @@ROWCOUNT -- simply in place for the java layer, to avoid a sql exception
		RETURN
	END
 
	/* 3a.  Now let's check and flag any internal dupes dbo.WorkingTable, internally first (keeping most recent). */
	DECLARE @rowcount2 INT
	UPDATE dbo.WorkingTable SET status_flag = 1
	WHERE dbo.WorkingTable.OMID <> (SELECT MAX(d.OMID) FROM dbo.WorkingTable d
	WHERE d.OrderNo = dbo.WorkingTable.OrderNo AND d.ClorID = dbo.WorkingTable.ClorID)
	OPTION (MAXDOP 1)
	SET @rowcount2 = @@ROWCOUNT
	SELECT CONVERT(VARCHAR,@rowcount2) + ' Records updated on 1st dupe update.'
 
	/* 3b. Now let's check and flag any dupes in comparison to the already-existing TargetTable data. */
	DECLARE @rowcount3 INT
	UPDATE dbo.WorkingTable SET status_flag = 1
	FROM dbo.WorkingTable
	INNER JOIN dbo.TargetTable d ON d.OrderNo = dbo.WorkingTable.OrderNo AND d.ClorID = dbo.WorkingTable.ClorID
	OPTION (MAXDOP 1)
	SET @rowcount3 = @@ROWCOUNT
	SELECT CONVERT(VARCHAR,@rowcount3) + ' Records updated on 2nd dupe update.'
 
	/* 4. Insert from dbo.WorkingTable to dbo.TargetTable where OrderNo,ClorID doesnt exist... 
	 ( Not dropping the indices w/TargetTable.  Present volumes do not warrant it, 12/3/08. )  */
	DECLARE @rowcount4 INT
	BEGIN TRANSACTION
	WHILE @@ROWCOUNT >0
	SET ROWCOUNT 500000
 
	   INSERT dbo.TargetTable (..........fields........)
	   SELECT w.field1,w.field2,.......w.firmposition,w.field5,w.field6.......
	   FROM dbo.WorkingTable w WITH (NOLOCK)
	   WHERE w.status_flag = 0
	   OPTION (MAXDOP 1)
 
	SET @rowcount4 = @@ROWCOUNT
	SET ROWCOUNT 0
	SELECT CONVERT(VARCHAR,@rowcount4) + ' Records inserted to dbo.TargetTable.'
 
	IF @@ERROR = 0
	BEGIN
		COMMIT TRAN
	END
	ELSE
	BEGIN
		ROLLBACK TRAN
	END
 
	/* 5.  First write WorkingTable to TargetTableDupes, then truncate the working table. */
	DECLARE @rowcount5 INT
	BEGIN TRANSACTION
	WHILE @@ROWCOUNT >0
	SET ROWCOUNT 100000
 
  	INSERT dbo.TargetTableDupes(..........fields........)
	SELECT w.field1,w.field2,.......w.firmposition,w.field5,w.field6.......
	FROM dbo.WorkingTable w WITH (NOLOCK) WHERE status_flag = 1
	AND NOT EXISTS (SELECT 1 FROM dbo.TargetTableDupes d WHERE w.OrderNo = d.OrderNo AND w.ClorID = d.ClorID)
 
	SET @rowcount5 = @@ROWCOUNT
	SET ROWCOUNT 0
	SELECT CONVERT(VARCHAR,@rowcount5) + ' Records written to dbo.TargetTableDupes.'
 
	IF @@ERROR = 0
	BEGIN
	  TRUNCATE TABLE WorkingTable
	  COMMIT TRAN
	END
	ELSE	
	BEGIN
	  ROLLBACK TRAN
  	END
    /*	 6. EXEC the bat file to rename/relocate the current .csv's. */
	EXEC master..xp_cmdshell '\\servername\E$\MSSQL\Tools\filename.bat', no_output  */
END
 
SET NOCOUNT OFF
GO

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24366904
Does anybody have any insight?   I just rescripted the whole damned thing from the ground up -- procs, tables, sql agent job -- everything.  And I ran it on the dev bed against the same fileset.

It, too, fails w/the same error.  BUT... it only fails when i execute it from the Agent.
Otherwise, end to end, there are no failures.  (meaning, if i run all the job steps from QA)
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24366992
retract.  i just ran it on last night's fileset.  in QA.  the 1st job step fails, same error
it's got to be the proc, not the files  (that's the one i posted up there)
still looking
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24367245
disregard.  i got it.  unfreekingbelievable
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24367289
the logging in both cases points to failingfile.csv

-----------------------------------------------------------------------------------
0 Records bulk inserted to dbo.tablename from goodfile.csv.                                                        
-----------------------------------------------------------------------------------
42 Records bulk inserted to dbo.tablename from failingfile.csv.
Msg 4864, Sev 16: Bulk insert data conversion error (type mismatch) for row 6561, column 5 (FirmPosition). [SQLSTATE 42000]                                                                  
-----------------------------------------------------------------------------------
14998 Records bulk inserted to dbo.tablename from anothergoodfile.csv.



--------------------------------------------------------------------------------------
0 Records bulk inserted to dbo.tablename from goodfile.csv.                        
--------------------------------------------------------------------------------------
0 Records bulk inserted to dbo.tablename from failingfile.csv.
Msg 4864, Sev 16: Bulk insert data conversion error (type mismatch) for row 11675, column 5 (FirmPosition). [SQLSTATE 42000]
Msg 4864, Sev 16: Bulk insert data conversion error (type mismatch) for row 11676, column 5 (FirmPosition). [SQLSTATE 42000]
--------------------------------------------------------------------------------------
11674 Records bulk inserted to dbo.tablename from anothergoodfile.csv.



but, in both cases, it was 'anothergoodfile.csv'.  and yes, on line 6561 of the file i'm looking at right now, firmposition is crap

as i figured at the point of the original failure, it was the file content/format

lord knows how the hell i didn't see it before this

i will close the inquiry
thank you for looking
0
 
LVL 6

Expert Comment

by:openshac
ID: 24372128
So it was the FirmPosition field and it was the a duff value.

Glad to have been able to help you solve your problem.
0
 
LVL 6

Accepted Solution

by:
openshac earned 2000 total points
ID: 24372155
The solution was suggested at:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24400856.html#a24363812
What are the offending vaues in FirmPosition?e.g. row 6561, column 5 (FirmPosition)

and

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24400856.html#a24364049
What sort of values does column 5(FirmPosition) contain.I am sure you have already looked but is there some way you are looking at the wrong files?


Original poster agrees with this solution:
"and yes, on line 6561 of the file i'm looking at right now, firmposition is crap"
the logging in both cases points to failingfile.csv ... but, in both cases, it was 'anothergoodfile.csv'.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24373277
openshac, i disagree slightly that the soution was suggested above --- but only because I was targeting the wrong file.  the errors as reported really implied it was the failingfile.csv, not anothergoodfile.csv.  just given the sequence of the process and error reporting.  

also interesting to note, the records were still loaded from the file where the firmposition was suspect.

I will award you the points
thank you for your input
0
 
LVL 12

Expert Comment

by:ullfindsmit
ID: 39341689
I apologize in advance for doing this, but can you please look at http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28180944.html and see if you have any suggestions
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question