Hi. I've a problem with my report. I'm using Crystal Reports XI R2, and connect with a SqlServer 2008 database. I've a StoredProcedure (big procedure, rsrs) that returns a cursor. When I insert my StoredProcudure with OLEDB Provider, I recive an error:  The error in portuguese brazil, i think in english is: "Description: the cursor name 'cur_multa' doesn't exists" My StoredProcedure: CREATE PROCEDURE [dbo].[SPGetPrintNotificacao](@GetType int,
@ViewMode varchar(20),
@OrgAutuador char(6),
@CodMunicipio int,
@LoteNum int,
@LoteAno int,
@AiipSer char(1) = null,
@AiipFai char(2) = null,
@AiipNum int = null,
@AiipNumDV int = null,
@SeqInicial int = null,
@SeqFinal int = null,
@Prazo int = null,
@IdUser int = null,
@ImagePath nvarchar(300),
@DataPost datetime)
AS
begin
set nocount on
declare @tb_multa table(
uid int, viewmode varchar(20), orgao_autuador char(6), codmunicipio int, codmunicipio_desc nvarchar(120), codmunicipiouf char(2), nome_proprietario varchar(180), endereco_proprietario varchar(150), numero_proprietario varchar(12), complemento varchar(30), cep_proprietario varchar(10),
municipio_proprietario varchar(100), uf_proprietario char(2), nome_indic varchar(120), cnh_indic varchar(20), dt_infracao datetime, dt_processamento datetime,
numero_ait char(10), placa char(7), uf_placa char(2), municipio_placa varchar(50),
marca nvarchar(100), modelo varchar(100), especie nvarchar(100), tipo nvarchar(100), categoria nvarchar(100),
cod_renainf int, local_infracao_a_desc varchar(120), local_infracao_a_num varchar(10), local_infracao_a_pos varchar(25), local_infracao_b_desc varchar(120),
valor_real varchar(20), descricao varchar(250), enqto int, base_legal varchar(40), classificacao varchar(25), pontos smallint, velocidade_reg int, velocidade_med int,
velocidade_con int, radarequip varchar(50), radarmarca varchar(50), radarmodelo varchar(50), dt_verificacao datetime, obs varchar(120), dt_limite_recurso datetime, numerorl varchar(9), numerorl_formatado varchar(13),
numimpr int, lote char(8), agente varchar(15), contrato_correio varchar(15), ImgNumber int, ImgCartaSeedRL nvarchar(300), ImgNameA nvarchar(300),
ImgNameB nvarchar(300), SegundaVia bit, CodigoDescr varchar(50), CodigoInfr float, dt_postagem datetime, Segundos char(1), perm_decibel char(10), afer_decibel char(10), cons_decibel char(10))
declare @Ser char(1), @Fai nvarchar(2), @Ait int, @NumDV int, @Placa char(7)
declare @NotifDtImpr datetime, @Enq int, @NotifSeqImpr int
declare @Uid int
declare @Passei int
declare @CodigoDescr varchar(50), @CodigoInfr float
declare @NotifDtPost datetime
declare @NotifSeqArFormatado nvarchar(13)
declare @VLoteNum int, @VLoteAno int
declare @SegundaVia bit
declare @Current_RL int, @Min_RL int, @NotifSeqAr int
declare @Contrato varchar(15)
declare @Segundos char(1)
declare @Prodesp int, @Renainf int, @PeriodoProdesp int, @PeriodoRenainf int
declare @PropNome varchar(180), @PropEnder varchar(120), @PropComplemento varchar(30), @PropNumero varchar(15), @PropCep char(8), @PropCodMunic int, @PropMunic varchar(100), @PropMunicUf char(2)
declare @CodRenainf int
declare @ValorReal varchar(20), @DescricaoEnq varchar(250), @BaseLegal varchar(40), @ClassificacaoEnq varchar(25), @PontosEnq smallint
declare @NomeCondutor varchar(120), @CNHCondutor varchar(20)
declare @CodMunicipioDesc nvarchar(50), @CodMunicipioUf char(2)
declare @marca_uid int, @marca varchar(100), @modelo varchar(100), @especie varchar(100), @tipo varchar(100), @categoria varchar(50)
declare @InfracaoData datetime, @AgCod varchar(15), @PlacaMunDesc varchar(50), @PlacaUf char(2), @placamunic int
declare @LocalInfracaoADesc varchar(120), @LocalInfracaoANum varchar(10), @LocalInfracaoAPos varchar(25), @LocalInfracaoBDesc varchar(120), @Observacao varchar(120)
declare @VelocidadeReg int, @VelocidadeMed int, @VelocidadeCon int, @radarequip varchar(50), @radarmarca varchar(50), @radarmodelo varchar(50), @DataAfericaoEquip datetime
declare @PermDecibel char(10), @AferDecibel char(10), @ConsDecibel char(10)
declare @ImgPrefixo char(1)
declare @TotalImg int, @ImgNameA nvarchar(300), @ImgNameB nvarchar(300), @ImgCartaSeedRL nvarchar(300)
declare @DtLimiteRecurso datetime
declare @TipoLote int
set @Passei = 0
set @ViewMode = 'AR'
set @SegundaVia = 0
if @GetType = 0 -- imprimi um ait especifico (mais usado na segunda via)
begin
if (@AiipNumDv is null) or (@AiipNumDv = 0)
begin
declare cur_multa cursor local fast_forward for
select aiip_ser, aiip_fai, aiip_num, aiip_num_dv, placa, notif_dt_impr, notif_seq_ar, notif_dt_post, enqto, notif_seq_impr from tb_multa
where aiip_ser = @AiipSer and aiip_fai = @AiipFai and aiip_num = @AiipNum and lote_num = @LoteNum and lote_ano = @LoteAno
order by notif_seq_impr
open cur_multa
end
else
begin
declare cur_multa cursor local fast_forward for
select aiip_ser, aiip_fai, aiip_num, aiip_num_dv, placa, notif_dt_impr, notif_seq_ar, notif_dt_post, enqto, notif_seq_impr from tb_multa
where aiip_ser = @AiipSer and aiip_fai = @AiipFai and aiip_num = @AiipNum and aiip_num_dv = @AiipNumDv and lote_num = @LoteNum and lote_ano = @LoteAno
order by notif_seq_impr
open cur_multa
end
end
else if @GetType = 1 -- imprimi uma sequencia
begin
if @SeqFinal > 0
begin
declare cur_multa cursor local fast_forward for
select aiip_ser, aiip_fai, aiip_num, aiip_num_dv, placa, notif_dt_impr, notif_seq_ar, notif_dt_post, enqto, notif_seq_impr from tb_multa
where notif_seq_impr between @SeqInicial and @SeqFinal and lote_num = @LoteNum and lote_ano = @LoteAno
order by notif_seq_impr
open cur_multa
end
else
begin
declare cur_multa cursor local fast_forward for
select aiip_ser, aiip_fai, aiip_num, aiip_num_dv, placa, notif_dt_impr, notif_seq_ar, notif_dt_post, enqto, notif_seq_impr from tb_multa
where notif_seq_impr >= @SeqInicial and lote_num = @LoteNum and lote_ano = @LoteAno
order by notif_seq_impr
open cur_multa
end
end
else if @GetType = 2 -- imprimi o lote inteiro
begin
declare cur_multa cursor local fast_forward for
select aiip_ser, aiip_fai, aiip_num, aiip_num_dv, placa, notif_dt_impr, notif_seq_ar, notif_dt_post, enqto, notif_seq_impr from tb_multa
where lote_num = @LoteNum and lote_ano = @LoteAno
order by notif_seq_impr
open cur_multa
end
else if @GetType = 3 -- segunda via
begin
set @SegundaVia = 1
if (@AiipNumDv is null) or (@AiipNumDv = 0)
begin
declare cur_multa cursor local fast_forward for
select aiip_ser, aiip_fai, aiip_num, aiip_num_dv, placa, notif_dt_impr, notif_seq_ar, notif_dt_post, enqto, notif_seq_impr from tb_multa
where aiip_ser = @AiipSer and aiip_fai = @AiipFai and aiip_num = @AiipNum and lote_num = @LoteNum and lote_ano = @LoteAno
order by notif_seq_impr
open cur_multa
end
else
begin
declare cur_multa cursor local fast_forward for
select aiip_ser, aiip_fai, aiip_num, aiip_num_dv, placa, notif_dt_impr, notif_seq_ar, notif_dt_post, enqto, notif_seq_impr from tb_multa
where aiip_ser = @AiipSer and aiip_fai = @AiipFai and aiip_num = @AiipNum and aiip_num_dv = @AiipNumDv and lote_num = @LoteNum and lote_ano = @LoteAno
order by notif_seq_impr
open cur_multa
end
end
set @Uid = 1
set @CodMunicipioDesc = (select descri from tb_veic_def_munic where uid = @CodMunicipio)
set @CodMunicipioUf = (select uf from tb_veic_def_munic where uid = @CodMunicipio)
fetch next from cur_multa into @Ser, @Fai, @Ait, @NumDV, @Placa, @NotifDtImpr, @NotifSeqAr, @NotifDtPost, @Enq, @NotifSeqImpr
while @@fetch_status = 0
begin
set @Contrato = (select top 1 contrato from tb_correio_cont where num_atu < num_fin)
if @NotifDtImpr is null
begin
set @NotifSeqAr = null
set @NotifDtImpr = getdate()
if @ViewMode = 'AR'
begin
exec @Current_RL = SPSetCurrentRL @Min_RL output
if @Current_RL = 0
break
else
begin
set @NotifSeqAr = (select num_atu from tb_correio_cont where contrato = @Contrato)
set @NotifSeqAr = dbo.CalcSeqAr(@NotifSeqAr)
end
end
exec SPSetPrintNotifDtImpr @Ser, @Fai, @Ait, @NumDV, @Placa, @NotifSeqAr, @Prazo
end
if @NotifDtPost is null
begin
exec SPSetPrintNotifDtPost @Ser, @Fai, @Ait, @NumDV, @Placa, @DataPost
if @NumDV is null
set @NotifDtPost = (select notif_dt_post from tb_multa where aiip_ser = @Ser and aiip_fai = @Fai and aiip_num = @Ait)
else
set @NotifDtPost = (select notif_dt_post from tb_multa where aiip_ser = @Ser and aiip_fai = @Fai and aiip_num = @Ait and aiip_num_dv = @NumDv)
end
set @TipoLote = (select tipo_lote from tb_lotes where num = @LoteNum and ano = @LoteAno)
if @SegundaVia = 1
begin
declare cur_prop cursor local fast_forward for
select top 1 placa_munic, prop_nome, ender, ender_num, ender_complem, ender_cep, ender_munic, (select descri from tb_veic_def_categ where uid = categ) as 'categoria', (select descri from tb_veic_def_espec where uid = espec) as 'especie',
(select descri from tb_veic_def_tipo where uid = tipo) as 'tipo' from tb_veiculo_hist where placa = @Placa order by historico desc
open cur_prop
end
else
begin
set @Prodesp = (select seq_prodesp from tb_lotes where num = @LoteNum and ano = @LoteAno)
set @Renainf = (select seq_renainf from tb_lotes where num = @LoteNum and ano = @LoteAno)
set @PeriodoProdesp = (select per_prodesp from tb_lotes where num = @LoteNum and ano = @LoteAno)
set @PeriodoRenainf = (select per_renainf from tb_lotes where num = @LoteNum and ano = @LoteAno)
if @Prodesp > 0
begin
declare cur_prop cursor local fast_forward for
select placa_munic, prop_nome, ender, ender_num, ender_complem, ender_cep, ender_munic, (select descri from tb_veic_def_categ where uid = categ) as 'categoria', (select descri from tb_veic_def_espec where uid = espec) as 'especie',
(select descri from tb_veic_def_tipo where uid = tipo) as 'tipo' from tb_veiculo_hist where placa = @Placa and seq_prodesp = @Prodesp and per_prodesp = @PeriodoProdesp
open cur_prop
end
else if @Renainf > 0
begin
set @Passei = @passei + 1
declare cur_prop cursor local fast_forward for
select placa_munic, prop_nome, ender, ender_num, ender_complem, ender_cep, ender_munic, (select descri from tb_veic_def_categ where uid = categ) as 'categoria', (select descri from tb_veic_def_espec where uid = espec) as 'especie',
(select descri from tb_veic_def_tipo where uid = tipo) as 'tipo' from tb_veiculo_hist where placa = @Placa and seq_renainf = @Renainf and per_renainf = @PeriodoRenainf
open cur_prop
end
end
fetch next from cur_prop into @placamunic, @propnome, @propender, @propnumero, @propcomplemento, @propcep, @propcodmunic, @categoria, @especie, @tipo
if @@fetch_status <> 0
begin
set @placamunic = null
set @propnome = null
set @propender = null
set @propnumero = null
set @propcomplemento = null
set @propcep = null
set @propcodmunic = null
set @categoria = null
set @especie = null
set @tipo = null
end
set @PlacaMunDesc = (select descri from tb_veic_def_munic where uid = @placamunic)
set @PlacaUf = (select uf from tb_veic_def_munic where uid = @placamunic)
set @PropMunic = (select descri from tb_veic_def_munic where uid = @PropCodMunic)
set @propMunicUf = (select uf from tb_veic_def_munic where uid = @PropCodMunic)
close cur_prop
deallocate cur_prop
set @marca_uid = (select marca from tb_veiculo where placa = @placa)
if @marca_uid >= 0
begin
set @marca = (select marca from tb_veic_def_marca where uid = @marca_uid)
set @modelo = (select modelo from tb_veic_def_marca where uid = @marca_uid)
end
declare cur_enqto cursor local fast_forward for
select valor_real, descri, gravidade, pontos, artigo from tb_enqto where enq = @Enq
open cur_enqto
fetch next from cur_enqto into @ValorReal, @DescricaoEnq, @ClassificacaoEnq, @PontosEnq, @BaseLegal
if @@fetch_status <> 0
begin
set @ValorReal = null
set @DescricaoEnq = null
set @ClassificacaoEnq = null
set @PontosEnq = null
set @BaseLegal = null
end
close cur_enqto
deallocate cur_enqto
--if @NumDV is null
--begin
-- declare cur_cond cursor local fast_forward for
-- select condutor_nome, condutor_cnh from tb_condutor where aiip_ser = @Ser and aiip_fai = @Fai and aiip_num = @Ait
-- open cur_cond
-- fetch next from cur_cond into @NomeCondutor, @CNHCondutor
-- if @@fetch_status <> 0
-- begin
-- set @NomeCondutor = null
-- set @CNHCondutor= null
-- end
-- close cur_cond
-- deallocate cur_cond
--end
--else
--begin
-- declare cur_cond cursor local fast_forward for
-- select condutor_nome, condutor_cnh from tb_condutor where aiip_ser = @Ser and aiip_fai = @Fai and aiip_num = @Ait and aiip_num_dv = @NumDV
-- open cur_cond
-- fetch next from cur_cond into @NomeCondutor, @CNHCondutor
-- if @@fetch_status <> 0
-- begin
-- end
-- close cur_cond
-- deallocate cur_cond
--end
-- limpa as variaveis para garantir
set @InfracaoData = null
set @AgCod = null
set @LocalInfracaoADesc = null
set @LocalInfracaoANum = null
set @LocalInfracaoAPos = null
set @LocalInfracaoBDesc = null
set @VelocidadeMed = null
set @VelocidadeReg = null
set @VelocidadeCon = null
set @radarequip = null
set @radarmarca = null
set @radarmodelo = null
set @DataAfericaoEquip = null
set @Observacao = null
set @NomeCondutor = null
set @CNHCondutor = null
set @PermDecibel = null
set @ConsDecibel = null
set @AferDecibel = null
if (dbo.GetTypeSerFai(@Ser, @Fai)) = 'Talão'
begin
declare cur_talao cursor local fast_forward for
select infracao_data, ag_cod, logradouro_a_desc, logradouro_a_num,
logradouro_a_pos, logradouro_b_desc, obs,
condutor_nome, condutor_cnh, afericao_cons , afericao_limite, afericao_medicao, afericao_equip from tb_entrada_talao
where aiip_ser = @Ser and aiip_fai = @Fai and aiip_num = @Ait
open cur_talao
fetch next from cur_talao into @InfracaoData, @AgCod, @LocalInfracaoaDesc, @LocalInfracaoANum,
@LocalInfracaoAPos, @LocalInfracaoBDesc, @Observacao,
@NomeCondutor, @CNHCondutor, @ConsDecibel, @PermDecibel, @AferDecibel, @radarequip
if @@fetch_status <> 0
begin
set @InfracaoData = null
set @AgCod = null
set @LocalInfracaoaDesc = null
set @LocalInfracaoANum = null
set @LocalInfracaoAPos = null
set @LocalInfracaoBDesc = null
set @Observacao = null
set @NomeCondutor = null
set @CNHCondutor = null
end
close cur_talao
deallocate cur_talao
end
else
begin
declare cur_radar cursor local fast_forward for
select infracao_data, ag_cod, logradouro_desc, logradouro_num, logradouro_pos, vel_aferida, vel_permitida, vel_considerada, radar_num, radar_marca, radar_modelo, radar_dt_afer, obs, condutor_nome, condutor_cnh
from tb_entrada_eletronicos where aiip_ser = @Ser and aiip_fai = @Fai and aiip_num = @Ait
open cur_radar
fetch next from cur_radar into @InfracaoData, @AgCod, @LocalInfracaoADesc, @LocalInfracaoANum, @LocalInfracaoAPos, @VelocidadeMed, @VelocidadeReg, @VelocidadeCon, @radarequip, @radarmarca, @radarmodelo, @DataAfericaoEquip, @Observacao, @NomeCondutor, @CNHCondutor
if @@fetch_status <> 0
begin
set @InfracaoData = null
set @AgCod = null
set @LocalInfracaoADesc = null
set @LocalInfracaoANum = null
set @LocalInfracaoAPos = null
set @LocalInfracaoBDesc = null
set @VelocidadeMed = null
set @VelocidadeReg = null
set @VelocidadeCon = null
set @radarequip = null
set @radarmarca = null
set @radarmodelo = null
set @DataAfericaoEquip = null
set @Observacao = null
set @NomeCondutor = null
set @CNHCondutor = null
end
close cur_radar
deallocate cur_radar
end
if @LocalInfracaoANum = '0'
set @LocalInfracaoANum = ''
set @TotalImg = 0
set @ImgNameA = 'NoImage'
set @ImgNameB = 'NoImage'
set @ImgPrefixo = 'P'
if (dbo.GetTypeSerFai(@Ser, @Fai)) = 'Radar'
begin
set @TotalImg = (select count(*) from tb_image_eletronicos where aiip_ser = @Ser and aiip_fai = @Fai and aiip_num = @Ait)
if @TotalImg = 1
--set @ImgNameA = @ImagePath + @ImgPrefixo + @Ser + @Fai + dbo.FormatAit(@Ait) + '.jpg'
set @ImgNameA = @ImagePath + @ImgPrefixo + dbo.FCFormatAITNotif(@Ser, @Fai, @Ait, 0) + '.jpg'
else if @TotalImg >= 2
begin
set @ImgNameA = @ImagePath + @ImgPrefixo + @Ser + @Fai + dbo.FormatAit(@Ait) + '.jpg'
set @ImgNameB = @ImagePath + @ImgPrefixo + @Ser + @Fai + dbo.FormatAit(@Ait) + 'B.jpg'
end
else
begin
set @ImgNameA = 'NoImage'
set @ImgNameB = 'NoImage'
end
end
set @ImgCartaSeedRL = ''
if @VelocidadeReg is null
set @VelocidadeReg = 0
if @VelocidadeMed is null
set @VelocidadeMed = 0
if @VelocidadeCon is null
set @VelocidadeCon = 0
set @CodigoDescr = null
set @CodigoInfr = null
if (@NumDv is null) or (@NumDv = 0)
begin
if @TipoLote in (1, 2)
begin
set @CodigoDescr = 'Código Infraest'
set @CodigoInfr = (select top 1 cod_infraest from tb_infraest where aiip_ser = @Ser and aiip_fai = @Fai and aiip_num = @Ait)
end
if @TipoLote in (3, 4)
begin
set @CodigoDescr = 'Código Renainf'
set @CodigoInfr = (select top 1 cod_renainf from tb_renainf where aiip_ser = @Ser and aiip_fai = @Fai and aiip_num = @Ait)
end
end
else
begin
if @TipoLote in (1, 2)
begin
set @CodigoDescr = 'Código Infraest'
set @CodigoInfr = (select top 1 cod_infraest from tb_infraest where aiip_ser = @Ser and aiip_fai = @Fai and aiip_num = @Ait and aiip_dv = @NumDv)
end
if @TipoLote in (3, 4)
begin
set @CodigoDescr = 'Código Renainf'
set @CodigoInfr = (select top 1 cod_renainf from tb_renainf where aiip_ser = @Ser and aiip_fai = @Fai and aiip_num = @Ait and aiip_num_dv = @NumDv)
end
end
if (@CodigoInfr is null) or (@CodigoInfr = 0)
begin
set @CodigoDescr = Null
set @CodigoInfr = null
end
if dbo.GetTypeSerFai(@Ser, @Fai) in ('Talão', 'Palm', 'Zona Azul')
set @Segundos = 'n'
else
begin
if @NotifDtImpr > '2009-03-18 23:59:59'
set @Segundos = 's'
else
set @Segundos = 'n'
end
set @DtLimiteRecurso = (select dt_indic_cond from tb_multa where aiip_ser = @Ser and aiip_fai = @Fai and aiip_num = @Ait and placa = @placa)
if @DtLimiteRecurso is null
begin
set @DtLimiteRecurso = dbo.FCCalculateVencDate(getdate(), @Prazo)
set @DtLimiteRecurso = cast(convert(varchar(25), @DtLimiteRecurso, 102) as datetime)
end
set @valorreal = replace(substring(@valorreal, 1, charindex('.', @valorreal) + 2), '.', ',')
set @NotifSeqArFormatado = cast(@NotifSeqAr as nvarchar(9))
while len(@NotifSeqArFormatado) < 9
begin
set @NotifSeqArFormatado = '0' + @NotifSeqArFormatado
end
set @NotifSeqArFormatado = 'RJ' + @NotifSeqArFormatado + 'BR'
insert into @tb_multa values (
@Uid, @ViewMode, @OrgAutuador, @CodMunicipio, @CodMunicipioDesc, @CodMunicipioUf, @PropNome, @PropEnder, @PropNumero, @PropComplemento, @PropCep, @PropMunic, @PropMunicUf, @NomeCondutor, @CNHCondutor,
@InfracaoData, @NotifDtImpr, dbo.FCFormatAITNotif(@Ser, @Fai, @Ait, 0), @Placa, @PlacaUf, @PlacaMunDesc,
@Marca, @Modelo, @Especie, @Tipo, @Categoria,
@CodRenainf, @LocalInfracaoADesc, @LocalInfracaoANum, @LocalInfracaoAPos, @LocalInfracaoBDesc, @ValorReal,
@DescricaoEnq, @Enq, @BaseLegal, @ClassificacaoEnq, @PontosEnq, @VelocidadeReg, @VelocidadeMed, @VelocidadeCon,
@radarequip, @radarmarca, @radarmodelo, @DataAfericaoEquip, @Observacao, @DtLimiteRecurso, @NotifSeqAr, @NotifSeqArFormatado, @NotifSeqImpr,
dbo.FCFormatLote(@LoteNum, @LoteAno), @AgCod, @Contrato, @TotalImg, @ImgCartaSeedRL, @ImgNameA, @ImgNameB, @SegundaVia,
@CodigoDescr, @CodigoInfr, @NotifDtPost, @Segundos, @PermDecibel, @AferDecibel, @Consdecibel)
set @Uid = @Uid + 1
if @SegundaVia = 1
begin
-- grava histórico de impressões da segunda via
begin transaction
insert into tb_segundavia_hist (aiip_ser, aiip_fai, aiip_num, aiip_num_dv, placa, dt_impr, status, id_user) values (@AiipSer, @AiipFai, @AiipNum, @AiipNumDv, @Placa, getdate(), 0, @IDUser)
commit transaction
end
fetch next from cur_multa into @Ser, @Fai, @Ait, @NumDV, @Placa, @NotifDtImpr, @NotifSeqAr, @NotifDtPost, @Enq, @NotifSeqImpr
end
if @GetType = 2 -- imprimi o lote inteiro
begin
begin transaction
update tb_lotes set user_impr = @IDUser, dt_impr = getdate() where num = @LoteNum and ano = @LoteAno and dt_impr is null
commit transaction
end
close cur_multa
deallocate cur_multa
set nocount off
select * from @tb_multa order by numimpr
endIf anyone show me any ideas. Thanks.
|
IP Logged