Re: Problem with varchar(max) (201 Views)
Reply
Occasional Advisor
Nick_Karpushkin
Posts: 10
Registered: ‎03-20-2013
Message 1 of 10 (293 Views)

Problem with varchar(max)

Hi everyone!

 

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"

 

Please use plain text.
Honored Contributor
DimitarPeychev
Posts: 292
Registered: ‎11-01-2011
Message 2 of 10 (279 Views)

Re: Problem with varchar(max)

Hi,

 

 

 Please check this: http://support.openview.hp.com/selfsolve/document/KM916545 Need to handle nvarchar(max), varchar(max), and varbinary(max) SQL Server datatypes

 

 

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 :)
Please use plain text.
Honored Contributor
DimitarPeychev
Posts: 292
Registered: ‎11-01-2011
Message 3 of 10 (278 Views)

Re: Problem with varchar(max)

Hi again,

 

 Please check also:

http://support.openview.hp.com/selfsolve/document/KM1326708
Importing nvarchar(max) and varbinary(max) from sqlserver does not map correctly

 

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 :)
Please use plain text.
Occasional Advisor
Nick_Karpushkin
Posts: 10
Registered: ‎03-20-2013
Message 4 of 10 (257 Views)

Re: Problem with varchar(max)


DimitarPeychev wrote:

Hi again,

 

 Please check also:

http://support.openview.hp.com/selfsolve/document/KM1326708
Importing nvarchar(max) and varbinary(max) from sqlserver does not map correctly

 


I can't open the link, it says: You are not authorized to view this document

Please use plain text.
Honored Contributor
DimitarPeychev
Posts: 292
Registered: ‎11-01-2011
Message 5 of 10 (242 Views)

Re: Problem with varchar(max)

Hi,

 

 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 :)
Please use plain text.
Occasional Advisor
Nick_Karpushkin
Posts: 10
Registered: ‎03-20-2013
Message 6 of 10 (229 Views)

Re: Problem with varchar(max)

If I'm not mistaking, this ER was about SM version 7.11, and we are dealing with version 9.30 now and still no change. Is there any chance this problem will be fixed?

Please use plain text.
Occasional Advisor
Nick_Karpushkin
Posts: 10
Registered: ‎03-20-2013
Message 7 of 10 (223 Views)

Re: Problem with varchar(max)

Maybe anyone can tell me how to change sqldbinfo record for sqlserver so that character datatype is mapped to varchar and to text at the same time?

Please use plain text.
Honored Contributor
John Stagaman
Posts: 3,314
Registered: ‎07-13-2007
Message 8 of 10 (213 Views)

Re: Problem with varchar(max)

[ Edited ]

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.

----------------------------------------------------
Kudos - what, where, how, and why
Want Good Answers? Ask Good Questions...
Please use plain text.
Honored Contributor
John Stagaman
Posts: 3,314
Registered: ‎07-13-2007
Message 9 of 10 (212 Views)

Re: Problem with varchar(max)

[ Edited ]

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. 

----------------------------------------------------
Kudos - what, where, how, and why
Want Good Answers? Ask Good Questions...
Please use plain text.
Occasional Advisor
Nick_Karpushkin
Posts: 10
Registered: ‎03-20-2013
Message 10 of 10 (201 Views)

Re: Problem with varchar(max)

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?

Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation