Hi there,
Our business is having a problem when using a queryEntity in Appian for a certain field. It converts that field to an NVARCHAR(4000) (I'm assuming because it's a text field) even though our Database and Data Type have it listed as VARCHAR(50). In SQL Server, when comparing an NVARCHAR to a VARCHAR, it causes an implicit conversion of all data in the VARCHAR column which is a big performance hit for us.
Is there any way to make sure that the parameter uses VARCHAR(50) instead of NVARCHAR(4000)?Thank you
Discussion posts and replies are publicly visible
I think we need more context on how you're using this. One thing to keep in mind is that within Appian, text values don't have limits on character lengths - the limits are entirely to determine how to connect to the database correctly. Because of that, a!queryEntity() doesn't do a cast between types or define a certain length - regardless of what length the database has, the value in Appian is just a text.
Based on your question, it sounds like the problem may be occurring in the database and not in Appian. How are you doing the comparison between fields of NVARCHAR to VARCHAR? Is this for a database view (and if so, can you share the definition of the view)?
In our Appian, when I use Ad Hoc Test in Appian rules (in this case, for the queryEntity), our text input box has a limit of 4,000 characters. The problem is in the database but it's happening when Appian's query is translated into SQL code.
This query is specifically for a table. The SQL definition for the column is this:
[UniquePatientId] VARCHAR (50) DEFAULT (CONVERT(VARCHAR(50), newid())) NOT NULL
A text field in Appian is in no way related to any SQL code. The characterLimit parameter does only control the numbers of characters a user can type into the field.