Solved

BULK INSERT data conversion error (type mismatch)

Posted on 2009-05-12
22
2,029 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
  • 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now