Scheduled job fails

Question

Scheduled job fails

ramsfield

SSC-Addicted

Points: 411
More actions
January 19, 2006 at 12:08 pm

#89236

I have the following code in a store procedure. I have a scheduled job setup to run every half hour. The job fails everytime with no indication as to why. If I exec the stored procedure from query analzyer it runs just fine. However, when I run it as a scheduled job it fails. I have also tried running just the code from the stored proc from the job. It also fails. Any ideas why the job is failing, but runs fine from the analyzer?
The owner of the job is SA. I double checked the right database was selected. And the sql agent is started by a member of the sysadmin group. I have also tried adding this step to a known working job on the same database. When I do this, this step of the job still fails.
CREATE PROCEDURE [sp_logged_in_users]
AS
Declare @record_time_2 [datetime]
Set @record_time_2 = Current_Timestamp
Begin
INSERT INTO
[Tidemark].[dbo].[logged_in_users]
( [Login_Name],[DBName], [LoginTime],[Lastbatch],[Record_time])
SELECT mp.loginame, ms.name,mp.login_time,mp.last_batch, @record_time_2
FROM master.dbo.sysprocesses mp INNER JOIN
master.dbo.sysdatabases ms ON mp.dbid = ms.dbid
WHERE (ms.name = N'Tidemark')and mp.loginame NOT IN ('sa','dbo','public')
End
GO

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

BeerBeerBeer Hall of Fame Points: 3961 More actions · Answer 1

I would assume that this failure comes with some error message - but it doesn't appear to be reported.

Have you looked in the SQL logs or the event viewer? If you are able to find the related error it will be far easier to resolve the problem.

Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

ramsfield SSC-Addicted Points: 411 More actions · Answer 2

The error doesn't tell much:

The job failed. The Job was invoked by User sa. The last step to run was step 1 (Logged_in_users).

ramses2nd Hall of Fame Points: 3720 More actions · Answer 3

An uimportant thing to consider here is that when a job is executed it is done so under the credentials of the user which is running the SQL Server Agent. Therefore, this user must have all the appropriate priviledges required by every step of the job. I would start investigating here.

David le Quesne SSCertifiable Points: 5229 More actions · Answer 4

Hi Ramsfield,

I suspect this might be a permissions issue as well.

The error message you quote looks like the one for the Job rather than for step 1. If you tick the Show Step Details box on the top right of the Job History dialog it should give an explanation of why step 1 failed, for example, here is one I made earlier

Executed as user: sa. OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005: ]. [SQLSTATE 01000] (Error 7300). The step failed.

Have you tried starting the Job manually by right clicking it in EM and selecting Start Job to see if it fails then?

You could also try modifying the stored procedure to omit part of the code, e.g.: does it work if you omit the insert? Does it work if you omit the select and insert literal values?

David

If it ain't broke, don't fix it...

bledu SSChampion Points: 12335 More actions · Answer 5

I tried to copied your stored proc and tested on my server. I did not get any error when I invoked the job by running it from enterprise manager.

If you haven't found a solution can you give me more info so that I can test on my machine.

Everything you can imagine is real.

ramsfield SSC-Addicted Points: 411 More actions · Answer 6

After overlooking the step history....

Executed as user: NT AUTHORITY\SYSTEM. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

I changed the field properties of one field of the destination table to match the system table and now it works fine.

Thanks for all the input.

BeerBeerBeer Hall of Fame Points: 3961 More actions · Answer 7

How important it was to find the actual error. That just makes it so much easier to resolve the problem - provided you don't get one of those really ambiguos errors of course.

Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

Dave Guo Valued Member Points: 56 More actions · Answer 8

Can yu give more details of your fix? I got the exact error after a server restore.

Thanks,

Dave Guo

SQLBill SSC Guru Points: 51440 More actions · Answer 9

Dave,

It usually means that a column is too small for the data it is receiving.

For example:

MyTable

MyCol CHAR(5)

INSERT INTO MyTable (MyCol)

VALUES ('123456')

That will fail since I am trying to insert 6 characters into a 5 character field.

Another issue is when you are importing/DTSing values. If the original COLUMN is larger than the NEW column, you will get the same error, even if the data length would fit.

MyTable2

MyCol VARCHAR(10)

1234

6789

MyTable3

MyCol VARCHAR(5)

Moving MyTable2.MyCol to MyTable3.MyCol will fail with that error. Even though all the values would fit into the new column, SQL Server doesn't know that...all it knows is you are trying to put a '10 length column' into a '5 length column'.

-SQLBill

harpreet-272382 Grasshopper Points: 21 More actions · Answer 10

I get error Executed as user: NT AUTHORITY\SYSTEM. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

the error is caused by a delete statement. I remove the delete statement it works just fine. How can a delete staement be trucated..puzzled.

visu_paradigm Newbie Points: 5 More actions · Answer 11

Hi David,

My dev box has job which has owner SA and run with Service account.

but while running I am getting the same error that you mentioned.

OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005: ]. [SQLSTATE 01000] (Error 7300). The step failed.

Step contains : Exec Linkedserver.procedureureName

I tested the same statement from the Query Analyzer and its working fine. I have given admin access to the service account on the Linked server. But still am facing the same problem.

Can you please help me how can i figure out or what might be the wrong ?

Thanks!

Visu