VERIFIED SOLUTION i

Problems in Portrait Foundation when creating a Generic Data Access transaction against a database table whose fields have spaces in their names

Product Feature: Generic access
 
In Portrait Foundation, we can use the Generic Data Access (GDA) framework to run ad hoc queries or stored procedures against databases or other providers.
The result set can contain several output fields based upon the query being used.

If the output is from a table whose field names contain spaces, then the Portrait Foundation Configuration Suite will be unable to create the necessary Data Object items - because they cannot contain spaces.
For example: We want to write a GDA transaction that returns the data from a SQL Server database table called, say, [Dummy]. If the definition of this table contains fields called [Dummy Id] and [Dummy Value] - which are legitimate field names in SQL Server, then the Configuration Suite would attempt to create Data Object items called "Dummy Id" and "Dummy Value". This is not allowed due to the prohibition of spaces in a Portrait Foundation system name.
An error would occur, and the creation of the GDA transaction would fail.

The workaround for this issue is to create a SQL Server database view of the table in question and to redefine its field names to remove the offending spaces. e.g. call the view, say, [v_Dummy] and rename the field names to be [DummyId] and [DummyValue]. This will ensure that the Configuration Suite can correctly create the Data Object items' system names without spaces and therefore prevents the error from being raised.

An alternative workaround would be to define the name of the columns in the database table(s) to not have any spaces in them, and then in any subsequent Select statement, to give each field an alias. For example:
SELECT
  FieldNameNoSpaces AS [Field Name With Spaces]
FROM MyTable

 
UPDATED:  April 19, 2017