Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1113
  • Last Modified:

SQL Bulk Insert // MaxErrors ignored

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
ullfindsmit
Asked:
ullfindsmit
  • 5
  • 5
1 Solution
 
Anthony PerkinsCommented:
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
 
ullfindsmitAuthor Commented:
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
 
Anthony PerkinsCommented:
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
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.

 
ullfindsmitAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
ullfindsmitAuthor Commented:
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
 
Anthony PerkinsCommented:
I suggest you delete this question and re-post with any additional information that you may have.
0
 
ullfindsmitAuthor Commented:
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
 
Anthony PerkinsCommented:
My point was that perhaps another member can explain it a different way.  Obviously I am not getting my message across.
0
 
ullfindsmitAuthor Commented:
I'll get that a try
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now