none
Connect Mobile Service to an existing database

    Question

  • Hello, 

    I have a SQL Azure that I'm using in a WP7 Application and I want to use the same data to use in my Win8 Application that I'm building.

    The Mobile Services are just great, I don't need to deploy service... just works. The problem it's I cannot create the Mobile Service on a table create by the "dbo" and all my tables was created by the "dbo" user.

    How can I skip this? Do I need to recreate all my database or there is any way to use the tables created this way?


    thanks in advance
    Paulo Aboim Pinto

    Thursday, September 06, 2012 5:56 PM

Answers

  • Hi Paulo,

    There are a few things you will need to do to get this working:

    1. You need to move your tables to the schema for your mobile service. You can do this using the ALTER SCHEMA command. If your service name is 'myservice1' and you have a Customer table then the command would be:

    ALTER SCHEMA myservice1 TRANSFER dbo.Customer

    2. Mobile Services won't detect your tables automatically, so you will need to create your tables from within the portal. It will see that the table already exists in the database and use that table (it won't overwrite your data).

    3. The primary key column for each of your tables needs to be 

    [id] [int] IDENTITY(1,1)NOTNULL
    (bigint works too) 

    Note that case sensitivity matters. If your PK column is 'ID' then you will run into some issues - for example the browse tab will not work correctly.

    Let me know how it goes!

    Paul

    Thursday, September 06, 2012 10:23 PM

All replies

  • Hi Paulo,

    There are a few things you will need to do to get this working:

    1. You need to move your tables to the schema for your mobile service. You can do this using the ALTER SCHEMA command. If your service name is 'myservice1' and you have a Customer table then the command would be:

    ALTER SCHEMA myservice1 TRANSFER dbo.Customer

    2. Mobile Services won't detect your tables automatically, so you will need to create your tables from within the portal. It will see that the table already exists in the database and use that table (it won't overwrite your data).

    3. The primary key column for each of your tables needs to be 

    [id] [int] IDENTITY(1,1)NOTNULL
    (bigint works too) 

    Note that case sensitivity matters. If your PK column is 'ID' then you will run into some issues - for example the browse tab will not work correctly.

    Let me know how it goes!

    Paul

    Thursday, September 06, 2012 10:23 PM
  • My primary keys on existing tables are all in the format tablename_id.  This was originally dictated because I have about 10 tables which have many-to-many relationships with each other.  I'm not sure how to differentiate between the tables in the many-to-many without unique names on the ids.  Is there anything I can do to get around this?
    Thursday, October 11, 2012 3:43 PM
  • Typically I differentiate between the ids for different tables by qualifying the column on table name (select person.id, address.id ...).

    Unfortunately we do not have any support for customizing the name of the primary key on tables that Mobile Services works with. The only thing I can suggest at the moment is for you to rename your primary keys columns. Sorry for the inconvenience!

    Thursday, October 11, 2012 11:52 PM
  • Hello,

    It would be a big help to add the information provided here to the instructions of the mobile services.

    Had to waste some time to figure these things out by myself. 

    I did not have an existing database but i wanted to alter the tables, but i did not find any documentation about the requirement of the 'id' column. Had to find this out via server responses etc.

    Monday, October 22, 2012 6:51 AM