"Matt" <matt@fruitsalad.org> wrote in message
news:b609190f.0404060345.6b309516@posting.google.c om...
> "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
Unfortunately, when I run your code I get many errors due to missing
defaults, and NOT NULL columns in your table, so I can't reproduce the
error. I would guess that somewhere in your trigger code some data is being
truncated, perhaps during a CONVERT() - you could use the stored procedure
debugger to investigate. You can only debug a procedure, so just create a
simple procedure with your INSERT statement and then debug it - when the
triggers fire, the debugger will step into the trigger code, and you can
follow the execution there.
Simon