Workflow Soluiton

I have an Integration project that executes a query to return several columns from a SQL table.  Maintenance was performed on the SQL table that included the use of parenthesis in the content one of the columns.  Once that maintenance was applied, my Integration project started throwing errors with the message "Input string was not in a correct format".  To get around the error I modified my query to exclude any rows whose text includes a parenthesis.

My assumption is that there is code in the Integration Project compiler designed to prevent SQL injections (or some other vulnerability) and this code considers a parenthesis to be an invalid character so it throws the error.

Can anyone verify that and/or offer a method for allowing the query to return a parenthesis?

I'm able to return a value containing a parenthesis without a problem.  Are you saying that the input value of the component has a parenthesis, or the output contains values that contain parenthesis?  I'm also able to pass in values that contain parentheses without issue as input to an integration component.

if the problem is the input, try placing apostrophes in the SQL query manually and remove the setting for Quotes Type "Single Quoted" and clear the "DB Parameter" checkbox for that input parameter in your generator.

what does your SQL query look like?

-andrew

The data being returned by the query contains the parenthesis.  The query is:

Select      SLC.[Name] as SoftwareTitle,
               SLC.Code,
               SLC.dLicUseRights as LicensePurchased,
               SLC.dSoftInstallCount as LicenseUsed,
               SLC.dCompliancy as LicenseAvailable,
               IsNull(SLC.[Type], -1) as EstimatedCost
From amSoftLicCounter as SLC WITH (NOLOCK)
Where Context = 'amPortfolio'
And Code Is Not Null
And Name Not Like '%(%'
Order by SLC.[Name]

I added And Name Not Like '%(%'  to get around the problem with parenthesis.

What's the [Name] column data type?

If the datatype isn't already, try a varchar conversion and see if it helps.

Select      CONVERT(varchar(max), SLC.[Name]) as SoftwareTitle,
               SLC.Code,
               SLC.dLicUseRights as LicensePurchased,
               SLC.dSoftInstallCount as LicenseUsed,
               SLC.dCompliancy as LicenseAvailable,
               IsNull(SLC.[Type], -1) as EstimatedCost
From amSoftLicCounter as SLC WITH (NOLOCK)
Where Context = 'amPortfolio'
And Code Is Not Null
Order by SLC.[Name]

-andrew

In the database the Name column is defined as VARCHAR (255).  In the Integration project it is defined as a STRING.

Unfortunately I'm having trouble reproducing this issue.  If nobody else gets back to you on this and you need help, i'll be happy to take a look over webex and see if anything obvious stands out.  send me a PM if so.

hopefully someone else can weigh in with an idea.

-andrew