Scheduled job fails
-
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
-
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.
-
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).
-
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.
-
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...
-
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.
-
Can yu give more details of your fix? I got the exact error after a server restore.
Thanks,
Dave Guo
-
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
-
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.
-
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply