I need to add headings to my SQL columns which is generated by a batch file. How

Question

I need to add headings to my SQL columns which is generated by a batch file. However by default I get "--------" in the second row below each column header, which I do NOT want.

Batch:

sqlcmd.exe /U admin /P xxxxx /d database /S sql1 /i APCheckInterfaceQuery.sql /W /o "F:ITApplicationsExportAPCheckInterface_output_file.txt" /s ","

What do I add to my batch file to get the column headings without the dashes in the second row?

I also tried to modify the SQL directly to manually add column headers (and use /h -1 in the batch to take the headers out), but I get conversion errors. Because some columns are numeric and the headers would be varchar:

Select 'Check#' as Check#
     , 'CheckDate' as CheckDate
, 'VendNbr' as VendNbr
, 'VendName' as VendName
, 'CheckAmt' as CheckAmt
     , 'Session#' as Session#

....etc.

I'll get the following error: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Please help me. Thank you.

Explanation / Answer

--- In Order to solve issue related to datatypes("converting data type varchar to numeric"),
--you can use below steps to convert all column datatypes to varchar.

Select * #TempConverstion From <YourTableName>

--Now we are creating below cursor to change datatypes of temp table

DECLARE AlterTableCursor CURSOR FAST_FORWARD
FOR
SELECT
AlterCmd = 'ALTER TABLE tempdb.dbo.<YourTableName> ALTER COLUMN ' + name + ' VARCHAR(255) NULL'
FROM
tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE
object_id = OBJECT_ID('tempdb.dbo.<YourTableName>')

DECLARE @AlterTableCmd NVARCHAR(200)

OPEN AlterTableCursor

FETCH NEXT FROM AlterTableCursor INTO @AlterTableCmd

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@AlterTableCmd)

FETCH NEXT FROM AlterTableCursor INTO @AlterTableCmd
END

CLOSE AlterTableCursor
DEALLOCATE AlterTableCursor

--Note Please <YourTableName> with your table name.
--Now all columns are varchar which will solve converting problem

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.