How can I avoid "Warning: String or Binary data would be truncated" in an SQL job giving me a failure

Question

I've already looked at this, but none of the solutions I've seen and tried actually solve me issue.

Background: using SQL server 2005

When I run a stored procedure to insert data into a table it works fine. If I get an SQL job to run that same stored procedure over the same data, the job runs successfully, but the data is NOT inserted into the same destination table.

I've tried the SET ansi_warnings OFF, but I get an error compiling the SQL, saying

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

Table schema:

[JobNo] [int] 
[ProductCode] [varchar](20) 
[ProductName] [varchar](200)
[ReportedAvailability] [varchar](100) 
[ReportedProductClean] [varchar](100)
[ReportedProductDate] [varchar](100) 
[ReportedProductPrice] [int] 
[ReportedCasesOrdered] [int] 
[AvailableYes] [int] 
[AvailableOOS] [int] 
[AvailableNotRanged] 
[ProductClean] [int] 
[ProductInDate] [int]
[ProductPrice] [int] 
[CasesOrdered] [int] 
[Include] [int] 
[Counter] [int] 
[UserId] [varchar](10) 
[Modified] [datetime] 

In the cursor, there is a try/catch around the insert statement, so as it processes the jobs one by one, if there is a problem, I know exactly which job it failed on, as there is an update statement to say "complete" if it's ok, "failed" if not.

All the data I am putting into this table is short enough to fit into the require columns (i.e. product names are less than 200 characters, INT values are 5 digits or less).

I get a warning, not an error, but I can't understand why it fails legitimate records when run as a SQL Job, but not when it's run as a stand alone SP.

There are no triggers on the destination table, there are a few default to 0 constraints on the INT columns (but I wouldn't have thought that would be the issue. Other tables in the same SP don't have this issue)

Thanks in advance

Answer 1

There shouldn't be any error when it comes to running a job or direct stored procedure unless there are logic within the job that causes some of your data to be longer than expected. I used so many jobs and stored procedure but I never encounter such error. On a developer standpoint, errors in jobs occur only if: 1) failure on the executing account or lacks of access, 2) there's really an error with the executing stored procedure