locked
migration from 2000 to 2008 schema RRS feed

  • Question

  • I migrate SQL Server 2000 database to SQL 2008.

    SQL 2000 databases do not have schema which is new for 2008.

    I will have to change all my stored procedures and functions to add my schema if I add schema to my new SQL 2008 databases.

    It looks like that it is almost impossible to add schema for a database migrate from 2000 to 2008.

    Please let me know if I am wrong or any method can add schema to 2008 new databases.

    Your information and help is great appreciated,

    Regards,

    Souris,

    Sunday, September 15, 2013 8:52 PM

Answers

  • I want to use new schema by department projects like HR, ACCT, CS as schema.


    So it's not a migration problem; you just added new schema's and want to use them? Yes, then you have to modify all stored procedures to the new schemas ... even if you still would use SQL Server 2000.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by sourises Wednesday, September 18, 2013 12:52 PM
    Wednesday, September 18, 2013 3:54 AM

All replies

  • SQL 2000 databases do not have schema which is new for 2008.

    Hello Souris,

    That's not correct, also in SQL Server 2000 schemas have been used, the only difference was, that schemas have been db user related; see User-Schema Separation

    And of course you can always add new schemas, see CREATE SCHEMA (Transact-SQL)

    So what's your exact problem here?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, September 16, 2013 4:53 AM
  • Thanks for the information and help,

    in this case, I can create a new schema for my group of tables, the only thing is about stored procedures and client connections.

    I need modify all stored procedures with schema and the client application need change link table name or SQLserver is smart enough to know the stored procedures without schema

    Thanks again for helping,

    Regards,

    Souris,

    Monday, September 16, 2013 9:36 PM
  • I need modify all stored procedures with schema and the client application need change link table name or SQLserver is smart enough to know the stored procedures without schema

    Hello Souris,

    I still don't understand, why you have to modify the existing SP; again, schemas are used in 2000 as well as in higher versions of SQL Server.

    Can you provide an example of an origin SP and what you have to change in the T-SQL (and why)?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, September 17, 2013 7:04 AM
  • because in SP, the SELECT stament like this

    SELECT * from dbo.MyTable in SQL 2000.

    dbo is user or schema, since the database in SQL 2000 always use dbo.MyTable.

    I need to change all dbo to new schema in SQL 2008.

    Regards,

    Souris,

     

    Tuesday, September 17, 2013 2:29 PM
  • dbo is user or schema, since the database in SQL 2000 always use dbo.MyTable.

    I need to change all dbo to new schema in SQL 2008.


    But also in SQL Server 2005 there is a (default) schema "dbo" in every database, and what for a new schema do you mean / are you using?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, September 17, 2013 2:39 PM
  • I want to use new schema by department projects like HR, ACCT, CS as schema.

    Because in SQL 2000 old database stored procedures use dbo as schema, the new SQL 2008 database can need to modify all stored procedures if I want to use those new schema.

    I think that I have to leave all old tables use dbo as schema and new tables I can create new schema.

    Thanks again for the information and help,

    Regards,

    Souris,

    Wednesday, September 18, 2013 1:13 AM
  • I want to use new schema by department projects like HR, ACCT, CS as schema.


    So it's not a migration problem; you just added new schema's and want to use them? Yes, then you have to modify all stored procedures to the new schemas ... even if you still would use SQL Server 2000.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by sourises Wednesday, September 18, 2013 12:52 PM
    Wednesday, September 18, 2013 3:54 AM
  • Thanks a million for the information and help,

    Regards,

    Souris,

    Wednesday, September 18, 2013 12:52 PM