KNOWN ISSUE: Error ''String or binary data would be truncated'' when clicking the Search button

Products

• Asset Management Solution

Issue/Introduction

When clicking the Search button in the Discovered Software Applications dialog, the following error is given:

Server Error in '/Altiris/ContractManagement' Application.
--------------------------------------------------------------------------------

String or binary data would be truncated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace:

[SqlException: String or binary data would be truncated.]
   System.Data.SqlClient.SqlDataReader.Read() +176
   System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping mapping) +175
   System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +260
   System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDataReader dataReader) +90
   System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
   System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand command, CommandBehavior behavior) +28
   System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +88
   Altiris.ContractManagement.Web.Wizards.dlgSoftwareProductsPicker.GetDiscoveredApplications()
   Altiris.ContractManagement.Web.Wizards.dlgSoftwareProductsPicker.DisplayGridResults()
   Altiris.ContractManagement.Web.Wizards.dlgSoftwareProductsPicker.btnSearch_Click(Object sender, EventArgs e)
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1292

Cause

This issue will occur if any record in the Inv_Mac_SW_Audit_Software table has data in column ProductName, ProductVersion, File Name, or Manufacturer, with a length exceeding the lengths defined in the table variables being created in the table-valued function - fnCon_SoftwareLicenseProductsMac.

In Asset Management Solution 6.2.1152 the fnCon_SoftwareLicenseProductsMac defines two table variables, @Products and @RawProducts as follows:

@Products TABLE
(
 [Product Name] nvarchar(255),
 [Product Version] nvarchar(128),
 [Product ID] nvarchar(50),
 [File Name] nvarchar(255),
 Manufacturer nvarchar(128),
 FromTable uniqueidentifier
) as

 @RawProducts TABLE
 (
  [Product Name] nvarchar(255),
  [Product Version] nvarchar(128),
  [Product ID] nvarchar(50),
  [File Name] nvarchar(255),
  Manufacturer nvarchar(128),
  FromTable uniqueidentifier
 ) 

However, the corresponding columns in Inv_Mac_SW_Audit_Software are defined with the following lengths:

ProductName (510)
ProductVersion nvarchar (256)
File Name (510)
Manufacturer nvarchar(510)

Resolution

These instructions are for SQL Server 2005:

1. Right-click the dbo.fnCon_SoftwareLicenseProductsMac table-valued function in the Altiris database and click Modify.
2. A SQL Script window loads containing ... ALTER function [dbo].[fnCon_SoftwareLicenseProductsMac]() ...

3. Find the code blocks where the following table variables are declared and set the varchar lengths as shown:
   
   @Products TABLE
   (
    [Product Name] nvarchar(510),
    [Product Version] nvarchar(256),
    [Product ID] nvarchar(50),
    [File Name] nvarchar(510),
    Manufacturer nvarchar(510),
    FromTable uniqueidentifier
   )

   @RawProducts TABLE
    (
     [Product Name] nvarchar(510),
     [Product Version] nvarchar(256),
     [Product ID] nvarchar(50),
     [File Name] nvarchar(510),
     Manufacturer nvarchar(510),
     FromTable uniqueidentifier
    ) 

4. Execute the new Alter Function script.

Applies To

Asset Management Solution 6.2.1152

Asset Management Solution 6.5.1126
Inventory Solution for Macintosh 6.2