Solved

Avoid SQL Job failure when warnings are encountered

Posted on 2008-10-08
8
304 Views
Last Modified: 2013-11-30
Hello,

I have a question about setting "allow warning" (I don't know how to call it) within the SQL jobs, and wondered if anyone can help.  I am monitioring an SQL 2000 Server, and within it, it contains a few SQL Jobs.  The jobs have been running fine all these times, but then one of them started to failed the last couple days.

The step that failed is in the middle of the job.  I tried to run that particular step in Query Analyzer, and it completed with an warning.  but if the job is auto-run in SQL Enterprise Manager, then it would fail.  I think I have heard (or read) somewhere that we can skip the non-so-critical messages, so the job would continue on without failing, but I couldn't recall what that is or where I can find that option.   Does anyone know what is that call, or where should I look for that information?  

Thanks in advance!
0
Comment
Question by:wingkchan
  • 4
  • 3
8 Comments
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22676082
In SQL Server Enterprise Manager inside your server go to Management then SQL Server Agetn then alerts

There are alerts that can break your job step at some point, i dont know what your job is doing and what of those alerts can be changed to let your job pass.

u can replace that alert with your own are make severity less so it doesnt stop the job
I know i have simular problems with my replication and when i changed those alerts (alert wasnt important at all) it worked nicely.

If u need more info i can give u some more details when i can get to server when I did that.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22676088
You just change the job step so that if it fails it goes to the next step (rather than completing the job with failure)
0
 
LVL 2

Author Comment

by:wingkchan
ID: 22676188
Cvijo123:  Thanks for the information.  However, I am not too sure as how to use it.  Once I set a new alert with a severity identifier, in the "Response" page, it doesn't seems to offer any option to "Ignore" that warning and tell SQL to go on with the job.  Do you know how to set the "Allow warning and keep on going" rule?

Nmcdermaid:  thanks the infomation as well... but I am not the DB admin and I don't want to change the job integrity check... I will confirm with him about that possible option.  Thanks for this alternative idea!
0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22676229
Is your job part of replication mybe ?

Now when i check on my server i remember i went on Distribution Agent Profiles where i could change behaviour what error to skip (used Continue on data consistency errors. in skip error list i edit error i got with my job's)

To check error u got go to your job and use job history to see error's (check See step details) and your error and what cousing him to stop jour job steps should be there.
(U can see those errors on that machine server in Event Viewer under Administrative tools in your Control Panel. Use Application tree in viewr and u can use filter to show only MSSQLSERVER source and everything except information)

Past those errors if u can so we can work around so your job execute normaly.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 2

Author Comment

by:wingkchan
ID: 22676483
Thanks for the quick response Cvijo123.  

The SQL job has to do with organizing data in different tables, and then updating another table, so it is somewhat different than your replication job, and I can't relate my case with your Distribution Agent Profile step.

The error message under Job Detail shows:
 String or binary data would be truncated. [SQLSTATE 22001] (Error 8152)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.
It might have happenend when the updating occurred
0
 
LVL 5

Accepted Solution

by:
Cvijo123 earned 300 total points
ID: 22676627
There is severel reasons you get this error and IMO u should fix that in your SP

1. You are updateing field with more value than it can store (example your field is NAme varchar(10) and u updateing it with '123567890_moreThanItCanStore'

2. You are using some kind of temp tables in your SP that have different field data types than source table

3. Your row after updaing will exceed 8060 bytes (this is limit in SQL 2000 u cant have more than 8060 byte row size in total, only ntex, images types are limited to 2GB in size)
So keep in mind that all your data in one row cant exceed 8060 bytes.

4. In last service pack for SQL Server 2000 they  fixed some issues about those warnings (last pack is SP 4)

IMO, you should work with error more than using job to continue if there is error.

If u can past your query and your table structure u are updateing we might work something out.
0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22676669
btw i just recalled u can do  something like this in your SP

SET ANSI_WARNINGS OFF

and it will eliminate those errors but i again suggest u fix your SP to work properly.
0
 
LVL 2

Author Closing Comment

by:wingkchan
ID: 31504586
Hi Cvijo123, I confirmed with the system developers and they said that it should be the data field not accepting long values just like you said.  It could have been that there are other jobs that uses the same tables, and that caused the errors in this SQL job.The case has been passed to them, and they will fix up the problems.  Thank you very much for helpful assistance.

And yes, I agree with you that it should be fix to the root instead of just skipping over the problem... But sad to say that I am not strong with SQL...and this is another good lesson to learn something that I don't know.  Thank you.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now