Solved

SQL Bulk Insert // MaxErrors ignored

Posted on 2013-06-26
10
934 Views
Last Modified: 2016-02-11
The code below works fine in SSMS but fails when run under a sql job

Bulk Insert Import_Data
	From 'D:\Data\Data.txt'
	With
	(
		FIELDTERMINATOR ='\t',
		ROWTERMINATOR ='\n',
		MAXERRORS = 3000,
		FIRSTROW = 2
	)
GO

Open in new window


any ideas why?
0
Comment
Question by:ullfindsmit
  • 5
  • 5
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39280096
Does D:\Data exist on the SQL Server and does the startup account for the SQL Server service have permissions to write to that folder?
0
 
LVL 12

Author Comment

by:ullfindsmit
ID: 39280175
Yes
It errors out on data conversions after line 22k or something

The issues seems to be that on SSMS the maxerrors of 3k is fine but for some reason on the sql agent job it ignores that directive

What am I missing?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39283192
What am I missing?
I thought I had already explained that.  So let's try it again. When you run a job using SQL Server Agent it runs in a totally different security context and environment then when you run it on your workstation.

Unfortunately, until you can provide us SSIS logging showing exactly what is going on and what errors are generated there is not a lot more we can add.
0
 
LVL 12

Author Comment

by:ullfindsmit
ID: 39283417
Below is what I see when I view history of the SQL JOB
The SQL JOB (not SSIS Package) runs a script which imports a text file into the server

It keeps erroring out even after MAXERRORS is set to 300000


Date		6/27/2013 3:15:00 AM
Log		Job History (SERVER: Import)

Step ID		1
Server		WIN-RAQ4
Job Name		SERVER: Import
Step Name		Import File
Duration		00:00:52
Sql Severity	16
Sql Message ID	4864
Operator Emailed	
Operator Net sent	
Operator Paged	
Retries Attempted	0

Message
Executed as user: NT SERVICE\SQLSERVERAGENT. ...er for the specified codepage) for row 2530, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2553, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2578, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2635, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2736, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2779, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2794, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2898, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3015, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3054, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3215, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3359, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3452, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4130, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4347, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6866, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7002, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7309, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9921, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10033, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11474, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11557, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12048, column 24 (SellingPrice). 
[SQLSTATE 42000] (Error 4864)  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12075, column 24 (SellingPrice). [SQLSTATE 42...  The step failed.

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39283458
The SQL JOB (not SSIS Package) runs a script which imports a text file into the server
That makes no difference whatsoever.  They key point you should focus on is this:
Executed as user: NT SERVICE\SQLSERVERAGENT
That is not the same as you running it.

Simply put the SQL Server Agent does not have access to the resources it needs.  It could be something as trivial as a different SET option.

Please read up on the error 42000.

I honestly don't know how else to spell it out.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 12

Author Comment

by:ullfindsmit
ID: 39297894
OK
I still dont get the actual error
I dont know what the resource restriction is

I have another JOB that does something similar and never has issues.

The only thing I can think of is the number of errors when importing the file that it prints to the buffer

Is there a way to do a bulk insert without printing errors to the buffer?

I have tried writing the errors to file and still no joy
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39298829
I suggest you delete this question and re-post with any additional information that you may have.
0
 
LVL 12

Author Comment

by:ullfindsmit
ID: 39300621
I have posted the entire log info taht I get from SQL
I have also provided info on how one works and another one fails
I have also verified that another job that does similar steps works fine as well

Not sure what more info I can provide even on a new ticket
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39300905
My point was that perhaps another member can explain it a different way.  Obviously I am not getting my message across.
0
 
LVL 12

Author Closing Comment

by:ullfindsmit
ID: 39315245
I'll get that a try
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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 …
In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).  My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text fi…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

760 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

19 Experts available now in Live!

Get 1:1 Help Now