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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

appariCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.