x

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
more ▼

asked Jun 26 '12 at 09:17 PM in Default

muk gravatar image

muk
400 24 32 34

@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.
Jun 26 '12 at 09:50 PM Oleg
Thank you for the tips guys. I tried putting the fully qualified name and it still does not work. Any other ideas?
Jun 27 '12 at 02:02 PM muk

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
Jun 27 '12 at 02:04 PM muk
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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 :)]
more ▼

answered Jun 27 '12 at 03:20 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

(comments are locked)
10|1200 characters needed characters left

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).

more ▼

answered Jun 26 '12 at 09:37 PM

DirkHondong gravatar image

DirkHondong
1.3k 14 17 19

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Jun 26 '12 at 09:37 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

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
Jun 27 '12 at 02:08 PM Kev Riley ♦♦

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$'
Jun 27 '12 at 02:29 PM Kev Riley ♦♦
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
Jun 27 '12 at 02:35 PM Kev Riley ♦♦

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?
Jun 27 '12 at 02:41 PM Kev Riley ♦♦

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?
Jun 27 '12 at 03:06 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x401
x85
x62
x4

asked: Jun 26 '12 at 09:17 PM

Seen: 2450 times

Last Updated: Jun 27 '12 at 04:06 PM