I have a report which was working fine in crystal 8.5. After I moved to Crystal 9.0, I'm getting the error message "Query Engin Error: '24000:[Microsoft][ODBC SQL Server Driver]Invalid Cursor State'".
The report's data source is the following stored procedure:
CREATE PROCEDURE dbo.sp_ComparativeCampaignAnalysis
@SysQuery_Key integer = null,
@SegKey integer = null,
@SegServer nvarchar(50) = '[MDTNTS-MDA].MoneyMaker_System',
@StartDate datetime = null,
@EndDate datetime = null,
@WildCard varchar(20) = '%',
@GroupByString nvarchar(3800) = 'source'
AS
set nocount on
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Gather Source Codes from SOURCE Table and Group Them into #TempSourceGroup table
set @WildCard = (case when isnull(@WildCard,'')='' then '%' else @WildCard end)
declare @FinalSQL nvarchar(3800)
set @FinalSQL = 'SELECT '+@GroupByString+' as GroupByCode, isnull(source_key,0) as source_key, '
set @FinalSQL = @FinalSQL+'isnull(source,'''') as source, isnull(descrip,'''') as descrip, '
set @FinalSQL = @FinalSQL+'isnull(acost,0.0000) as acost, isnull(numbermail,0) as numbermail, '
set @FinalSQL = @FinalSQL+'.001*isnull(acost,0.0000)*isnull(numbermail,0) as fullcost, '
set @FinalSQL = @FinalSQL+'0 as giftcount, 0.00 as giftamount '
set @FinalSQL = @FinalSQL+'FROM source WHERE source like '''+@WildCard+''' '
if @StartDate is not null
set @FinalSQL = @FinalSQL+'AND Source.Date >= '''+convert(char(23), @StartDate, 121)+''' '
if @StartDate is not null
set @FinalSQL = @FinalSQL+'AND Source.Date <= '''+convert(char(23), @EndDate, 121)+''' '
set @FinalSQL = @FinalSQL+'ORDER BY GroupByCode, Source'
create table #TempSourceXRef (GroupByCode varchar(20),
source_key integer,
source varchar(20),
descrip nvarchar(100),
acost money,
numbermail integer,
fullcost numeric(14,4),
giftcount integer,
giftamount money)
insert into #TempSourceXRef
exec (@FinalSQL)
if isnull(@SegKey,0) <> 0 -- set up the donor segment
begin
set ansi_nulls on
set ansi_warnings on
declare @SegSQL varchar(8000)
create table #TempSegSQL (sqlstring nvarchar(3800) null)
set @SegSQL = 'select cast(isnull(vfp_sql,'' '') as nvarchar(3800)) as sqlstring '
set @SegSQL = @SegSQL + 'from '+@SegServer+'.dbo.SysSeg '
set @SegSQL = @SegSQL + 'where sysseg_key='+convert(char,@SegKey)+' '
insert into #TempSegSQL
exec(@SegSQL)
declare @l_cSQL nvarchar(3800)
select @l_cSQL = min(sqlstring)
from #TempSegSQL
drop table #TempSegSQL
create table #TempDonorSegment (names_ID char(9) null)
insert into #TempDonorSegment (names_ID)
exec(@l_cSQL)
-- update the mailing counts for the donor segment
select #TempSourceXRef.source_key, count(*) as numbermail
into #TempMailingCounts
from #TempSourceXRef join Mailing
on #TempSourceXRef.source_key = Mailing.source_key
join #TempDonorSegment
on Mailing.Names_ID = #TempDonorSegment.Names_ID
group by #TempSourceXRef.source_key
update #TempSourceXRef
set numbermail = #TempMailingCounts.numbermail
from #TempSourceXRef join #TempMailingCounts
on #TempSourceXRef.source_key = #TempMailingCounts.source_key
drop table #TempMailingCounts
-- update the gift counts and amounts for the donor segment
select #TempSourceXRef.source_key, count(*) as giftcount, sum(camt) as giftamount
into #TempGiftCountDS
from #TempSourceXRef join Contrib
on #TempSourceXRef.source_key = Contrib.source_key
and contrib.moneytype<>-1
and contrib.type<>6
join #TempDonorSegment
on Contrib.Names_ID = #TempDonorSegment.Names_ID
group by #TempSourceXRef.source_key
update #TempSourceXRef
set giftcount = #TempGiftCountDS.giftcount,
giftamount = #TempGiftCountDS.giftamount
from #TempSourceXRef join #TempGiftCountDS
on #TempSourceXRef.source_key = #TempGiftCountDS.source_key
drop table #TempGiftCountDS
end
else -- no donor segment was selected
begin
-- update the gift counts and amounts when no donor segment was selected
select #TempSourceXRef.source_key, count(*) as giftcount, sum(camt) as giftamount
into #TempGiftCount
from #TempSourceXRef join Contrib
on #TempSourceXRef.source_key = Contrib.source_key
and contrib.moneytype<>-1
and contrib.type<>6
group by #TempSourceXRef.source_key
update #TempSourceXRef
set giftcount = #TempGiftCount.giftcount,
giftamount = #TempGiftCount.giftamount
from #TempSourceXRef join #TempGiftCount
on #TempSourceXRef.source_key = #TempGiftCount.source_key
drop table #TempGiftCount
end
-- finish cleaning up donor segment if it was used
if (isnull(@SegKey,0) <> 0)
drop table #TempDonorSegment
-- Group the sources by GroupByCode
declare SourceGroup_Cursor cursor for
select GroupByCode,
sum(numbermail) as numbermail,
sum(fullcost) as fullcost,
sum(giftcount) as giftcount,
sum(giftamount) as giftamount
from #TempSourceXRef
Group by GroupByCode
open SourceGroup_Cursor
declare @SourceGroup_Code varchar(20)
declare @SourceGroup_numbermail integer
declare @SourceGroup_fullcost numeric(14,4)
declare @SourceGroup_GiftCount integer
declare @SourceGroup_GiftAmount numeric(14,4)
declare @SourceGroup_averagecost numeric(14,4)
declare @SourceGroup_Desc nvarchar(3800)
create table #TempSourceGroup (GroupByCode varchar(20),
numbermail integer,
fullcost numeric(14,4),
averagecost numeric(14,4),
giftcount integer,
giftamount numeric(14,4),
SourceGroupDesc nvarchar(3800))
fetch next from SourceGroup_cursor
into @SourceGroup_Code,
@SourceGroup_NumberMail,
@SourceGroup_FullCost,
@SourceGroup_GiftCount,
@SourceGroup_GiftAmount
WHILE @@FETCH_STATUS = 0
BEGIN
set @SourceGroup_Desc = ''
Select @SourceGroup_Desc=@SourceGroup_Desc+ltrim(rtrim(source))+' - '+ltrim(rtrim(isnull(descrip,'')))+char(10)+char(13)
from #TempSourceXRef
where GroupByCode = @SourceGroup_Code
insert into #TempSourceGroup (GroupByCode, NumberMail, FullCost, AverageCost, GiftCount, GiftAmount,SourceGroupDesc)
values (@SourceGroup_Code, @SourceGroup_NumberMail, @SourceGroup_FullCost,
(case when @SourceGroup_NumberMail = 0 then 0.00
else @SourceGroup_FullCost/@SourceGroup_NumberMail end),
@SourceGroup_GiftCount, @SourceGroup_GiftAmount, @SourceGroup_Desc)
fetch next from SourceGroup_cursor
into @SourceGroup_Code,
@SourceGroup_NumberMail,
@SourceGroup_FullCost,
@SourceGroup_GiftCount,
@SourceGroup_GiftAmount
END
CLOSE SourceGroup_Cursor
DEALLOCATE SourceGroup_Cursor
drop table #TempSourceXRef
-- Gather New Mailplan Info From SYSSeg
create table #TempMCSource (source varchar(20), descrip nvarchar(100))
set @FinalSQL = 'SELECT keycode AS source, isnull(name,'''') AS descrip FROM '
set @FinalSQL = @FinalSQL+@SegServer+'.dbo.SysSeg where omission=0 and sysquery_key = '+ltrim(convert(char,@SysQuery_Key))
insert into #TempMCSource
exec (@FinalSQL)
-- Gather Source Codes from NAMES.MailCode and Group Them into #TempMCGroup table
set @FinalSQL = 'SELECT max('+@GroupByString+') as GroupByCode, source, descrip, '
set @FinalSQL = @FinalSQL+' sum(case when mailcode is null then 0 else 1 end) as numbermail '
set @FinalSQL = @FinalSQL+'FROM #TempMCSource LEFT OUTER JOIN Names on #TempMCSource.source = Names.MailCode '
set @FinalSQL = @FinalSQL+'GROUP BY source, descrip ORDER BY GroupByCode, Source'
create table #TempMCXRef (GroupByCode varchar(20),
source varchar(20),
descrip nvarchar(100),
numbermail integer)
insert into #TempMCXRef
exec (@FinalSQL)
drop table #TempMCSource
declare MCGroup_Cursor cursor for
select GroupByCode,
sum(numbermail) as numbermail
from #TempMCXRef
Group by GroupByCode
open MCGroup_Cursor
declare @MCGroup_Code varchar(20)
declare @MCGroup_numbermail integer
declare @MCGroup_Desc nvarchar(3800)
create table #TempMCGroup (GroupByCode varchar(20),
numbermail integer,
MCGroupDesc nvarchar(3800))
fetch next from MCGroup_cursor
into @MCGroup_Code, @MCGroup_NumberMail
WHILE @@FETCH_STATUS = 0
BEGIN
set @MCGroup_Desc = ''
Select @MCGroup_Desc=@MCGroup_Desc+ltrim(rtrim(source))+' - '+ltrim(rtrim(isnull(descrip,'')))+char(10)+char(13)
from #TempMCXRef
where GroupByCode = @MCGroup_Code
insert into #TempMCGroup (GroupByCode, NumberMail, MCGroupDesc)
values (@MCGroup_Code, @MCGroup_NumberMail, @MCGroup_Desc)
fetch next from MCGroup_cursor
into @MCGroup_Code, @MCGroup_NumberMail
END
CLOSE MCGroup_Cursor
DEALLOCATE MCGroup_Cursor
drop table #TempMCXRef
select isnull(#TempSourceGroup.GroupByCode,#TempMCGroup.GroupByCode) as GroupByCode,
convert(text,isnull(#TempSourceGroup.SourceGroupDesc,'')) as SourceGroupDesc,
convert(text,isnull(#TempMCGroup.MCGroupDesc,'')) as MCGroupDesc,
isnull(#TempSourceGroup.NumberMail,0) as OldNumberMail,
isnull(#TempSourceGroup.GiftCount,0) as GiftCount,
isnull(#TempSourceGroup.GiftAmount,0.00) as GiftAmount,
isnull(#TempMCGroup.NumberMail,0) as NewNumberMail,
(case when isnull(#TempSourceGroup.GiftCount,0)=0 then 0 else
isnull(#TempMCGroup.NumberMail,0)*isnull(#TempSourceGroup.GiftAmount,0.00)/
isnull(#TempSourceGroup.GiftCount,0) end) as ProjectedRev,
isnull(#TempSourceGroup.AverageCost,0.0000)*isnull(#TempMCGroup.NumberMail,0) as NewCost
from #TempSourceGroup full outer join #TempMCGroup
on #TempSourceGroup.GroupByCode = #TempMCGroup.GroupByCode
drop table #TempSourceGroup
drop table #TempMCGroup
GO
The report's data source is the following stored procedure:
CREATE PROCEDURE dbo.sp_ComparativeCampaignAnalysis
@SysQuery_Key integer = null,
@SegKey integer = null,
@SegServer nvarchar(50) = '[MDTNTS-MDA].MoneyMaker_System',
@StartDate datetime = null,
@EndDate datetime = null,
@WildCard varchar(20) = '%',
@GroupByString nvarchar(3800) = 'source'
AS
set nocount on
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Gather Source Codes from SOURCE Table and Group Them into #TempSourceGroup table
set @WildCard = (case when isnull(@WildCard,'')='' then '%' else @WildCard end)
declare @FinalSQL nvarchar(3800)
set @FinalSQL = 'SELECT '+@GroupByString+' as GroupByCode, isnull(source_key,0) as source_key, '
set @FinalSQL = @FinalSQL+'isnull(source,'''') as source, isnull(descrip,'''') as descrip, '
set @FinalSQL = @FinalSQL+'isnull(acost,0.0000) as acost, isnull(numbermail,0) as numbermail, '
set @FinalSQL = @FinalSQL+'.001*isnull(acost,0.0000)*isnull(numbermail,0) as fullcost, '
set @FinalSQL = @FinalSQL+'0 as giftcount, 0.00 as giftamount '
set @FinalSQL = @FinalSQL+'FROM source WHERE source like '''+@WildCard+''' '
if @StartDate is not null
set @FinalSQL = @FinalSQL+'AND Source.Date >= '''+convert(char(23), @StartDate, 121)+''' '
if @StartDate is not null
set @FinalSQL = @FinalSQL+'AND Source.Date <= '''+convert(char(23), @EndDate, 121)+''' '
set @FinalSQL = @FinalSQL+'ORDER BY GroupByCode, Source'
create table #TempSourceXRef (GroupByCode varchar(20),
source_key integer,
source varchar(20),
descrip nvarchar(100),
acost money,
numbermail integer,
fullcost numeric(14,4),
giftcount integer,
giftamount money)
insert into #TempSourceXRef
exec (@FinalSQL)
if isnull(@SegKey,0) <> 0 -- set up the donor segment
begin
set ansi_nulls on
set ansi_warnings on
declare @SegSQL varchar(8000)
create table #TempSegSQL (sqlstring nvarchar(3800) null)
set @SegSQL = 'select cast(isnull(vfp_sql,'' '') as nvarchar(3800)) as sqlstring '
set @SegSQL = @SegSQL + 'from '+@SegServer+'.dbo.SysSeg '
set @SegSQL = @SegSQL + 'where sysseg_key='+convert(char,@SegKey)+' '
insert into #TempSegSQL
exec(@SegSQL)
declare @l_cSQL nvarchar(3800)
select @l_cSQL = min(sqlstring)
from #TempSegSQL
drop table #TempSegSQL
create table #TempDonorSegment (names_ID char(9) null)
insert into #TempDonorSegment (names_ID)
exec(@l_cSQL)
-- update the mailing counts for the donor segment
select #TempSourceXRef.source_key, count(*) as numbermail
into #TempMailingCounts
from #TempSourceXRef join Mailing
on #TempSourceXRef.source_key = Mailing.source_key
join #TempDonorSegment
on Mailing.Names_ID = #TempDonorSegment.Names_ID
group by #TempSourceXRef.source_key
update #TempSourceXRef
set numbermail = #TempMailingCounts.numbermail
from #TempSourceXRef join #TempMailingCounts
on #TempSourceXRef.source_key = #TempMailingCounts.source_key
drop table #TempMailingCounts
-- update the gift counts and amounts for the donor segment
select #TempSourceXRef.source_key, count(*) as giftcount, sum(camt) as giftamount
into #TempGiftCountDS
from #TempSourceXRef join Contrib
on #TempSourceXRef.source_key = Contrib.source_key
and contrib.moneytype<>-1
and contrib.type<>6
join #TempDonorSegment
on Contrib.Names_ID = #TempDonorSegment.Names_ID
group by #TempSourceXRef.source_key
update #TempSourceXRef
set giftcount = #TempGiftCountDS.giftcount,
giftamount = #TempGiftCountDS.giftamount
from #TempSourceXRef join #TempGiftCountDS
on #TempSourceXRef.source_key = #TempGiftCountDS.source_key
drop table #TempGiftCountDS
end
else -- no donor segment was selected
begin
-- update the gift counts and amounts when no donor segment was selected
select #TempSourceXRef.source_key, count(*) as giftcount, sum(camt) as giftamount
into #TempGiftCount
from #TempSourceXRef join Contrib
on #TempSourceXRef.source_key = Contrib.source_key
and contrib.moneytype<>-1
and contrib.type<>6
group by #TempSourceXRef.source_key
update #TempSourceXRef
set giftcount = #TempGiftCount.giftcount,
giftamount = #TempGiftCount.giftamount
from #TempSourceXRef join #TempGiftCount
on #TempSourceXRef.source_key = #TempGiftCount.source_key
drop table #TempGiftCount
end
-- finish cleaning up donor segment if it was used
if (isnull(@SegKey,0) <> 0)
drop table #TempDonorSegment
-- Group the sources by GroupByCode
declare SourceGroup_Cursor cursor for
select GroupByCode,
sum(numbermail) as numbermail,
sum(fullcost) as fullcost,
sum(giftcount) as giftcount,
sum(giftamount) as giftamount
from #TempSourceXRef
Group by GroupByCode
open SourceGroup_Cursor
declare @SourceGroup_Code varchar(20)
declare @SourceGroup_numbermail integer
declare @SourceGroup_fullcost numeric(14,4)
declare @SourceGroup_GiftCount integer
declare @SourceGroup_GiftAmount numeric(14,4)
declare @SourceGroup_averagecost numeric(14,4)
declare @SourceGroup_Desc nvarchar(3800)
create table #TempSourceGroup (GroupByCode varchar(20),
numbermail integer,
fullcost numeric(14,4),
averagecost numeric(14,4),
giftcount integer,
giftamount numeric(14,4),
SourceGroupDesc nvarchar(3800))
fetch next from SourceGroup_cursor
into @SourceGroup_Code,
@SourceGroup_NumberMail,
@SourceGroup_FullCost,
@SourceGroup_GiftCount,
@SourceGroup_GiftAmount
WHILE @@FETCH_STATUS = 0
BEGIN
set @SourceGroup_Desc = ''
Select @SourceGroup_Desc=@SourceGroup_Desc+ltrim(rtrim(source))+' - '+ltrim(rtrim(isnull(descrip,'')))+char(10)+char(13)
from #TempSourceXRef
where GroupByCode = @SourceGroup_Code
insert into #TempSourceGroup (GroupByCode, NumberMail, FullCost, AverageCost, GiftCount, GiftAmount,SourceGroupDesc)
values (@SourceGroup_Code, @SourceGroup_NumberMail, @SourceGroup_FullCost,
(case when @SourceGroup_NumberMail = 0 then 0.00
else @SourceGroup_FullCost/@SourceGroup_NumberMail end),
@SourceGroup_GiftCount, @SourceGroup_GiftAmount, @SourceGroup_Desc)
fetch next from SourceGroup_cursor
into @SourceGroup_Code,
@SourceGroup_NumberMail,
@SourceGroup_FullCost,
@SourceGroup_GiftCount,
@SourceGroup_GiftAmount
END
CLOSE SourceGroup_Cursor
DEALLOCATE SourceGroup_Cursor
drop table #TempSourceXRef
-- Gather New Mailplan Info From SYSSeg
create table #TempMCSource (source varchar(20), descrip nvarchar(100))
set @FinalSQL = 'SELECT keycode AS source, isnull(name,'''') AS descrip FROM '
set @FinalSQL = @FinalSQL+@SegServer+'.dbo.SysSeg where omission=0 and sysquery_key = '+ltrim(convert(char,@SysQuery_Key))
insert into #TempMCSource
exec (@FinalSQL)
-- Gather Source Codes from NAMES.MailCode and Group Them into #TempMCGroup table
set @FinalSQL = 'SELECT max('+@GroupByString+') as GroupByCode, source, descrip, '
set @FinalSQL = @FinalSQL+' sum(case when mailcode is null then 0 else 1 end) as numbermail '
set @FinalSQL = @FinalSQL+'FROM #TempMCSource LEFT OUTER JOIN Names on #TempMCSource.source = Names.MailCode '
set @FinalSQL = @FinalSQL+'GROUP BY source, descrip ORDER BY GroupByCode, Source'
create table #TempMCXRef (GroupByCode varchar(20),
source varchar(20),
descrip nvarchar(100),
numbermail integer)
insert into #TempMCXRef
exec (@FinalSQL)
drop table #TempMCSource
declare MCGroup_Cursor cursor for
select GroupByCode,
sum(numbermail) as numbermail
from #TempMCXRef
Group by GroupByCode
open MCGroup_Cursor
declare @MCGroup_Code varchar(20)
declare @MCGroup_numbermail integer
declare @MCGroup_Desc nvarchar(3800)
create table #TempMCGroup (GroupByCode varchar(20),
numbermail integer,
MCGroupDesc nvarchar(3800))
fetch next from MCGroup_cursor
into @MCGroup_Code, @MCGroup_NumberMail
WHILE @@FETCH_STATUS = 0
BEGIN
set @MCGroup_Desc = ''
Select @MCGroup_Desc=@MCGroup_Desc+ltrim(rtrim(source))+' - '+ltrim(rtrim(isnull(descrip,'')))+char(10)+char(13)
from #TempMCXRef
where GroupByCode = @MCGroup_Code
insert into #TempMCGroup (GroupByCode, NumberMail, MCGroupDesc)
values (@MCGroup_Code, @MCGroup_NumberMail, @MCGroup_Desc)
fetch next from MCGroup_cursor
into @MCGroup_Code, @MCGroup_NumberMail
END
CLOSE MCGroup_Cursor
DEALLOCATE MCGroup_Cursor
drop table #TempMCXRef
select isnull(#TempSourceGroup.GroupByCode,#TempMCGroup.GroupByCode) as GroupByCode,
convert(text,isnull(#TempSourceGroup.SourceGroupDesc,'')) as SourceGroupDesc,
convert(text,isnull(#TempMCGroup.MCGroupDesc,'')) as MCGroupDesc,
isnull(#TempSourceGroup.NumberMail,0) as OldNumberMail,
isnull(#TempSourceGroup.GiftCount,0) as GiftCount,
isnull(#TempSourceGroup.GiftAmount,0.00) as GiftAmount,
isnull(#TempMCGroup.NumberMail,0) as NewNumberMail,
(case when isnull(#TempSourceGroup.GiftCount,0)=0 then 0 else
isnull(#TempMCGroup.NumberMail,0)*isnull(#TempSourceGroup.GiftAmount,0.00)/
isnull(#TempSourceGroup.GiftCount,0) end) as ProjectedRev,
isnull(#TempSourceGroup.AverageCost,0.0000)*isnull(#TempMCGroup.NumberMail,0) as NewCost
from #TempSourceGroup full outer join #TempMCGroup
on #TempSourceGroup.GroupByCode = #TempMCGroup.GroupByCode
drop table #TempSourceGroup
drop table #TempMCGroup
GO