Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2006
    Posts
    58

    Question Unanswered: How to share user-defined data type in different database?

    Hi,everyone.

    I have defined a data type "OC_BUN_NAME" in database "CVPRO". I want to create a table tempdb.dbo.CVPRO in SQL SERVER 2005 system Database tempdb. But SQL SERVER 2005 DBMS gives a Error Messages:"Can not find the data type OC_BUN_NAME".

    How can I do? How to use data types in the other database?

    Please give me some advice. Thank you in advance.

  2. #2
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    Hi
    You should define the same data type in tempdb.
    Also it would be good to define it in model database.
    The tempdb is destroyed and recreated (from model) after each SQL Server restart. So if you define your data type in model database you do not have to redefine it in tempdb each time you reboot the server or restart SQL Server service.

    Regards,
    Kris Zywczyk

  3. #3
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  4. #4
    Join Date
    Aug 2006
    Posts
    58
    Thank you,Kris Zywczyk.
    Thank you, rudra.

    However, after I define my data types in MODEL system databse, if I create a new database, the defined data types will automatically be added into the new database. I hope it can not happen. How can I do?

    Thank you!

  5. #5
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    Why do you want to create any tables in tempdb?
    Regards
    Kris Zywczyk

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Good lord. Don't even touch tempdb.
    And in my opinion, don't bother with user-defined datatypes. They have little purpose.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Aug 2006
    Posts
    58
    In SQL Server 2005, we can create a table in tempdb database so that different users can get access to data in the table. At the same time, when the SQL Server Services stop, tables created in tempdb database will be automatically destroyed. So in our project we want ot create a table in the tempdb database. However when we create the table, we can not use user-defined data type(the data types are defined in database "CVPRO"). Please give me some help.

    Thanks!

  8. #8
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    Hi
    If you want to use user-defined data type you need to define it in current database (database where you create the table).

    Can't you use standard tables in your 'CVPRO' database and delete it when it is not used?
    You can always create the SQL Server Agent job dropping not needed tables and schedule it to run daily at i.e. 11 PM...

    What exactly you want to do?
    Regards
    Kris Zywczyk

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •