NOTICE: Branded Content
NOTICE: Certain versions of content (“Material”) accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.
IT Asset Manager / Connect-IT User Discussions
cancel

AM Queries to MS SQL Server are using Unicode (nvarchar) instead of ANSI (varchar)

Highlighted
GoldenRatioRJP Honored Contributor.
Honored Contributor.

AM Queries to MS SQL Server are using Unicode (nvarchar) instead of ANSI (varchar)

Hello All,

This was logged against AM9.6, but I am running into it in AM9.3 as well.  Anyone have a workaround?  Generlly queries are fast enough, but I have one now which is taking 2+ hours to run with the Unicode syntax AM generates and ~4 secvonds if I remove the "N" from in front of the String.  Which makes sense if the way AM generates the SQL is preventing Indexes from being used.
 
AM Queries to MS SQL Server are using Unicode (nvarchar) instead of ANSI (varchar)
"When activating a filter on a text field in the AM client or web client, the query being sent to MS SQL Server uses nvarchar instead of varchar on a non-Unicode database. This prevents SQL Server from utilizing indexes and slows performance of the application."

 

KM03013230 / QCCR1E144928

https://softwaresupport.softwaregrp.com/group/softwaresupport/search-result/-/facetsearch/document/KM03013230

Cheers... Russ 

Russell Parker, CITAM, CHAMP, CSAM
President - Golden Ratio, Inc.
Russ.Parker@GoldenRatioInc.com
2 REPLIES
GoldenRatioRJP Honored Contributor.
Honored Contributor.

Re: AM Queries to MS SQL Server are using Unicode (nvarchar) instead of ANSI (varchar)

... and of course the $64K question is if this has been fixed in AM9.7 or not ...

Russell Parker, CITAM, CHAMP, CSAM
President - Golden Ratio, Inc.
Russ.Parker@GoldenRatioInc.com
GoldenRatioRJP Honored Contributor.
Honored Contributor.

Re: AM Queries to MS SQL Server are using Unicode (nvarchar) instead of ANSI (varchar)

I heard back from Support and it seems this has not yet been fixed.  However a workaround was added in AM9.5

=============================

There are two workarounds in the meantime.

 

  1. You can activate Unicode on your database through the Database Administrator.
  2. You can enable the "Use Unicode string constants in Microsoft SQL Server queries" in the Database options, and then set the ConstAsTextForGet=1 and ConstAsTextForSelect=1 in the amdb.ini in order to remove the parameterization of the queries.
Russell Parker, CITAM, CHAMP, CSAM
President - Golden Ratio, Inc.
Russ.Parker@GoldenRatioInc.com
Top Contributors Last 30 Days