July 17, 2008 at 2:18 pm
Afternoon,
I am trying to have a very simple piece of SQL executed automatically each night around midnight. The SQL works if I run it in a query sheet and also runs if I place it in a stored procedure and kick that off. If I try to place the T-SQL in a step within a job it fails when trying to run that step of the job.
I'm a bit confused as to why?
Also, isn't there a way to have a job run a stored procedure? I don't see this as an option.
My SQL is...
insert into dbo.attendance ("uid")
select ("uid")
from dbo.users
where users.userType = 'Employee'
Again, this is giving the result of exactly what I want but not when puting the TSQL in the job directly.
The error I receive is that the column UID does not exist and yes, I have it set to the appropriate DB.
July 17, 2008 at 2:29 pm
Does the message say UID doesn't exist or you don't have permission? Anything run in a job is executed as the login that runs SQL Agent. If that's a low privilege account that doesn't have access to the database where UID is located, you'll get that error.
You can execute a stored procedure in a job step by set Type to "Transact-SQL script (T-SQL) and putting "exec your stored procedure in the Command box.
Greg
July 17, 2008 at 2:52 pm
The other thing to check, with the script in the job, is whether or not the job is running it in the right database. I can't count the number of times I've had a job fail because I forgot to set that option and it tried to run in "master."
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply