Hoping I'm just over looking something. I have the query below in SSMS and it works just fine. (There are additional Insert and Update sections that were removed in this example for clarity.) When I try to add a Dataset in SQL Server BIDS, the @season parameter isn't available. When I alter the query and remove the insert statement, the parameter becomes available. I don't do a lot with SSRS reports normally but trying to move that way.
Thanks in advance
USE [impresario]GO/****** Object: StoredProcedure [dbo].[LP_PAF_PERF_PROOF_1] Script Date: 06/05/2018 15:31:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[LP_PAF_PERF_PROOF_1] @season int = nullASBEGIN SET NOCOUNT ON; create table #results(perf_no int,perf_name varchar(100),perf_code varchar(20),perf_dt datetime)
insert #results (perf_no, perf_name, perf_code, perf_dt) select p.perf_no as i_perf_no, i2.description as i_perf_name, p.perf_code as i_perf_code, p.perf_dt as i_perf_dt--, from T_PERF pjoin TR_SEASON s on p.season = s.idjoin T_INVENTORY i on p.prod_season_no = i.inv_nojoin T_INVENTORY i2 on p.perf_no = i2.inv_nojoin TR_PERF_STATUS ps on p.perf_status = ps.idwhere p.season = @seasonorder by p.perf_dt
select *from #resultsorder by perf_no
return
END
Hello Joel,
Did you provide execute permission on LP_PAF_PERF_PROOF_1 procedure to impuser of tessitura in SSMS.
Thanks,
Malek