473,390 Members | 875 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 software developers and data experts.

SQL Server Agent confusion

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
Jul 20 '05 #1
5 12759

"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
Jul 20 '05 #2
"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
Jul 20 '05 #3

"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
Jul 20 '05 #4
"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
Jul 20 '05 #5
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?
Mar 16 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Bob | last post by:
Hi Team, Is anyone else out there struggling with SQL Server on XP? Specifically, is there anyone struggling with starting the Agent Service using the NT_AUTHORITY "security" role? I am having...
60
by: Fotios | last post by:
Hi guys, I have put together a flexible client-side user agent detector (written in js). I thought that some of you may find it useful. Code is here: http://fotios.cc/software/ua_detect.htm ...
9
by: Neurocon | last post by:
Hey everyone -- I've been having this problem for a couple weeks and was hoping maybe someone can help out a little bit. Synopsis: This SQL agent job contains code to perform a full...
4
by: Prince Kumar | last post by:
I joined a company recently and they have a java program which hangs (does nothing) after a while. This is no way consistent. It could succeed quite a few times and can fail a few other times....
0
by: Suresh | last post by:
Hi Guys I have Db2 server installed on remote server. i am connecting to that remote server by using VPN. I want to connect that remote DB2 server instance using my local machine DB2...
4
by: gavino | last post by:
This seems easy but I have been asking tcl and python IRC chat all day and no one gave an answer. I have 100 servers which need a new backup server added to a text file, and then the backup...
13
by: gavino | last post by:
This seems easy but I have been asking tcl and python IRC chat all day and no one gave an answer. I have 100 servers which need a new backup server added to a text file, and then the backup agent...
1
by: vijay.db | last post by:
Hi Team, Very serious problem with my DB2 V8.1 Fixpack 6 running in AIX 5.1 machine. Every one hour my DB2 instance processes are killed and it's going down. Several trap files are generated in...
0
by: aj | last post by:
SQL Server 2005 SP2 64-bit I am quite confused about the SQL Server Agent Alert System. I have configured Database Mail, establishing my Profile(s) and Account(s), and specifying SMTP...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.