SSMS Job Error.

[Catadmin]

I have a job that executes a stored procedure I wrote. The stored procedure itself is fairly simply. Based on a record count, it does a Select...Into and then a Delete. Afterwards, it then uses msdb.dbo.sp_send_dbmail to send a message depending on if the record count was Zero (in which case nothing else was done) or > Zero.

This job worked fine in SQL 2000 using a sp_stmp_sendmail procedure we had created in the database. When I moved it to SQL 2k5, I had to change it because that proc was no longer working. However, even after I changed it to dbmail, I keep getting the same error message.

Date 9/6/2006 6:23:20 AM
Log Job History (BadRefunds)

Step ID 1
Server MYServer
Job Name BadRefunds
Step Name Null_NoRefunds_Check
Duration 00:00:00
Sql Severity 16
Sql Message ID 15157
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Unable to perform a SETUSER to the requested username 'dbo' because the username is invalid for database 'apacs'. The step failed.

I've checked the job from top to bottom. I'm not doing a SETUSER anywhere it in and the "Run AS" isn't set because the job step is a Transact-SQL step.

Does anyone have any notion as to what might be causing the problem?

Thanks,




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

Can you run the proc manually?

Who is the owner of the database? How about the job?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

When I run the proc manually, I get errored out too.

Mail queued.
Msg 8152, Level 16, State 14, Procedure spNULL_NoRefunds, Line 120
String or binary data would be truncated.
The statement has been terminated.

(19 row(s) affected)

(3 row(s) affected)
Mail queued.

The owner of the job is a domain user account with Sysadmin privledges (not me). The owner of the database, probably because I was the one who did the copy over from the old server, is me (also sysadmin). I didn't even think of broken ownership chain issues. Let me try changing the job owner.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Darnit. I fixed the truncate issue and can run the proc manually. However, I can't change the job owner because I get my login to the server via the DBA group, not as an individual login.

I hate to change the db owner in the middle of the workday... Thoughts on other options?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"