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 184.108.40.206 and 220.127.116.11. 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 18.104.22.168/32 and 22.214.171.124/32
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 "mycomputer.test.xxx.com,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 "mycomputer.test.xxx.com\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 ColumnsIt 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.
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.
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.