CODEWITHSUNDEEP
sqlsql-serversql-server-2012sql-agent-job
Keith
Keith

Reputation: 111

2012 SQL job problems

I'm have a horrible time with SQL 2012. I'm used to job scheduling and automation with SQL 2000 but I'm new to 2012.

I've written a very basic script (see below) and no matter what I do the script fails as step one of the job, but works fine as a stand alone query in the query window.

Any help would go a long way.

declare @AccountID as int
declare @ID as int
declare @SaleDate as datetime
declare @todaysdate as datetime
declare @sentdate as datetime
declare @writeToFile as int
begin
set @todaysdate = getdate()
set @writeToFile = 0
select @id = ID, @AccountID = AccountID, @SaleDate = SaleDate from TransHeader where Transheader.ID = 19680
--print @@ROWCOUNT

select AccountID, max(SurveySent) as "THEFINALDATE" from tbl_survey_email where AccountID = @AccountID AND datediff(dd, SurveySent, @todaysdate) <= 182 Group By AccountID
set @writeToFile = @@ROWCOUNT
--print @writeToFile

if (@writeToFile = 0)
    begin

        insert into tbl_survey_email ('AccountID','TransactionID', 'datetime') VALUES (@AccountID, @ID, @SaleDate)
    end

end

enter image description here

enter image description here

After hours of trying everything to make this work, it is still not working. I've even changed teh SQL Server Agent service to run as me (domain admin)

I've even change my script to only be an insert script but still getting pretty much the same error. Any ideas? enter image description here

enter image description here

enter image description here

Upvotes: 3

Views: 71

Answers (1)

user8128167
user8128167

Reputation: 7676

You have to give the NT SERVICE user permissions to access the tables in your database. Your Keith user has permissions, but the NT SERVICE does not. It should have permissions like this:

enter image description here

Of course, in my screen shot, the user is not exactly the same as what I think yours is, but you get the idea.

Upvotes: 4

Related Questions