473,839 Members | 1,328 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_inlevexts ystem

This job fails with the following message

Job 'AutoInlev' : Step 1, 'Run the SP q_spr_inlevexts ystem' : 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 12777

"Matt" <ma**@fruitsala d.org> wrote in message
news:b6******** *************** ***@posting.goo gle.com...
Hello

I am running a SP from the SQL Server Agent, the job has one step that
looks like this.

exec q_spr_inlevexts ystem

This job fails with the following message

Job 'AutoInlev' : Step 1, 'Run the SP q_spr_inlevexts ystem' : 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.bluewi n.ch>...
"Matt" <ma**@fruitsala d.org> wrote in message
news:b6******** *************** ***@posting.goo gle.com...
Hello

I am running a SP from the SQL Server Agent, the job has one step that
looks like this.

exec q_spr_inlevexts ystem

This job fails with the following message

Job 'AutoInlev' : Step 1, 'Run the SP q_spr_inlevexts ystem' : 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,
bestlevantextqt y)
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**@fruitsala d.org> wrote in message
news:b6******** *************** ***@posting.goo gle.com...
"Simon Hayes" <sq*@hayes.ch > wrote in message news:<40******* ***@news.bluewi n.ch>...
"Matt" <ma**@fruitsala d.org> wrote in message
news:b6******** *************** ***@posting.goo gle.com...
Hello

I am running a SP from the SQL Server Agent, the job has one step that
looks like this.

exec q_spr_inlevexts ystem

This job fails with the following message

Job 'AutoInlev' : Step 1, 'Run the SP q_spr_inlevexts ystem' : 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,
bestlevantextqt y)
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**@fruitsala d.org> wrote in message
news:b6******** *************** ***@posting.goo gle.com...
"Simon Hayes" <sq*@hayes.ch > wrote in message

news:<40******* ***@news.bluewi n.ch>...
"Matt" <ma**@fruitsala d.org> wrote in message
news:b6******** *************** ***@posting.goo gle.com...
> Hello
>
> I am running a SP from the SQL Server Agent, the job has one step that
> looks like this.
>
> exec q_spr_inlevexts ystem
>
> This job fails with the following message
>
> Job 'AutoInlev' : Step 1, 'Run the SP q_spr_inlevexts ystem' : 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,
bestlevantextqt y)
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
ShnellShnell
1 New Member
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
1853
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 no end of grief. I started with a perfectly clean XP install. I performed an absolutely clean, default install, and the installation hung. It appeared to freeze with the Agent startup. killed the install after 30 minutes of "freeze" time, and...
60
7331
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 The detector requires javascript 1.0 to work. This translates to netscape 2.0 and IE 3.0 (although maybe IE 2.0 also works with it)
9
53478
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 database backup with verification and is writing to a local disk array. I typically also use some code for reporting - declaring variables and inserting them into
4
6604
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. There is no consistency when it fails. Could anyone here shed some light on how to debug/resolve the issue. I guess IBM looked at the issue and were not able to pinpoint where the issue is. When the program hangs and when force the DB2...
0
3929
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 development client. Bur Its gives me following error message. I searched lots of things on net and tried on remote server but i didnt got suceess. Can any one tell me how to set TCP\IP connection protocol on server for particular instance. Becuase I think
4
1476
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 restarted. If I have a list of the servers, all with same root password, and the connection is ssh. How do I connect to the server, cat the line to the config file, stop adn start the agent, and then do same to next server in list and iterate...
13
2420
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 restarted. If I have a list of the servers, all with same root password, and the connection is ssh. How do I connect to the server, cat the line to the config file, stop adn start the agent, and then do same to next server in list and iterate...
1
6493
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 the db2 diag log directory. Following the detailed entry in the db2diag.log: 2007-02-28-05.00.10.650672 Instance:****** Node:000 PID:2109578(db2agent (dbname) 0) TID:1 Appid:GA6DA619.C438.002D98045717
0
2355
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 server+port. I then configured SQL Server Agent, under Properties->Alert System, to use Database Mail, specifying a particular Mail Profile to use. I then go into the properties of one of my Agent Jobs, and under
0
10908
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10587
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10649
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10295
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7829
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5867
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4487
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4064
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3136
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.