Solved

SQL Bulk Insert // MaxErrors ignored

Posted on 2013-06-26
10
1,047 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
[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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

632 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