question

muk avatar image
muk asked

job failing

Hi I have a job created to execute two stored procedures: exec sp\_Import\_PopulateImportTables
exec sp\_Import If I run these two commands regularly (without a job) they work fine. But when I try to put them in a job I get this error: Executed as user: NT AUTHORITY\\SYSTEM. Could not find stored procedure 'sp\_Import\_PopulateImportTables'. [SQLSTATE 42000] (Error 2812). The step failed. This stored procedure is DEFINITELY in the database. What am I doing wrong here????? Thanks in advance!!!!!! any insight is greatly appreciated
stored-proceduresjobimport-datajob failure
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@muk Try to avoid naming the procedures with sp\_ prefix if it is at all possible. Back in the good old days this would be totally bad because this prefix is reserved for the system procedures in the master database. I now that in nowadays the chicanery with sp\_ is possible, but still the practice should be avoided.
2 Likes 2 ·
muk avatar image muk commented ·
Thank you for the tips guys. I tried putting the fully qualified name and it still does not work. Any other ideas?
0 Likes 0 ·
muk avatar image muk commented ·
Actually sorry I now get this error: Executed as user: NT AUTHORITY\SYSTEM. Login failed for user 'EXCHANGE\SQL01$'. [SQLSTATE 28000] (Error 18456) Staging Import Succeeded [SQLSTATE 01000] (Error 0). The step failed. (The name of the database is SQL01) and I am executing as user SQLbackups
0 Likes 0 ·

3 Answers

·
Kev Riley avatar image
Kev Riley answered
But testing the connections through SSMS isn't the same as a job running under SQL Agent trying to connect... You may need to add the SQLBackups account as a named account on the linked server connection, depending on how you have them set up. You got some linked server pointers in this question http://ask.sqlservercentral.com/questions/90047/please-help-login-failed.html [Posted as an answer at the request of the OP - see the comment trail under my other answer to see how we got here :)]
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DirkHondong avatar image
DirkHondong answered
Hmm, either it has something to do with the executing account of sql server agent or you have the wrong db focus (or both). First the easy one: Try the FQDN of your db, e.g. Mydatabase.schemaName.MyProcedure or reconfigure your Jobstep and choose the right db in the drop down menue. Another hint: normally it is better to use usp_ instead of sp_ when naming user stored procedures (catchword: naming convention).
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
The database in context of a job is not necessarily the one where your sp is. Fully qualify the tsql as Exec database.schema.storedprocedure
17 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
So now you have a login failure error. Check in the SQL Server error log for that status that accompanies the 18456 error and look it up here http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx
1 Like 1 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
No not that state - look in the SQL log for a message like Error: 18456, Severity: 14, **State: XXX.** Login failed for user 'EXCHANGE\\SQL01$'
1 Like 1 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
FYI - the SQLSTATE is an ODBC error code http://msdn.microsoft.com/en-us/library/windows/desktop/ms714687(v=vs.85).aspx and isn't related to the underlying issue
1 Like 1 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
OK then we'll try this blind :).... so the server is complaining that the user EXCHANGE\\SQL01$ (the server account) does not have the necessary permissions to do something. It might be running those procs, or something you are trying to do within those procs. When you say they run fine regularly, I presume you mean in SSMS - what user are you connected as then? What account is SQL Agent running as?
1 Like 1 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
It's possible. Have you tried running SSMS as the SQLBackups user, and then running the procs? Actually looking at the error message it states 'Executed as user: NT AUTHORITY\\SYSTEM' - are you sure the job is being run as the Agent service? Is there any value in the drop down box 'Run as' on the job steps?
1 Like 1 ·
muk avatar image muk commented ·
There are no details about state28000
0 Likes 0 ·
muk avatar image muk commented ·
I tried to check the SQL error logs and there are no details of this error in them
0 Likes 0 ·
muk avatar image muk commented ·
When you say they run fine regularly, I presume you mean in SSMS - what user are you connected as then? - My exchange login, I am the system admin so I have full rights What account is SQL Agent running as? - An account called SQLbackups which is also sa and has full rights
0 Likes 0 ·
muk avatar image muk commented ·
the procedure sp_Import_PopulateImportTables executes 2 other stored procedures. would this matter?
0 Likes 0 ·
muk avatar image muk commented ·
You are correct, there is no value in the drop down. There is also no value to choose from. I believe one of the procedures is calling a linked server as well. Is there some configuration I can check for this? (This was setup by the previous DBA and now I am cleaning up the mess haha)
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Ahhh! Linked servers - see if that 18456 error message appears on that server
0 Likes 0 ·
muk avatar image muk commented ·
I tested the connections and they both succeeded
0 Likes 0 ·
muk avatar image muk commented ·
I am proud to announce I fixed the error! The problem was the remote login for the linked server was mapped incorrectly so it really had nothing to do with the job itself. Thank you so much Kev for all your assitance.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Glad we got there in the end!
0 Likes 0 ·
muk avatar image muk commented ·
post the last comment you made as an "answer" so I can mark it as correct. This is what lead me to the resolution, thanks again.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Done! Thanks!
0 Likes 0 ·
muk avatar image muk commented ·
no, thank you!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.