"Simon Hayes" <sql@hayes.ch> wrote in message news:<40719e94$1_3@news.bluewin.ch>...
> "Matt" <matt@fruitsalad.org> wrote in message
> news:b609190f.0404042309.198c2276@posting.google.c om...
> > Hello
> >
> > I am running a SP from the SQL Server Agent, the job has one step that
> > looks like this.
> >
> > exec q_spr_inlevextsystem
> >
> > This job fails with the following message
> >
> > Job 'AutoInlev' : Step 1, 'Run the SP q_spr_inlevextsystem' : Began
> > Executing 2004-04-05 09:00:00
> >
> > output
> > --------------------------------------------------------------------------
> ----------------------------------------------------------------------------
> ----------------------------------------------------------------------------
> -----------------------------
> > (null)
> > Starting copy...
> > (null)
> > 1 rows copied.
> > Network packet size (bytes): 4096
> > Clock Time (ms.): total 1
> > (null)
> >
> > (0 rows(s) affected)
> >
> > Msg 8152, Sev 16: String or binary data would be truncated. [SQLSTATE
> > 22001]
> > Msg 3621, Sev 16: The statement has been terminated. [SQLSTATE 01000]
> >
> >
> >
> > however if I run that exact commandline from queryanalyzer it works
> > perfectly.
> >
> > What does SQL Server Agent do different from query analyzer? this has
> > me totally stumped.
> >
> > regards
> >
> > Matt
>
> This may be due to the settings for ANSI_WARNINGS - it's possible that you
> have it OFF in Query Analyzer (it's ON by default), but it's ON for the SQL
> Agent connection. SQL Agent uses ODBC, and this setting is ON by default for
> ODBC connections.
>
> It looks like you're calling bcp.exe from your procedure, so you may want to
> use -e to see if the problem is caused by rows which won't fit into your
> destination table.
>
> Simon


I checked the settings and they are ON in query analyzer, I dont know
how to check it for the sql server agent, and you are right I am using
xp_cmdshell to call bcp from the SP and the rows fit fine into my temp
table, I have no created some simple apps to isolate the problem and I
can easily reproduce it with the following code

declare @bestnr int,
@artnr varchar(30),
@journalnrrow int,
@bestlevant decimal,
@vb_inpris money

set @bestnr = 33434
set @artnr = '1440'
set @journalnrrow = 11
set @bestlevant = 50
set @vb_inpris = 10


insert into bpl (bestnr, artnr, jibpjournal, bestlevant, vb_inpris,
bestlevantextqty)
values (@bestnr, @artnr, @journalnrrow, @bestlevant, @vb_inpris,
@bestlevant)


the table def for the bpl table can be found at
http://donald.fruitsalad.org/bpl.txt

I scripted the table and the triggers and keys and things

regards

Matt