Post reply

Partitioning tables on SQL 2005. Database is on Compatibility mode 70.

  • July 11, 2006 at 1:02 pm

    #366774

    I did a side by side migration of some SQL 7 databases to SQL server 2005, using the backup-restore method.

    I need to use the new partitioned tables feature in order to maintain and handle big tables.When I tried the CREATE PARTITION FUNCTION statement on one of my databases I got the following error:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'FUNCTION'.

    I changed the database to Compatibility Mode 80 and the PARTITION FUNCTION was created using exactly the same statement with no issues. I was also able to successfully complete the process of setting up partitioning on one of the tables.

    Is there any way to set partitioning  keeping the database on Compatibility Mode 70?

    I need to be able to use this new feature and also keep the database on Compatibility Mode 70 while we are upgrading the code gradually from SQL 7 to SQL 2005.

    Please, advise.

    Thanks,

    Jose

     

  • July 12, 2006 at 5:40 am

    #648669

    Nope - Table/Index Partitioning is a "90" compatibility mode feature only.  Like many other new features in 2005, Partitioning of tables will only function in "90" mode.  Think about it, if you tell 2005 to act like "70", then that also means that you can't use features that weren't built-in to that version because then 2005 would not be acting like 7.

    Remember to use Upgrade Advisor and a new tool called Upgrade Assistant (both available for FREE) to help find all of your issues.  Both tools may be found at http://www.microsoft.com/sql under the "Upgrade" section.

    Hope this helps!

  • July 12, 2006 at 5:54 am

    #648670

    But it did work with the database on SQL 2000 compatility, and partitioned  tables is a new feature on SQL 2005, it should not work on "80" compatibility mode either.

    Thanks

     

  • July 14, 2006 at 7:06 am

    #649231

    Wow!

    You are correct.  The Patitioning did work in 80 mode, but I was also able to switch the database to 70 mode after creating my partitions in 80 mode to 70 mode.  It seems that anything in 70 mode that references the word FUNCTION, it does not llike, but I was able to SWITCH partitions in 70 mode.  The bad part is that you would need to change your mode to 80 or 90 to create/remove partitions.  In my brief test, commands in 70 mode referenced the table just fine.

    Interesting.....

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply