I did my best to look around the web but this problem eludes me. I have a stored procedure in SSIS that works fine. It does a bunch of stuff eventually returning some numbers and text. The procedure itself uses #temp tables since the data does not need to exist beyond the proc run and returns ~931K rows.
The next step was to bring the output of the proc into excel. Using MS query, I call the proc including the necessary parameters. it runs but the only data I get back is the columns with numbers. I am missing the text values. I thought it might be a text translation issue from SSIS to Excel so I changed the output from nvarchar to varchar and the problem remains. I wrote the proc so I can make any changes necessary. Also, I thought that it might be a temp table issue so I tried building a table, inserting the data there using the proc then pull that table into Excel and while I got a few more text columns, a number were still blank.
Are there any suggestions?
Short version of the problem: SQL works in management studio but text is not returned to excel. The number or rows from the proc match the number of rows in Excel when it is finished importing/updating. The numbers come back as expected.
Versions:
Excel: 2007 - SQL Server: 2005 - Management studio: 2008R2 - ODBC connection using MS query -
USE [cmdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [estimate].[sp_calendar](
@calendar_start char(8),
@years as int
)
as
set nocount on;
declare @calendar_end char(8)
declare @actual_start_date datetime
declare @actual_end_date datetime
declare @loop_counter datetime
set @actual_start_date = CONVERT (datetime, @calendar_start, 112)
set @loop_counter = @actual_start_date
set @actual_end_date = dateadd(year,+@years,@actual_start_date)
set @calendar_end = cast(year(@actual_end_date) as char(4))+RIGHT('00'+ CONVERT(VARCHAR,month(@actual_end_date)),2)+RIGHT('00'+ CONVERT(VARCHAR,day(@actual_end_date)),2)
begin
create table #calendar (
[yearmonth] nvarchar(8)
)
end
begin
create table #results (
[actual ExpectedActionDt] datetime
,[calc ExpectedActionDt] ntext
,ExpectedActionDt datetime
,[calc IntegratedReleasePlanDt] ntext
,IntegratedReleasePlanDt datetime
,[key] ntext
,projectid ntext
,projectnm ntext
,ParentChaseProjectNo ntext
,VersionTag ntext
,itemid ntext
,Qty float
,ItemNotes ntext
,CashflowType ntext
,frequency ntext
,UnitPrice float
,[cost] float
)
end
begin
create table #baseline (
[actual ExpectedActionDt] datetime
,[calc ExpectedActionDt] nvarchar(8)
,ExpectedActionDt datetime
,[calc IntegratedReleasePlanDt] nvarchar(8)
,IntegratedReleasePlanDt datetime
,[key] ntext
,projectid ntext
,projectnm ntext
,ParentChaseProjectNo ntext
,VersionTag ntext
,itemid ntext
,Qty float
,ItemNotes ntext
,CashflowType ntext
,frequency ntext
,UnitPrice float
,[cost] float)
end
insert into #calendar (
[yearmonth])
select
distinct calendarid [yearmonth]
from
[cmdb_core].[dbo].[Calendar]
where
calendarid between @calendar_start and @calendar_end
insert into #baseline (
[actual ExpectedActionDt]
,[calc ExpectedActionDt]
,ExpectedActionDt
,[calc IntegratedReleasePlanDt]
,IntegratedReleasePlanDt
,[key]
,projectid
,projectnm
,ParentChaseProjectNo
,VersionTag
,itemid
,Qty
,ItemNotes
,CashflowType
,frequency
,UnitPrice
,[cost])
select
case
when (ExpectedActionDt is not null)
then ExpectedActionDt
when (IntegratedReleasePlanDt is not null)
then IntegratedReleasePlanDt
else
DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
end [actual ExpectedActionDt]
,case
when (ExpectedActionDt is not null)
then cast(year(ExpectedActionDt) as char(4))+RIGHT('00000'+ CONVERT(VARCHAR,month(ExpectedActionDt)),2)+RIGHT('00'+ CONVERT(VARCHAR,day(ExpectedActionDt)),2)
when (IntegratedReleasePlanDt is not null)
then cast(year(IntegratedReleasePlanDt) as char(4))+RIGHT('00000'+ CONVERT(VARCHAR,month(IntegratedReleasePlanDt)),2)+RIGHT('00'+ CONVERT(VARCHAR,day(IntegratedReleasePlanDt)),2)
else
cast(year(getdate()) as char(4))+'0101'
end [calc ExpectedActionDt]
,ExpectedActionDt
,cast(year(IntegratedReleasePlanDt) as char(4))+RIGHT('00'+ CONVERT(VARCHAR,month(IntegratedReleasePlanDt)),2)+RIGHT('00'+ CONVERT(VARCHAR,day(IntegratedReleasePlanDt)),2) [calc IntegratedReleasePlanDt]
,IntegratedReleasePlanDt
,cast(ModelEstimateId as nvarchar(max))+cast(BucketId as nvarchar(max))+cast(ItemNo as nvarchar(max)) [key]
,projectid
,projectnm
,ParentChaseProjectNo
,VersionTag
,itemid
,Qty
,ItemNotes
,CashflowType
,frequency
,UnitPrice
,case
when frequency = 'OneTime'
then Qty
else
cast(round((UnitPrice*Qty)/12,0) as int)
end [cost]
from
estimate.ComputedEstimates
where
[status] <> 'Hold'
and CostCategory <> 'Assembly'
and includeinforecast = 'Y'
and case
when (ExpectedActionDt is not null)
then cast(year(ExpectedActionDt) as char(4))+RIGHT('00000'+ CONVERT(VARCHAR,month(ExpectedActionDt)),2)+RIGHT('00'+ CONVERT(VARCHAR,day(ExpectedActionDt)),2)
when (IntegratedReleasePlanDt is not null)
then cast(year(IntegratedReleasePlanDt) as char(4))+RIGHT('00000'+ CONVERT(VARCHAR,month(IntegratedReleasePlanDt)),2)+RIGHT('00'+ CONVERT(VARCHAR,day(IntegratedReleasePlanDt)),2)
else
cast(year(getdate()) as char(4))+'0101'
end >= @calendar_start
WHILE (@loop_counter <= @actual_end_date)
BEGIN
insert into #results (
[actual ExpectedActionDt]
,[calc ExpectedActionDt]
,ExpectedActionDt
,[calc IntegratedReleasePlanDt]
,IntegratedReleasePlanDt
,[key]
,projectid
,projectnm
,ParentChaseProjectNo
,VersionTag
,itemid
,Qty
,ItemNotes
,CashflowType
,frequency
,UnitPrice
,[cost])
select * from #baseline where [actual ExpectedActionDt] >= @loop_counter
set @loop_counter = dateadd(day,+1,@loop_counter)
END
select
c.[yearmonth]
,a.[calc ExpectedActionDt]
,a.[key]
,a.projectid
,a.projectnm
,a.ParentChaseProjectNo
,a.VersionTag
,a.itemid
,a.ItemNotes
,a.CashflowType
,a.frequency
,a.Qty
,a.UnitPrice
,a.[cost]
from
#calendar as c
left outer join
#results a
on c.[yearmonth] = a.[calc ExpectedActionDt]
order by 1,2,3
drop table #baseline
drop table #results
drop table #calendar
