I've got a problem with fields in MS SQL Server that have varchar(max) datatype. At first, such fields were mapped to TEXT SQL datatype in dbdict, but after first time I SELECT data from that table dbdict auto-updates and sets SQL datatype for the field to varchar(0), thus I'm not able to retrieve any data from that field. I'm using SM 9.30 with MS SQL Server 2012.
Any ideas how to force SM to map varchar(max) SQL datatype in DB to TEXT SQL datatype in dbdict record?
In addition, this is what I found in SM Help:
Service Manager data types also allow you to take advantage of the following advanced features.
You can move your system data from one RDBMS type to another
You can map system data to multiple RDBMS types at the same time
You can use the SQL to SQL Conversion utility to enable one or both of these advanced features. The SQL to SQL Conversion utility allows you to move or map data on a table by table basis. It also allows you to customize how you want data in array fields to be mapped in the new RDBMS as well as offering several export options such as creating DDL or creating duplicate tables.
But I can't find anything about "SQL to SQL Conversion utility" or about "map system data to multiple RDBMS types at the same time"
it is an Enhancement Request and maybe that's why.
it say basically that:
Attempting to import a dbdict from an existing table with varchar(max) and varbinary(max) may produce dbdict entries of varchar(0) and varbinary(0). You can change them manually after they are imported to the correct mappings, but then you get a duplicate key error and the file gets locked, preventing further manipulation.
At this time, Service Manager does not support the varchar(max) or varbinary(max) data types offered by Micrisift SQL Server.And so reccomendation is to use the data types image and text when Service Manager runs against a SQL server instance. As I mentioned, it is an Enhancement Request and will correct this in the furure :)
HP Support If you find that this or any post resolved your issue, please be sure to mark it as an accepted solution. Please also give kudo if you find it interesting :)
The most important thing about that linked support article is that it's status is set to "deferred". That means no work is being done on it, nor is there any plan at this time to do any work on it, but that it may be revisited in the future.
The issue dates back to SC 6.2 but the article is updated with 9.30 as well. I peraonally wouldn't consider it to be a defect: support for nvarchar(max), varchar(max), and varbinary(max) is not required to enable product functionality.
I don't know of a way to map a field twice to the database.
If you need an array field to be indexable/searchable, you can map it to an array table (several out of box examples are in the operator table). You wouldn't want that to be default behavior for arrays, however, as there is some overhead when the tables are joined for record display/update.
The thing is that this SM Server + MS SQL Server used to work somehow and varchar(max) in DB was addressed as TEXT in dbdict, but because of automatic dbdict update the first time I try to work with table containing such a mapping varchar(0) replaces TEXT for these fields and such string appears in sm.log:
2492( 3536) 03/07/2014 14:58:06 RTE I Change of SQL data type for field 'classification' from 'TEXT' to 'VARCHAR(0)' detected has been saved to DBDICT for file 'ESSXMLClassByEmployee'
Is there a way to block this auto update of dbdict?