SQL, Stored Procedures, Job and &

[GreggW]

Why isn't it possible to run a stored procedure via a job in SQL Server Agent which references a table whose name includes ampersand (&) but it works if you perform exactly the same operation in query analyzer?

Hmmmmm.......

 

[osjohnm]

Does the SQL Server Agent Account have enough permissions to execute the stored procedure?

John

 

[GreggW]

Yes, the account we're using to execute the stored procedure is the system administrator.

 

[osjohnm]

'sa' might have excute permissions but sql server agent either runs as the administrator account on the actual server or as a domain account eg. SERVER01\Administrator or FINANCE\User1.

The job schedules are handled and executed by SQL Server Agent, now if that account can't execute the sp that could be the cause.

It would always work in analyser since you are logged on have the necessary rights. The same things affect DTS since the sql server agent often does not have rights to the servers it fetches or passes data to.

Therefore the only logical explanation is that SQL Server Agent can't exec the sp.

I suggest that you make sure that the local administrator has 'sa' rights and if you use a domain account for the sql server agent that that account exists in sql server as a login and also has 'sa' rights.

One more thing is sql server agent running?

John