Please help me transfer the data from sql temporary table to a file

See more:

SQL-Server-2005

SQL-Server

Hi,

I am pasting a stored procedure code here. this stored procedure is caleed by an application( in C#) to generates a report in .CSV. The stored procedure works fine for small data but if the data is huge ( 99% of the times ) , the sp is not able to send the entire data, hence I have to break the data in chunks and give it to the .CSV. i tried many thing , like dividing the data in chunks and then try sending it to the .CSV, but it did not help. ONLY THE FIRST CHUNK OF DATA WAS FOUND IN THE .CSV. Please help me so that i can send the data in chunks rather than bulk.


NOTE : Please concentrate on the comment in the below SP: This select query sends the entire data to the report, i guess we have to change the code here-- The code in the upper half generates the report and format it.

select record from records order by slno



PLEASE HELP ME AT THE EARLIEST

STORED PROCEDURE :

ALTER PROCEDURE [dbo].[USCfp_GenerateReport]( 
@ReportDt datetime,  
@RetCode int output 
 ) 
  
 
AS 
SET NOCOUNT ON  
  
DECLARE  @IsRecreate     BIT,    
    @RptLastRunDt    DATETIME,   
    @RptStartDt     DATETIME,   
    @RptEndDt     DATETIME,       
    @RowCount     int, 
    @startAR     money, 
    @endAR      money, 
    @startDR     money, 
    @endDR      money, 
    @now      datetime, 
    @LastTransDate    datetime, 
    @MtrNo      int, 
    @PieceCnt     bigint 
    
  
create table #detailedrecord 
( 
MpdMtrNo int, 
MpdEndDate varchar(10), 
MpdCategoryCD varchar(20), 
WW6_ITEM_CD varchar(10), 
MpdTolPieceCnt bigint, 
MpdTolValue money 
) 
  
  
  
create table #RateCategory 
(Bucket_Name varchar(20), 
 Rate_Category varchar(20)) 
  
  
Create table #Records 
( 
slno int identity(1,1), 
RecordType varchar(10), 
Record varchar(1000), 
DiffAR money , 
TolCount varchar(20) 
)  
  
create table #startSRDetails 
( 
MpsMtrNo int, 
MpsStartAscReg money,  
MpsStartDescReg money, 
WW6_ITEM_CD varchar(10) 
)  
  
create table #endSRDetails 
( 
 MpsMtrNo int, 
MpsEndAscReg money,  
MpsEndDescReg money, 
MaxMpsAddDate datetime 
) 

 
set @RetCode=0 
  
  BEGIN TRANSACTION 
 IF @ReportDt IS NOT NULL 
  SET @IsRecreate = 1               
 ELSE 
  SET @IsRecreate = 0      
  
 /* IF THE REPORT IS BEING RE-CREATED, THEN IT SHOULD BE ORIGINALLY CREATED ON @ReportDt(File Creation Date). IF IT IS NOT  
  * ABORT EXTRACTION 
  */ 
  
 IF @IsRecreate = 1   
  BEGIN 
   /*IF EXTRACTED PREVIOUSLY ON THE DATE @RPTDT, CONTINUE WITH RE-GENERATION ELSE ABORT RECREATION 
     SET THE @RETCODE TO 1. MESSAGE HANDLED APPROPRIATELY IN THE APPLICATION*/ 
   IF EXISTS (SELECT WW50_JOB_RUN_DT  
      FROM WW050T        
      WHERE WW50_USER_ID = 'PSRExtract'         
       AND DATEDIFF(D,@ReportDt,WW50_JOB_RUN_DT) = 0) 
    BEGIN 
     SELECT @ReportDt = WW50_JOB_RUN_DT  
      FROM WW050T        
      WHERE WW50_USER_ID = 'PSRExtract'         
       AND DATEDIFF(D,@ReportDt,WW50_JOB_RUN_DT) = 0           
  
     SELECT @RptLastRunDt = MAX(WW50_JOB_RUN_DT)  
     FROM WW050T  
     WHERE WW50_USER_ID = 'PSRExtract'  
     AND WW50_JOB_RUN_DT < @ReportDt    
  
    END 
   ELSE 
    BEGIN      
     SET @RetCode = 1 
     GOTO Proc_Err 
    END      
  END 
 ELSE 
  BEGIN 
     
   --set report date as today's date 
   SET @ReportDt = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) 
   SELECT @RptLastRunDt = MAX(WW50_JOB_RUN_DT)  
   FROM WW050T WHERE WW50_USER_ID = 'PSRExtract'  
   AND WW50_JOB_RUN_DT <= @ReportDt 
  END 
   
 /*IF THE @RPTLASTRUNDT AND @RPTDT ARE IDENTICAL, IT IMPLIES THAT THE REPORT HAS ALREADY  
 BEEN EXTRACTED FOR THE DAY*/ 
 IF @RptLastRunDt = @ReportDt 
  BEGIN 
   SET @RetCode = 2 
   GOTO Proc_Err 
  END 
   
 --Compute USPS report start date [first day of the month of last successful extraction prior to @RptDt] 
 SET @RptStartDt = CONVERT(DATETIME,CONVERT(VARCHAR,YEAR(@RptLastRunDt))+RIGHT(REPLICATE('0',2) +  
   CONVERT(VARCHAR,MONTH(@RptLastRunDt)),2)+'01',112) 
    
 --Compute USPS report end date [last day of the month prior to @RptDt] 
 SET @RptEndDt = CONVERT(DATETIME,CONVERT(VARCHAR,YEAR(@ReportDt))+RIGHT(REPLICATE('0',2) + 
    CONVERT(VARCHAR,MONTH(@ReportDt)), 2)+'01',112) 
  
 IF @@ERROR <> 0 GOTO Proc_Err 
  
