Stored procedure runs successfully but fails when called from a job

Question

I'm running into a very bizarre error in SQL that I would like some help with. The code below creates the two stored procedures I need for this report:

USE [ONDTTEST]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE ONDT_OPS_OTD_STEP1 
AS
    BEGIN
        SET NOCOUNT ON;

        /** Create Table **/
        CREATE TABLE ONDT_OPS_OTD_SMARTVIEW
        (
            ID      varchar(15) NOT NULL,
            STATUS  char(1) NOT NULL
        );

        /** Insert data into table **/
        INSERT INTO      [ONDT_OPS_OTD_SMARTVIEW]
        SELECT           [ID]
                        ,[STATUS]
        FROM             [CUSTOMER_ORDER]
        WHERE            [STATUS] = 'H';

        /** Update customer order table from H to R **/
        UPDATE           [CUSTOMER_ORDER]
        SET              [STATUS] = 'R'
        WHERE            [STATUS] = 'H';
    END
GO

CREATE PROCEDURE ONDT_OPS_OTD_STEP2 
AS
    BEGIN
        SET NOCOUNT ON;

        /** Update records back to H **/
        UPDATE  [CUSTOMER_ORDER]
        SET     [STATUS] = 'H'
        WHERE   [ID] IN (SELECT [ID] FROM [ONDT_OPS_OTD_SMARTVIEW]);

        /** Drop Table **/
        DROP TABLE ONDT_OPS_OTD_SMARTVIEW;
    END
GO

When I execute both stored procedures it runs succesfully, however when I call the stored procedure from a scheduled job it yields the following error:

Executed as user: admin. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.

I ran profiler and saw that it fails at the update step to the CUSTOMER_ORDER table on both procedures. The Status column is set as char(1) and the stored procedure works correctly when called from a query but it is failing when being called from a job.

Thanks

[I shared it using pastebin](http://pastebin.com/gK3zTsdc). Thanks. — Rafael Sampaio, Feb 12 '15 at 16:40
@RafaelSampaio he said "schema" as in just the column names and data types for the table, not all of that information you just dumped. As in `ID int, Status char(1)...` etc — Tanner, Feb 12 '15 at 16:44
@SoulTrain The schema is ID varchar(15), STATUS char(1), thanks for clarifying that — Rafael Sampaio, Feb 12 '15 at 16:46
Thanks for the info. Everythings looks in order IMO. Just to be safe, could you specify column names in your insert statement. Like `INSERT INTO [ONDT_OPS_OTD_SMARTVIEW] ( [ID] ,[STATUS]) SELECT [ID] ,[STATUS] FROM [CUSTOMER_ORDER] WHERE [STATUS] = 'H';` and see if it helps — SoulTrain, Feb 12 '15 at 16:55
did you drop the table 'ONDT_OPS_OTD_SMARTVIEW' after you run the query outside the job? — Dudi Konfino, Feb 15 '15 at 13:27

score 0 · Accepted Answer · answered Feb 15 '15 at 16:39

After a lot of struggle I found the culprit. It turns out that SQL does not like the fact that the user name running the job is over 50 characters long. The query works fine when I added:

EXEC AS USER = 'sysadm'

Which is my SQL admin account (same rights as the domain one). Hopefully this will save someone from the headache it caused me.

Thanks to everyone who pitched in to help, you guys are great.

Regards,

Stored procedure runs successfully but fails when called from a job

1 Answers1