SQL Server Agent confusion

[Matt]

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

[Simon Hayes]

"Matt" <ma**@fruitsalad.org> wrote in message
news:b6**************************@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

[Matt]

"Simon Hayes" <sq*@hayes.ch> wrote in message news:<40**********@news.bluewin.ch>...

"Matt" <ma**@fruitsalad.org> wrote in message
news:b6**************************@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

[Simon Hayes]

"Matt" <ma**@fruitsalad.org> wrote in message
news:b6**************************@posting.google.c om...

"Simon Hayes" <sq*@hayes.ch> wrote in message news:<40**********@news.bluewin.ch>...

"Matt" <ma**@fruitsalad.org> wrote in message
news:b6**************************@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

[Matt]

"Simon Hayes" <sq*@hayes.ch> wrote in message news:<40********@news.bluewin.ch>...

"Matt" <ma**@fruitsalad.org> wrote in message
news:b6**************************@posting.google.c om...

"Simon Hayes" <sq*@hayes.ch> wrote in message

news:<40**********@news.bluewin.ch>...

"Matt" <ma**@fruitsalad.org> wrote in message
news:b6**************************@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

I am not sure I know what you mean by the debugger, how do I use that?

I enabled traces in query analyser but my main problem is that the
procedure and the test code always works just fine in query analyser,
but as soon as I put it into a job in sql server agent it fails the
exact same procedure or code, and this happens on two different SP's
doing inserts into two different tables now, this smells like a bug to
me but I am not skilled enough to finally determine if it is, but it
sounds very dodgy to me that the exact same code works in query
analyser but not in sql server agent as a job.

If it was the case that data would be truncated by the trigger would
it not be equally truncated by query analyser and the sql server
agent?

rgds

Matt

[ShnellShnell]

I am also having exactly the same problem

I am running a Stored Procedure which runs perfectly when run in Query Analyzer but fails every time it is run in SQL Server Agent.

Msg 8152, Sev 16: String or binary data would be truncated. [SQLSTATE 22001]
Msg 3621, Sev 16: The statement has been terminated. [SQLSTATE 01000]

PS: This is running on our old SQL 7 server, no such problems on SQL 2000 however I cant move this system onto SQL 2000 yet.

Has anyone found a solution to this issue?