Scheduled job fails - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Scheduled job fails

Author

Topic

ramsfield
Starting Member

9 Posts

Posted - 2006-01-19 : 14:08:14

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-19 : 15:28:23

Log into the database server using the account that the MSSQLSERVER service uses. Run the INSERT/SELECT statement in Query Analyzer using Windows Authentication. Does it work there using this acocunt?

Tara Kizer
aka tduggan

ramsfield
Starting Member

9 Posts

Posted - 2006-01-23 : 15:05:34

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources