use of (optional) keyword RRS feed

  • Question

  • I'm using an application that is generating some SQL scripts for SQL Server 2005.  I'm trying tweak it so that I can run it on SQL Server 2000.


    The line that I'm having trouble with is:


    CREATE INDEX FKFFF41F9960601995 ON alf_access_control_entry (permission_id);(optional)


    The key word "(optional)" is causing trouble. 

    I understand this keyword, when used in SQL Server 2005, let's the script continue and complete when errors are detected.


    What is the alternative syntax to use in SQL Server 2000?




    Monday, November 26, 2007 10:35 PM


  • There is no equal to that in SQL2K.  You'd have to actually trap an error.  The best thing to do is to do a mass replace of (optional)... replace it with "--(Optional)".  You'll have to handle any errors that come up but what else can you do.


    The only other thing would be to script the check for the object first... so an IF NOT EXISTS clause, but that'll take a lot of time so you're just better off commenting out the code and handling the errors as they come up.  Of course, you can always drop all those indexes first to make sure there won't be any errors.


    Tuesday, November 27, 2007 3:50 AM