Row sort order on SQL script to export query to CSV via BCP

I'm having trouble with the query I wrote below.This script runs on SQL Server 2005 Express edition (meaning no DTS / SSIS) This script is used to add a header row to the export with column names (something BCP can't do natively)

The problem with this script is that the header row created on lines 156 - 175 does not always return at the top despite the sort by the temp column.

How can I fix the sort order in this script? Would i be better off adding an identity column?

if object_id('##TempExportData') is not null
    drop table ##TempExportData
if object_id('##TempExportData2') is not null
    drop table ##TempExportData2

declare @columnNames varchar(max), @columnConvert varchar(max), @tempSQL varchar(max), @xpCMDText varchar(8000)

-- insert data into a global temp table

SELECT 					a._1402 AS contacts_birthdate,
						c._1240 AS contacts_email .....[code truncated for readability]...........

		into ##TempExportData
		FROM myTable AS a .....[code truncated for readability].............

-- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
SELECT   @columnNames = COALESCE( @columnNames  + ',', '') + column_name,
        @columnConvert = COALESCE( @columnConvert  + ',', '') + 'convert(nvarchar(4000),' 
        +	case when data_type = 'varchar' then  'QuoteName('+column_name+',''"'')' 
			else column_name 
			end

			+ case when data_type in ('datetime', 'smalldatetime') then ',121'
                             when data_type in ('numeric', 'decimal') then ',128'
                             when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'
                             when data_type in ('datetime', 'smalldatetime') then ',120'
                             else ''
                        end + ')as ' + column_name
FROM    tempdb.INFORMATION_SCHEMA.Columns
WHERE    table_name = '##TempExportData'

-- execute select query to insert data and column names into new temp table
SELECT    @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID] 
       from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'
print (@sql)
exec (@sql)

-- build full BCP query

--- Change the following 
---  -U[sql username here] -P[sql pw here]  or use -T to use a trusted account


select @xpCMDText = 'bcp " select * from ##TempExportData2 " queryout "' + @fullFileName + '" -c -Slocalhost\EMMSDE -t","  -T -CRAW'

-- execute BCP
Exec master..xp_cmdshell @xpCMDText

Open in new window

IntuvoAsked:
Who is Participating?
 
appariConnect With a Mentor Commented:
try changing like this

-- execute select query to insert data and column names into new temp table
SELECT    @sql = 'select * into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID]
       from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'
print (@sql)
exec (@sql)

-- build full BCP query

--- Change the following
---  -U[sql username here] -P[sql pw here]  or use -T to use a trusted account


select @xpCMDText = 'bcp " select  ' + @columnNames + ' from ##TempExportData2 order by [temp##SortID] " queryout "' + @fullFileName + '" -c -Slocalhost\EMMSDE -t","  -T -CRAW'

-- execute BCP
Exec master..xp_cmdshell @xpCMDText
0
 
Ramesh Babu VavillaCommented:
for More info on BCP command , have a look into my blog

http://sqlservr.blog.com/
0
 
IntuvoAuthor Commented:
The temp sort order did the trick! Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.