Stored procedures runs well. But job does n't. why?

[dearamjath]

HI all,

I have a stored procedure, It runs well as a stored procedure. But When I made it as a Job It fails. What may be the reason? I have come across a error diagnosis message 'NT Authority/ Anonymous logon'

Thanks,
Amjath

 

[Rishi146]

how are you running it in a job please give more details is it in a DTS package
or just a job if just a job


1.Make sure SQL server agent is running
2.MAke sure the Agent has right authentication if running accross domains
3.In the job if you click on it and goto the tab steps and open the properties of the step is the type set to TSQL and in the command are you saying EXEC stored procedure name


you could also try is to create a DTS package --choose EXECUTE SQL TASK-- place a EXEC stored procedure name

THEN what you do is Save the package and right click on that schedule it and that in TURN it creates a JOB
run that job manually or by the schedule time if that Runs and SUCCEEDS GREAT if it does not ITS TO DO with the SQL SERVER AGENT LOGINS (check step 2 as i have written above).

Rishi(mcsd,MCDBA,MCP)

 

[dearamjath]

Thanks Rishi,

I am just executing a Stored procedure say, sp_proc1.

But in the sp_proc1, I am using two different servers, say serv1, serv2.

Stored procedure resides in the serv1.

So, I have used a query like this,

SELECT * INTO mytablename FROM serv2.mydb.dbo.mytablename.

But when it sees the other server serv2, it fails in the job, giving the error, 'NT Authority/Anonymous logon'. I know it is a NT specific error. But My doubt is how to get rid of this problem. What should I do to overrid NT authority.

As for now, I have just copied the table from serv2(through DTS) and using it in the serv1 server for local.

Thanks for your time.

Amjath

 

[Rishi146]

I dont think you will able to do this without setting up a LINKED SERVER.This would be the easiest way to do this

Rishi