SQL MAINTENANCE PLANS DO NOT ALWAYS WORK OUT OF THE BOX...for various reasons....
SQL auto-generates SQL Maintenance plans automatically using AUTO-GENERATED code. Sometimes this code does not work and you have to copy and paste from Microsoft sql 2005 or microsoft sql 2008 auto-generated code into your own scheduled maintenance tasks.
Where do you get this code from? look at the jobs history and a button shows up under the detail description to show t-sql.
THIS CODE is very wordy....and has lines such as
USE [DATABASNAME]
GO
several times repeated... In my case these lines were repeated in my code problem 1300 times. So I wanted a easy way to keep the code in the same order and delete all the extra lines and commands which really are not needed.
HOW TO DELETE CARRIAGE RETURNS in SQL 2005 and MICROSOFT SQL 2008 AUTO-GENERATED CODE easily.....
1> USE MAINTENANCE PLAN TO CREATE CODE After I ran the maintenance plan ........it errored out on this....so I copied the the sql code from the history log on the maintenance plan. (SEE ABOVE)
2> COPIED CODE FROM History after it failed and re-ran as stand alone query ran the code in sql to determine where the errors were and corrected it...all errors occured because I did not have page level locing "clicked" on when I orginally created the index.
3>REMOVED ALL THE EXCESS CODE use [DATABASE] command GO commands and blank lines. HOW? --EXCEL or Microsoft ACCESS would probably work
a>Created a fake table in Microsoft ACCESS with two fields. AutoNumber and Notes (memo type)
b>Copied data into it used copy paste (NOTES FIELD NEEDS TO BE IN POSITION 1) during copy and paste(About 2500 lines of code copied)
c>Sorted the Notes field...Deleted all lines starting with [DATABASE], GO, and blank linesd>RESORTED THE TABLE using the AUTONUMBER FIELD...to place code in same order SQL MAINTENANCD JOB CREATED4>RE-RAN CODE BEFORE I COPIED INTO THE SCHEDULED JOB FOR RE-INDEXING THE SQL DATABASE ON A NIGHTLY AUTO SCHEDULED JOB.
ADDITIONAL SEARCH ENGINE REFERENCE TERMS:
Check Database integrity on Local server connection
Shrink Database on Local server connection
Reorganize index on Local server connection
Rebuild index on Local server connection
Update Statistics on Local server connection
Maintenance Cleanup on Local server connection
transact-sql command: t-sql
how to remove carriage returns from SQL AUTO GENERATED CODE
sql delete carriage returns find and replace
how to remove ASCII Character 13 from SQL 2005, SQL 2008 AUTO-generated code
delete similar multiple lines of code for SQL CODE
DATE CREATED: 2/1/2009