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]