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?
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
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