error converting datatypes

Hello,

Firstly, i need to work out why I cannot change my datatypes(please see query)

Code Snippet

SELECT * FROM (

SELECT top 10

ref,

RecordDate,

TransactionID,

StatusChangedTimeStamp,

TransactionStatus,

PartyTransactionStatus,

BadDeliveryReason,

TradingDaysRef

FROM (

SELECT 1 seq,

'ref' ref,

'RecordDate' RecordDate,

'TransactionID' TransactionID,

'TransactionStatus' TransactionStatus,

'StatusChangedTimeStamp' StatusChangedTimeStamp,

'PartyTransactionStatus' PartyTransactionStatus,

'BadDeliveryReason' BadDeliveryReason,

'TradingDaysRef' TradingDaysRef

UNION ALL

SELECT 2 seq,

cast(ref as bigint),

RecordDate,

TransactionID,

StatusChangedTimeStamp,

TransactionStatus,

PartyTransactionStatus,

BadDeliveryReason,

TradingDaysRef

FROM dbo.ParticipantTradeStatusChange

) x

order by seq, RecordDate

) y

The error returned is:

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

The reason for me doing this, is exporting both column names & data to a xls file.

Secondly, once i get the query to complete...I kindly ask, how would i make this query a little swifter in which, i mean, select the top 100 from the table then SELECT the columns...when i do

SELECT top 10 * FROM (

SELECT *

It falls over and dies

Help much appreciated

thanks,

i

The data -- literals -- that you have above your unions are not implicitly compatible with what is below the union. Look at this example:

Code Snippet

select 'Header'
union all
select 2

/*
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Header' to a column of data type int.
*/

I am basically getting the same error. You may need to explicitly declare the datatype of the lower part of the union to have them go as varchar -- maybe like:

Code Snippet

select 'Header' as Data
union all
select cast (2 as varchar)

/*
Data
Header
2
*/

|||

i've done a dirty workaround..

simply used cast(columnname as varchar(4000))

did the trick and i have my bcp file with headings

all todo now is make it run faster...