I've created an test app to learn to use SQL ...

(Terry Jacob) #1

I’ve created an test app to learn to use SQL Server before implementing what I’ve learned to an app that’s currently being used on the field.

I’ve read the support page “Using Data from SQL Server” and (unless I’m not understanding it) it says it is best to set the Key Column to nvarchar(8).

I have a Delivery_ID column set to nvarchar(8) which is the key column on SQL Server and also made this the Key with AppSheet. I’ve added UNIQUEID() in the InitialValue, but it will not sync.

I’ve been able to successfully use RANDBETWEEN(10000, 99999).

However,

the support page reads: “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.”

I will be using this on a high volume app (10 drivers capturing between up to 800 rows a day) so I want to make sure I’m setting it up correctly.

Is it safe using RANDBETWEEN? Or is there something I need to look at to get nvarchar(8) to work?

(Terry Jacob) #2

Hi Tony,

This is the error message:

Unable to add/edit/delete row in table ‘dbo.delivery_drops’. → One or more SQL errors has been encountered: One or more SQL Server errors has been encountered: The insert/update operation attempted to write data that was too large for a column in your SQL Server table Please give the app creator the following SQL error message to troubleshoot the problem: String or binary data would be truncated. The statement has been terminated. Attempted command: if exists (select * from sys.columns where object_id = OBJECT_ID(’[dbo].[delivery_drops]’, ‘U’) and is_identity = 1) SET IDENTITY_INSERT [dbo].[delivery_drops] ON; INSERT INTO [dbo].delivery_drops VALUES(@BARCODE0,@BOXTYPE0,@STATUS0,@EDITION0,@PROMOTION0,@LOCATION0,@ADDRESS0,@CITY0,@ZIPCODE0,@CROSS_STREET0,@PAPER_RETURNS0,@BOX_ISSUE0,@SELECT_ISSUE0,@PHOTO0,@DATETIME0,@TIME_ADJUSTED0,@GEOLOCATION0,@DRIVER0,@DELIVERY_ID0,@ADD_NOTES0,@DRIVER_NOTES0

(Tony Fader) #3

@Terry_Jacob What’s the error you’re seeing for the UNIQUEID() issue?

If you’re generating 800 rows per day, and there are 89,999 possible keys then I believe there’s a 97% chance of that you’ll generate duplicate keys in a day. That’s no good. (Source: https://en.wikipedia.org/wiki/Birthday_problem)

The UNIQUEID() approach is better because there are way more than 89,999 values.

You could also use a timestamp column using NOW().

(Terry Jacob) #4

@Harry I’m sorry, do you mean a temporary account with Microsoft SQL Server Management Studio? If yes, I’ll have to figure out how to do that. I’ve added your IP address to the server on Azure.

(Dinh Nguyen Nguyen) #5

@Terry_Jacob Hi Terry, an account with Microsoft SQL Server Management Studio should work. The reason I’m asking for access to the database is so that I can inspect the table schema to determine the cause of the problem.

(Terry Jacob) #6

@Harry Hi Dinh. I understand. I’m new to this SQL thing and trying to learn how to create a new user so you can log in. Once I figure it out how would you like me to send you the login info?

(Terry Jacob) #7

@Harry Ok, I think I’ve figured out how to add you as a user to the database on Management Studio. Let me know how you’d like me to share this login info with you.

(Dinh Nguyen Nguyen) #8

@Terry_Jacob You can email it to me at nguyen@appsheet.com. Please cc support@appsheet.com in your email.

(Terry Jacob) #9

@Harry I need to get the SQL version of this app into the driver’s phones. I’m going to use the RANDBETWEEN until we figure out the issue with the UNIQUEID(). When you get the chance, please let me know how to get you the login info for the SQL Server Management. I really don’t want to post it here.

(Terry Jacob) #10

@Harry Oh, you got to me while I was typing. Please disregard previous message.

(Terry Jacob) #11

I have a Date/Time column that I tried to use for the key. It wouldn’t sync either.

:frowning:

What problems would I face using RANDBETWEEN? It appears I could get up to 9999999, I believe.

(Tony Fader) #12

I’m adding @Harry to the thread to take a look at the issue.

(Dinh Nguyen Nguyen) #13

@Terry_Jacob Hi Terry, as the error message suggests, the data you’re trying to write to a text column in your database is too large. This column does not have to be the key column. Could you check the size limit of the other columns in your database? Are you writing a long text value to any of the other columns?

(Dinh Nguyen Nguyen) #14

@Terry_Jacob For text type columns (varchar, nvarchar), the size limit is in terms of number of characters. For example, for a nvarchar(8) column, you can only write a text value with at most 8 characters to the column. The UNIQUEID() expression generates a random that is 8 characters long, so most likely this is not the cause of the problem.

(Terry Jacob) #15

@Harry Hi Dinh. It’s simple data. The longest would be a cross-street column and I’ve set that at (100) to be safe. Thing thing is, it syncs fine when I use RANDBETWEEN. Same data.

(Dinh Nguyen Nguyen) #16

@Terry_Jacob Hi Terry, I notice there’s an image column in the table. What is the type and size of this image column in the database? The image file won’t be saved to the database, but the image path will be, so it is possible that the image path exceeds the number of characters allowed in the column.

If this does not fix the problem, I will need the credentials to access your database in order to investigate further. You can create and share a temporary account with me just for debugging purposes, and you can delete this account later. Please also add my IP address to the database’s firewall: 157.130.186.54

(Terry Jacob) #17

I just tested the app using the longest cross street, which is 76 characters using the RANDBETWEEN and it synced. Then I switched the key to UNIQUEID() and tried to capture the same data and it would not sync.

(Dinh Nguyen Nguyen) #18

@Terry_Jacob Hi Terry, could you share the credentials to access the database with me? You can create and share a temporary account with me just for debugging purposes, and you can delete this account later. Please also add my IP address to the database’s firewall: 157.130.186.54