Excel SSIS查询在Excel中返回空列，但在Management Studio中不返回

有什么建议吗?问题的短版:SQL在Management Studio中有效，但文本不会返回给excel.完成导入/更新后，proc中的行数与Excel中的行数匹配.这些数字按预期返回.版本: Excel:2007年-SQL Server:2005年-管理工作室:2008R2-使用MS查询的ODBC连接-USE [cmdb]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [estimate].[sp_calendar]( @calendar_start char(8), @years as int )asset nocount on;declare @calendar_end char(8)declare @actual_start_date datetimedeclare @actual_end_date datetimedeclare @loop_counter datetimeset @actual_start_date = CONVERT (datetime, @calendar_start, 112)set @loop_counter = @actual_start_dateset @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)begincreate table #calendar ( [yearmonth] nvarchar(8))endbegin 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 )endbegin 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)endinsert 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_startWHILE (@loop_counter <= @actual_end_date)BEGINinsert 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_counterset @loop_counter = dateadd(day,+1,@loop_counter)ENDselect 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,3drop table #baselinedrop table #resultsdrop table #calendar解决方案此问题的解决方案归结为数据类型.如果您像我一样知道目标Excel，则必须使用Excel可以转换的数据类型.我一直在使用nvarchar(max)，它没有被带到Excel中，当我将字段更改为text和char时，我感觉很好.一旦知道要查找的内容，便从Microsoft找到了这个答案: Microsoft Excel数据类型.还有关于限制的页面:数据类型限制.另一个问题是我使用的是存储过程而不是纯SQL，尽管直接从表中进行选择也存在问题.我尝试加载表，而不是依赖具有类似故障的存储过程.没有任何错误返回，这只是没有数据.通过我的测试，以下是文本/字符类型的转换及其成功:文本-作品 ntext-作品 char-作品 nchar-作品 varchar-失败 nvarchar-失败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]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [estimate].[sp_calendar]( @calendar_start char(8), @years as int )asset nocount on;declare @calendar_end char(8)declare @actual_start_date datetimedeclare @actual_end_date datetimedeclare @loop_counter datetimeset @actual_start_date = CONVERT (datetime, @calendar_start, 112)set @loop_counter = @actual_start_dateset @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)begincreate table #calendar ( [yearmonth] nvarchar(8))endbegin 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 )endbegin 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)endinsert 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_startWHILE (@loop_counter <= @actual_end_date)BEGINinsert 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_counterset @loop_counter = dateadd(day,+1,@loop_counter)ENDselect 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,3drop table #baselinedrop table #resultsdrop table #calendar 解决方案 The solution to this issue came down to data types. If you know that your destination Excel, as I did, then you have to use a data type that Excel can convert. I had been using nvarchar(max) which wasn't being brought over to Excel, When I changed the fields to text and char, I was good. I found this answer from Microsoft once I knew what to look for: Microsoft Excel Data Types. There was also a page on limitations: Data Type Limitations. The other piece was that I was using a stored procedure rather than pure SQL although there was also a problem with selecting directly from the table. I tried to load a table rather than relying on the stored procedure with similiar failures. No errors were returned in any of this, it was just no data. Through my testing, here are the text/character type conversions and their success:text - worksntext - workschar - worksnchar - worksvarchar - failednvarchar - failed 这篇关于Excel SSIS查询在Excel中返回空列，但在Management Studio中不返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！