Windows Azure Mobile Services Custom API for existing SQL Database

Sometimes you have existing SQL database where you would like to expose some custom queries results

So there’s existing SQL database that has two tables Person and Orders. Custom API needs to query them with a parameter that’s given and then return result as JSON.

1. Create new Mobile Service and choose use exising SQL database
2. Fill in your username and password
3. Open your new mobile service and go to API tab
4. Create new API and set GET Permission to everyone (for easy testing)
5. Edit the script (Code can be find below) and save it
6. Browse to your mobileservice url/api/sql?id=1 to see the results

Javascript Code:

P.S. Note that if you get error that looks like this on windows azure mobile services log. Error occurred executing query: Error: [Microsoft][SQL Server Native Client 10.0][SQL Server]The SELECT permission was denied on the object ‘Orders’, database ‘TapanilaDemo’, schema ‘dbo’. It means that you need to grant the Windows Azure Mobile Services read rights to the schema.

---