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...
