Hi,

I had the same issue from the same type of client.

I sorted it out with a bit of dynamic SQL to create the Union query.

Note: because all the data must be converted to varchar due to the new first line containing column titles, formats need to be taken into account (for dates at least).

[Code="sql"]

Declare @TableName nvarchar(max)

Declare @ListColumns nvarchar(max)

Declare @ListColumnsConverted nvarchar(max)

Declare @QueryColumnNames nvarchar(max)

Declare @QueryTableValues nvarchar(max)

Declare @QueryHeaderAndRows nvarchar(max)

Set @TableName = 'YourTable'

--Get the column hearders from the system table

SELECT @ListColumns = coalesce(@ListColumns + ', ', '') + convert(varchar(500),COLUMN_NAME)

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @TableName

ORDER BY ORDINAL_POSITION

--Get the same list of column from the system table but adding a convert statement (All columns must be converted to varchar because of the first row containing only alphanumeric)

SELECT @ListColumnsConverted= STUFF((SELECT ',' + Case When DATA_TYPE = 'datetime' Then

'Convert(VARCHAR(11), ' + convert(varchar(max), COLUMN_NAME) + ',103)'

Else

'Convert(varchar(max),' + convert(varchar(max), COLUMN_NAME) + ')'

End

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @TableName

ORDER BY ORDINAL_POSITION

FOR XML PATH('')), 1, 1, '')

-- Create the Select statement containing only the headers names

SET @QueryColumnNames = 'Select ''' + Replace(@ListColumns, ', ', ''', ''') + ''''

-- Creating the statement with the values

SET @QueryTableValues = 'Select ' + @ListColumnsConverted + ' From ' + @TableName

-- Union all on both

SET @QueryHeaderAndRows = @QueryColumnNames + ' Union All ' + @QueryTableValues

EXEC (@QueryHeaderAndRows)

[/Code]