database maintenance plan - reorganize data pages and indexes

database maintenance plan - reorganize data pages and indexes

• nobugz

  Sorry to take so long, I had to find out how to add the commands. Which I did, but I'm still getting the same error. I c&p'd them into the 'edit' of the job step and applied. Then I went back and checked that they were still there. I started the job (which I've set up on a copy of the prod db) and it died w/the same error.
  
  As you can tell, this is new stuff to me.
  
  What else can I try
  
  
  
  
  
  

• PregJ

  Try putting the following statements at the top of the step with the SQL Agent Job that the DB Maintenance Plan creates to schedule execution of the plan:

  set nocount on
  set arithabort on
  set ansi_nulls on
  set ansi_null_dflt_on on
  set ansi_padding on
  set ansi_warnings on
  set cursor_close_on_commit off
  set implicit_transactions off
  set quoted_identifier on
  set concat_null_yields_null on
  set numeric_roundabort off

  That should be directly followed by the statement that already existed within the step to execute the master.dbo.xp_sqlmaint procedure.

  HTH

  
  
  

• hiren

  Well, try changing the 'on' in the 'set quoted identifier on' statement to 'off' instead...all comes down to differences between these settings in the msdb, master, and your user database...
  
  

• Ade Miller MS

  I'm back! Thought I had this resolved, but am still having the same error. The bad thing is that it only occurs on some of the DBs in the same instance of SQL Server. I've checked the options on the DBs and they match between the ones that can be integrity checked and the ones that can not. The ones that can be i ckd will run w/o any of the overrides. For the ones that the job gets an error, I've tried quoted_identifier on and off, with the other options and w/o. All DBs have ANSI NULL default, Auto update statistics, torn page, auto create statistics and use quoted identifiers checked, others not. (sql 2000). Db compatibility level 80 is selected on the compatibility. Some Dbs are model simple, some are full. That doesn't seem to matter.

  I'd appreciate any more ideas.........

  
  
  

• Griffin62

  I couldn't chg the flag. But did get the suggestion you sent to work. I removed the line returns at the end of each line and replaced them with just a space, so that the entries where all strung together when I edited the step. So that worked.
  
  So thank you. Now I just have to learn how to keep them when the job is refreshed.
  
  as you've probably guessed, I'm learning as I go.
  
  
  
• database maintenance plan - reorganize data pages and indexes