No data for smallint
-
Hi,
I am using DTS to output an SQL query to a text file.
I am explicitly selecting all the columns in the table, using a SELECT statement, as I need to covert some of the columns.
The last column is a smallint datatype.
The DTS package works ok, but when I look at the output file, there is no data in the last column, and I know there is data in the table.
I tried moving the column to a different position in my SELECT statement, but it still did not output any data.
Any ideas why it is not giving me any data for that column?
tks.
-
Can you show us the code for the conversion?
-
yep here it is:
select [MemberNo],
''+substring(convert(varchar(30), EffDate, 120),9,2)
+'.'+substring(convert(varchar(30), EffDate, 120),6,2)
+'.'+substring(convert(varchar(30), EffDate, 120),1,4)
+'' AS [EffDate],[Action],[PersonID],[HomeAddrID],[PostalAddrID],
case when EndDate is not null then
''+substring(convert(varchar(30), EndDate, 120),9,2)
+'.'+substring(convert(varchar(30), EndDate, 120),6,2)
+'.'+substring(convert(varchar(30), EndDate, 120),1,4)
+'' else '01.01.0001' end AS [EndDate],[Premium],[IsPayInclRebate],
case when AuditTime is not null then
''+substring(CONVERT(Varchar(30), AuditTime, 120),1,10)
+'-'+substring(convert(varchar(30), AuditTime, 120),12,2)
+'.'+substring(convert(varchar(30), AuditTime, 120),15,2)
+'.'+substring(convert(varchar(30), AuditTime, 120),18,3)
+'.000000'+ '' else '0001-01-01-01.01.01.000001' end AS [AuditTime],
Level from [db_name].[dbo].[RebateActivity]
order by memberno,effdate
-
Hi,
I have found my problem....I didn't have my transformations defined correctly...doh!.
-
Hence why I asked to see it
.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply