Using data from Microsoft SQL Server

AppSheet can build apps from Microsoft SQL Server databases that are hosted in Amazon AWS, Microsoft Azure, or another cloud-hosting provider.

To do so, add a SQL Server data source using the Account>Data Sources pane.

In order to access SQL Server data, the SQL Server instance must be hosted in the cloud (in Amazon AWS, Microsoft Azure, or another cloud-hosting provider). 

You must also make sure that your SQL Server accepts traffic from the IP addresses and  These are the IP addresses that AppSheet will be connecting from, so your firewall must allow connections from those addresses. In the case of a database running on AWS, please ensure that Network Security option 'Publicly Accessible' is set to Yes and add a Security Group rule for the server that allows the IP address and

To add a SQL Server data source, you will be asked to provide a server name, database name, username and password. The system stores these values securely, and concatenates them to construct a connection string to the database.

  • SQLServer has a default port of 1433. If instead a non-default port number is being used, that should be provided as part of the server name. So for example, if it is running on port 2000, use ",2000" as the server name.
  • If a non-default instance name is being used for the SQL Server, provide that as part of the server name. So for example, if the instance is called "myInstance", use "\myInstance" as the server name.

Once a SQL Server data source has been added to your account, you can add SQL Server tables or views to any app. When you choose "Add Table" in your app, you can select the specific data source, and a table or view from that data source.

Once added to the app, AppSheet treats all data sources similarly. In fact, it is common and natural to combine data from a SQL Server data source with data from other sources in the same app. 


Database Identity Columns

It is common for a database table to use an identity column as a key. The values of the identity column are auto-incrementing numbers that get automatically inserted by the database. When you use such a table with AppSheet, there can be a problem. By default, SQL does not let an application define the identity column value. However, with AppSheet, new rows are created in the app when it is potentially offline and may only be synced later. So the app needs to be able to assign key/identity values. This problem is not new and every app framework that enables offline access data entry has to deal with it.
The best solution is to avoid IDENTITY column keys altogether in your database schema. Instead, use a column that is an nvarchar(8) (or in general an nvarchar of length greater than or equal to 8). In AppSheet's column definition for this key, give it an InitialValue of UNIQUEID(). Now unique key values can be assigned by the app and inserted into the backend database.
If the database must use IDENTITY columns, it is preferable to create them with a large initial seed. For example, IDENTITY(100000, 1). In AppSheet's column definition for this key, give it an InitialValue of RANDBETWEEN(10000, 99999). Now any records created from your app will have five digit values that lie randomly in the range 10000 to 99999, while records created directly against the database will have higher values. Please note that when your app is being synced, AppSheet will turn on the IDENTITY_INSERT property for the table, insert a new row and then turn it off again. There are restrictions on how many of these operations can occur concurrently, so if this is a high-traffic app, errors could occur. In these situations, we again advise that an nvarchar(8) column should be used as the key instead.
If the database schema cannot be changed and if there is already an IDENTITY column being used with the default initial seed (of 1), we recommend that you follow the same approach as described earlier. However, you can first manaully increment the identity seed as follows:
SET IDENTITY_INSERT <tablename> ON; INSERT INTO <tablename>(col1Name, col2Name ...) VALUES(100000, ....); SET IDENTITY_INSERT <tablename> OFF; Delete the row just inserted

This should "re-seed" the identity column to the desired range. Your AppSheet app will insert values in the range specified by the RANDBETWEEN() function in the InitialValue of your AppSheet column definition. 


Datetime Columns

There are 2 main data types to store date time values in SQLServer: "datetime" and "datetime2". The type "datetime" only accepts date time values that lie between 01/01/1753 00:00:00 and 12/31/9999 23:59:59. On the other hand, the type "datetime2" allows a much larger range from 01/01/0001 00:00:00 to 12/31/9999 23:59:59. In general, it is recommended that you use "datetime2" instead of "datetime" in your database, especially if you expect your app to work with dates before 01/01/1753. 

Another common pitfall to look out for while using date time in SQLServer is how date time inputs are typed into the app. Some browsers, such as Firefox, do not provider a date time picker. Instead, the app user will have to manually type in date time values. This might lead to typos or formatting errors, which would cause SQLServer to reject the input date time values.

For instance, a common typo is to type "0217" instead of "2017". In this case, if "datetime" is used in the database, the year value will be rejected. If "datetime2" is used in the database, the value will be accepted even though it is incorrect.

Another common formatting error is to type in 2-digit year values, such as "04/17/17" instead of "04/17/2017". In this case, the server will interpret the year to be 0017 instead of 2017. If "datetime" is used in the database, the year value will be rejected. If "datetime2" is used in the database, the value will be accepted even though it is incorrect.


Image and File Columns

AppSheet treats image and file columns in a special way. When your AppSheet app captures and saves an image or file, AppSheet creates a new file on the provider's file system for each image or file, and writes the image or file data to that file. It then places the name of this file into the image or file column in your SQL table. Finally, it writes the image or file column value to your SQL database. 

Database providers differ from other cloud-storage providers in that they do not have a file system. In the case of a SQL Server provider, images and files are always stored in the main cloud file system associated with the app creator's primary AppSheet account, be it Google Drive, Dropbox, Office 365, or Box.

If your AppSheet app captures images or files, your database table must include a VARCHAR or NVARCHAR column to store the file name of the saved image or file. 

Have more questions? Submit a request


Article is closed for comments.