Job failed after upgrade to SQL 2005

• Nserve

  Grasshopper

  Points: 13

  More actions

  May 16, 2008 at 3:13 am

  #186649

  Hi,

  After upgraded to sql 2005, Job is failing with an error message - "Cannot drop the table '#temp_procob', because it does not exist or you do not have permission", though the code is checking for whether temp table is exists in database before executing drop statement. Please find the below copied code for your reference.

  If exists (select 1 from tempdb..sysobjects where name like '#temp_procob%')

  DROP TABLE #temp_procob

  This is happening in production server where temp database is created on three different drives. All these drives are having full rights to drop or create or delete the temp tables.

  When we ran the same job (Stored procedure) in query analyzer, it is running successfully.

  Can anyone help how to solve this issue.

• KenpoDBA

  SSCrazy Eights

  Points: 9811

  More actions

  May 16, 2008 at 1:50 pm

  #816717

  Perhaps the agent acct doesn't have the rights you think it does. Try running the job as a specific user.

  Watch my free SQL Server Tutorials at:
  http://MidnightDBA.com
  Blog Author of:
  DBA Rant – http://www.MidnightDBA.com/DBARant

  Minion Maintenance is FREE: