February 3, 2010 at 9:25 pm
hi all,
1) one of our jobs running in the production server(sql 2000) is failed i checked in the job hystory it shows this error
error
The job failed. The Job was invoked by Schedule 9 (ttc). The last step to run was step 1 (ttc).
...tion Report of abc 3.0 [SQLSTATE 01000] (Message 0) No Issues In abc! Application [SQLSTATE 01000] (Message 0) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) No Issues In Allocation [SQLSTATE 01000] (Message 0) Employee detail present in def Snapshot Employee Master is not present in abc Employee Master.The Total Number Of Records are 116 . [SQLSTATE 01000] (Message 0) @CostingPeriodToDate [SQLSTATE 01000] (Message 0) Jan 31 2010 12:00AM [SQLSTATE 01000] (Message 0) @CostingPeriodFromDate [SQLSTATE 01000] (Message 0) Jan 1 2010 12:00AM [SQLSTATE 01000] (Message 0) @EmployeeLevel3Code [SQLSTATE 01000] (Message 0) DM12 [SQLSTATE 01000] (Message 0) @previousTempToDate [SQLSTATE 01000] (Message 0) @CostingPeriodToDate [SQLSTATE 01000] (Message 0) Jan 31 2010 12:00AM [SQLSTATE 01000] (Message 0) TEMP TO DA... The step failed.
2) and one more job faild in the maintenance plan (Transaction log backup job for db maintenance plan Db maintenance plan1
It shows the error messahe in the job history
error
The job failed. The Job was invoked by Schedule 53 (Schedule 1). The last step to run was step 1 (Step 1).
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
3) another job Db Backup job for Db maintenance plan
error in the job history
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
please reply
Thanks in advance
February 4, 2010 at 7:12 am
What statement is in executing in the first step that failed?
Could you tr executing in a Test envionment?
Does the statement fail when you run in interactive mode? If so what error do you get?
Have you defined an error log file?
As for the other errors please refer to this Link:
http://qa.sqlservercentral.com/Forums/Topic128881-24-1.aspx
I hope this helps...
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 4, 2010 at 7:26 am
I noticed thyat in addion to no clustered Indexes there are no Unique Indexes on the table?
Do you delete Statement remove a range of records or a single record?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 4, 2010 at 6:06 pm
There is a known bug that is related to this. You may want to check if it applies to you:
Maintenance job fails on named instances of SQL Server 2000 with Error 22029
Otherwise this gives general troubleshooting guidance:
How to troubleshoot problems that occur when you use a Database Maintenance plan in SQL Server
Joie Andrew
"Since 1982"
February 4, 2010 at 10:04 pm
Hi all thanks for u r valuble replies
it is working fine, if i excute this procedure in query analyser. through scheduler it is failing.
it is tested in in test server
i reschedule the job still it is not working
suggessions Pls
Thanks in advance
February 5, 2010 at 5:04 am
Try specifying a job log file in that stored procedure execution step which is failing and post the error message from log file here.
MJ
February 5, 2010 at 5:58 am
What User ID do you use when you execute the Stored Procedure in Query Analyzer?
What happens if you use the User ID of the SQL Agent Service Account?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2010 at 9:07 pm
when i execute the query it is giving huge o/p with some error
last steps of error message :
@previousTempToDate
Jan 31 2009 12:00AM
@CostingPeriodToDate
Jan 31 2009 12:00AM
TEMP TO DATE
Jan 31 2009 12:00AM
EMPLOYEE CODE
9992
@AllocationFromDate
@CostingPeriodToDate
Jan 31 2009 12:00AM
@CostingPeriodFromDate
Jan 1 2009 12:00AM
@EmployeeLevel3Code
DM07
(1 row(s) affected)
@previousTempToDate
Jan 31 2009 12:00AM
@CostingPeriodToDate
Jan 31 2009 12:00AM
TEMP TO DATE
Jan 31 2009 12:00AM
EMPLOYEE CODE
9992
@AllocationFromDate
@previousTempToDate
Jan 31 2009 12:00AM
@CostingPeriodToDate
Jan 31 2009 12:00AM
TEMP TO DATE
Jan 31 2009 12:00AM
Msg 242, Level 16, State 3, Procedure SP_ProcessEmployeeCosting, Line 78
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Msg 242, Level 16, State 3, Procedure SP_ProcessEmployeeCosting, Line 201
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
@previousTempToDatem
@CostingPeriodToDate
TEMP TO DATE
(9051 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
please reply . any mistakes in the above query
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply