title: How can I avoid "Warning: String or Binary data would be truncated" in an SQL job giving me a failure
body: <p>I've already looked at this, but none of the solutions I've seen and tried actually solve me issue. </p>
  
  <p>Background: using SQL server 2005</p>
  
  <p>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. </p>
  
  <p>I've tried the <code>SET ansi_warnings OFF</code>, but I get an error compiling the SQL, saying </p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>Table schema:</p>
  
  <pre><code>[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] 
  </code></pre>
  
  <p>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. </p>
  
  <p>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). </p>
  
  <p>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. </p>
  
  <p>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)</p>
  
  <p>Thanks in advance</p>