We're moving a SQL 2005/Windows 2003 process to SQL 2008 R2/Windows 2008 and are having a devil of a time making it work. It runs in 2005 just dandy. SQL Server 2008 R2 (64-bit) is running on a virtual machine, everything is new.
Standard SQL server install. No service packs.
SQL Agent runs a SSIS package with several steps, one of which is a Execute SQL task which submits a series of Stored Procs. The job pretty much bombs after the same Stored Proc everytime.
When the Stored Procs are run from SSMS, it runs no problem.
Note that other SQL Agent jobs that run SSIS that run stored procs are OK.
The Error Message:
Here is the error message from the the SQL Agent:
Refresh failed for Job 'UpdateDatabase'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.Ex
View Complete Post