Teemu Tapanila bio photo

Teemu Tapanila

Microsoft Azure MVP and Windows Phone developer

Email Twitter Facebook Google+ LinkedIn Github Stackoverflow

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 Create new Windows Azure Mobile Service using existing database
  2. Fill in your username and password Create new Windows Azure Mobile Service using existing database 2
  3. Open your new mobile service and go to API tab Windows Azure Mobile Service API tab
  4. Create new API and set GET Permission to everyone (for easy testing) Windows Azure Mobile Service new Custom API
  5. Edit the script (Code can be find below) and save it Windows Azure Mobile Service API javascript
  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.