Post reply

Create a Federated Table in SQL Server 2005

  • February 27, 2009 at 9:10 am

    #97348

    Is there a way to set up a Federated table in SQL Server 2005 (like can be done in MySQL)?

    The problem I am trying to address is the fact that a table needs a foreign key constraint, but the table containing the information resides in another database.

    As I understand it, SQL Server does not allow foreign key constraints to cross database boundaries so I need to know if this is possible.

    PJ

  • February 27, 2009 at 10:16 am

    #951279

    a Federated Table is kind of neat in MYSQL: basically Federated tables are tables with storage in a remote server.

    The closest equivilent in SQL would be a view which points to a linked server's table...and being able to update the view, which would update the linked server.

    to answer your question, there's no true equivilent to Federated tables, and you are right, you cannot do an FK accross database boundaries.

    you can create a view to a table that is either a linked server or cross database chained, and update that no problem...there's sometimes a performance hit as it updates the remote server.

    I only tested with SQL server linked servers and an Oracle 10G server,but i doubt there is much of a difference between other linked servers...excel/access/mysql/text

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 27, 2009 at 10:18 am

    #951281

    Is it possible to combine the databases into a single database with different schemas?

    scehma1.table1 references schema2.table. This is a solution I used when migrating Sybase databases to SQL Server and Sybase too allows cross database foreign key contstraints.

    DAB

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

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