--Detail Records 
  
  
 
insert into #detailedrecord 
SELECT MpdMtrNo,  
RIGHT(REPLICATE('0',2) + CONVERT(VARCHAR,MONTH(MpdEndDate)),2) + CONVERT(VARCHAR,YEAR(MpdEndDate)) 
 ,MpdCategoryCD,LEFT(WW6_ITEM_CD,2),Convert(bigint,MpdTolPieceCnt),MpdTolValue 
          FROM MtrPeriodDetails mpd 
   INNER JOIN WW006T ww6 
ON mpd.MpdMtrNo = ww6.WW6_MTR_NO 
INNER JOIN MtrModelConfig mmc  
ON ww6.WW6_ITEM_CD=mmc.MMC_ITEM_CD and mmc.MMC_DEVICE_TYPE in (20,21,28) 
and (MpdAddDate >= @RptStartDt) AND (MpdAddDate < @ReportDt)and   --To include all the records whose Add date falls between Report Start date and current date 
    NOT(MpdAddDate < @RptLastRunDt AND MpdEndDate < @RptStartDt)  
  AND (MpdEndDate < @RptEndDt) 
order by 
MpdMtrNo, 
LEFT(WW6_ITEM_CD,2), 
RIGHT(REPLICATE('0',2) + CONVERT(VARCHAR,MONTH(MpdEndDate)),2) + CONVERT(VARCHAR,YEAR(MpdEndDate)), 
MpdCategoryCD 
  
   
 
 
 
 
insert into #RateCategory 
select distinct Bucket_Name,Rate_Category from ratecategory, MtrPeriodDetails where MpdCategoryCD=Bucket_Name 
  
 
 
 
insert into #Records 
select 'DT','DT'+','+ CONVERT(VARCHAR,@ReportDt,112)+',' +MpdEndDate+ ','+'02'+','+t1.WW6_ITEM_CD +',' 
+Convert(varchar,RIGHT(REPLICATE('0',10) + CONVERT(VARCHAR,t1.MpdMtrNo),10))+','+Rate_Category  +',' 
+RIGHT(REPLICATE('0',7) + Convert(varchar,sum(t1.MpdTolPieceCnt)),7)+','+ 
RIGHT(REPLICATE('0',13) + CONVERT(VARCHAR,(CONVERT(BIGINT,(sum(t1.MpdTolValue* 1000))))),13),sum(t1.MpdTolValue),RIGHT(REPLICATE('0',7) + Convert(varchar,sum(t1.MpdTolPieceCnt)),7) 
from #detailedrecord t1 
inner join  
(select Bucket_Name,Rate_Category from #RateCategory )R 
on t1.MpdCategoryCD=R.Bucket_Name 
GROUP BY 
  t1.MpdMtrNo,     
  t1.WW6_ITEM_CD, 
  t1.MpdEndDate, 
  R.Rate_Category 
 ORDER BY  
  t1.MpdMtrNo,  
  t1.WW6_ITEM_CD ,  
  t1.MpdEndDate, 
  R.Rate_Category 
  
 
 
 
 
 
 
insert into #startSRDetails  
SELECT  t1.MpsMtrNo as MtrNo, t1.MpsStartAscReg as StartAR, t1.MpsStartDescReg as StartDR,TempMpsMin.WW6_ITEM_CD as ItemCd 
FROM   MtrPeriodSummary t1 
       INNER JOIN ( 
          SELECT MpsMtrNo, Min(MpsEndDate) As EndDate ,WW6.WW6_ITEM_CD 
          FROM MtrPeriodSummary mps 
   INNER JOIN WW006T ww6 
ON mps.MpsMtrNo = ww6.WW6_MTR_NO 
INNER JOIN MtrModelConfig mmc  
ON ww6.WW6_ITEM_CD=mmc.MMC_ITEM_CD and mmc.MMC_DEVICE_TYPE in (20,21,28) 
and (mps.MpsAddDate >= @RptStartDt) AND (mps.MpsAddDate < @ReportDt)and   --To include all the records whose Add date falls between Report Start date and current date 
   NOT(mps.MpsAddDate < @RptLastRunDt AND mps.MpsAddDate < @RptStartDt) 
  AND (mps.MpsEndDate < @RptEndDt)  
group by mps.mpsmtrno,WW6.WW6_ITEM_CD 
       ) TempMpsMin 
 ON t1.MpsMtrNo = TempMpsMin.MpsMtrNo and t1.MpsEndDate=TempMpsMin.EndDate 
  
insert into #endSRDetails 
SELECT  t1.MpsMtrNo as MtrNo, t1.MpsEndAscReg as EndAR, t1.MpsEndDescReg as EndDR, MaxAddDate as EndDate 
FROM   MtrPeriodSummary t1 
  INNER JOIN ( 
          SELECT MpsMtrNo, Max(MpsAddDate) as MaxAddDate,Max(MpsEndDate) As MaxEndDate 
          FROM MtrPeriodSummary mps 
INNER JOIN WW006T ww6 
ON mps.MpsMtrNo = ww6.WW6_MTR_NO 
INNER JOIN MtrModelConfig mmc  
ON ww6.WW6_ITEM_CD=mmc.MMC_ITEM_CD and mmc.MMC_DEVICE_TYPE in (20,21,28) 
and (mps.MpsAddDate >= @RptStartDt) AND (mps.MpsAddDate < @ReportDt)and   --To include all the records whose Add date falls between Report Start date and current date 
   NOT(mps.MpsAddDate < @RptLastRunDt AND mps.MpsAddDate < @RptStartDt) 
  AND (mps.MpsEndDate < @RptEndDt)  
group by mps.mpsmtrno 
       ) TempMpsMax 
on t1.MpsMtrNo = TempMpsMax.MpsMtrNo and t1.MpsEndDate=TempMpsMax.MaxEndDate 
  
  
  
  
insert into #Records 
select 'SR','SR'+','+'02'+','+LEFT(WW6_ITEM_CD,2)+','+RIGHT(REPLICATE('0',10) + CONVERT(VARCHAR,S.MpsMtrNo),10)+','+CONVERT(VARCHAR,getdate(),112)+',' + 
RIGHT(REPLICATE('0',18) + CONVERT(VARCHAR,(CONVERT(BIGINT,(S.MpsStartAscReg * 1000)))),18) +','+ 
RIGHT(REPLICATE('0',18) + CONVERT(VARCHAR,(CONVERT(BIGINT,(E.MpsEndAscReg * 1000)))),18)+','+ 
RIGHT(REPLICATE('0',18) + CONVERT(VARCHAR,(CONVERT(BIGINT,(S.MpsStartDescReg * 1000)))),18)+','+ 
RIGHT(REPLICATE('0',18) + CONVERT(VARCHAR,(CONVERT(BIGINT,(E.MpsEndDescReg * 1000)))),18) +','+ 
CONVERT(VARCHAR,E.MaxMpsAddDate,112), 
0,0 
from #startSRDetails S 
INNER JOIN #endSRDetails E 
on S.MpsMtrNo=E.MpsMtrNo 
order by S.MpsMtrNo  
  
  
 IF @@ERROR <> 0  
 Begin   
  GOTO Proc_Err 
    End 
 
--Trailer Record 
  
insert into #Records  
select 'TR','TR'+','+'02'+','+CONVERT(VARCHAR,@ReportDt,112) + ','+  
RIGHT(REPLICATE('0',11)+Convert(varchar(20),(select count(*) from #Records where RecordType like 'DT%')),11) + ','+ 
RIGHT(REPLICATE('0',3)+Convert(varchar(20),(select count(distinct(WW6_ITEM_CD)) from #detailedrecord)),3)+','+ 
RIGHT(REPLICATE('0',10)+Convert(varchar(20),Convert(bigint,(select sum(Convert(bigint,TolCount)) from #Records where RecordType='DT'))),10)+','+ 
RIGHT(REPLICATE('0',18)+Convert(varchar(20),Convert(bigint,(select (sum(DiffAR)*1000) from #Records where RecordType='DT'))),18),0,0 
  
  
  
 IF @@ERROR <> 0  
 Begin  
    
  GOTO Proc_Err 
 End 
  
 
 -- This select query sends the entire data to the report, i guess we have to change the code here--
 
select record from records order by slno
 
--If the report is being created, update WW050T with run date. Not required,if report is being regenerated. 
 IF @IsRecreate <> 1 
  INSERT INTO WW050T  
    (WW50_JOB_RUN_DT,  
     WW50_USER_ID,  
     WW50_SELECT_START_DT,  
     WW50_SELECT_END_DT)  
  VALUES  
    (@ReportDt, 
    'PSRExtract', 
    @RptStartDt, 
    @RptEndDt) 
 --Main Logic: End  
  
   
  COMMIT TRANSACTION 
  GOTO Proc_Exit 
 
Proc_Err: 
  ROLLBACK TRANSACTION 
  RETURN @RetCode 
 
 
Proc_Exit: 
 SET NOCOUNT OFF 
  RETURN @RetCode