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 @IsRecreate = 1
BEGIN
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 @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 @RptLastRunDt = @ReportDt
BEGIN
SET @RetCode = 2
GOTO Proc_Err
END
SET @RptStartDt = CONVERT(DATETIME,CONVERT(VARCHAR,YEAR(@RptLastRunDt))+RIGHT(REPLICATE('0',2) +
CONVERT(VARCHAR,MONTH(@RptLastRunDt)),2)+'01',112)
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
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
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
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
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
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
select record from records order by slno
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)
COMMIT TRANSACTION
GOTO Proc_Exit
Proc_Err:
ROLLBACK TRANSACTION
RETURN @RetCode
Proc_Exit:
SET NOCOUNT OFF
RETURN @RetCode