SQL Help

I have revised a SQL procedure that appears to work fine when manually running the code, but I cannot get this to work from within Tessitura.  The only modification I made to the previous report setup screen was to add perf_date_start and perf_date_end parameters.

 

Here is the SQL Code:

USE [impresario]
GO
/****** Object:  StoredProcedure [dbo].[LP_TCSOM_SEATING_BOOK_RPT_BABY_HARMONY]    Script Date: 01/15/2015 11:29:10 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

/*
lp_TCSOM_SEATING_BOOK_RPT_BABY_HARMONY
@season_no = 393,
@perf_no_str = null,
@section_str = null,
@gen_public = 1,
@Perf_dt_start = '1-1-2015',
@Perf_dt_end = '1-31-2015'



*/


ALTER           PROCEDURE [dbo].[LP_TCSOM_SEATING_BOOK_RPT_BABY_HARMONY](
    @season_no int = 0,
    @perf_no_str varchar(255)=NULL,
    @section_str varchar(255)=NULL,
    @gen_public int = 1,
    @Perf_dt_start datetime,
    @Perf_dt_end datetime
   
   
)
 
AS
Set NoCount On  -- added auto CWR 8/9/2001

 
/*************************************************************************************************************
New Procedure 9/22/2000 by CWR based on SQL by S. Dorey for Seating Book Report
Shows only seats that have been sold
 
Called by d_seating_book_rpt
 
gen_public parm can be 1 (yes) or 2 (no)
 
*************************************************************************************************************/
 

create table #tperf (perf_no int null)

if @perf_no_str is null or ISNULL(Datalength(ltrim(@perf_no_str)),0)=0
insert into #tperf
select    perf_no
from    t_perf
where    season=@season_no


Else

    insert into #tperf
    select perf_no
    from t_perf
    where charindex(','+convert(varchar,perf_no)+',',','+@perf_no_str+',')>0
           
create table #tsections(id int null, description varchar(30) null)
 
IF @section_str is null or ISNULL(Datalength(ltrim(@section_str)),0) = 0
    Insert    into #tsections
    Select    id, description
    From    tr_section
Else
    Insert     into #tsections
    Select     id, description
    from     tr_section
        where     charindex(',' + convert(varchar,id) + ',' , ',' + @section_str + ',') > 0
 
Select    distinct    s.seat_no,
                    o.customer_no,
                    name = CONVERT(CHAR(50), CASE WHEN o.customer_no = 0 THEN '(General Public)'
                            ELSE RTRIM(c.lname) + CASE WHEN ISNULL(c.fname, ' ') = ' ' THEN '' ELSE ', ' + c.fname END END),
                    sort_name = CASE WHEN o.customer_no = 0 THEN '(General Public)'
                            --ELSE CONVERT(CHAR(10),c.lname) + CONVERT(CHAR(5), c.fname) END,
                            ELSE c.sort_name END,
                        --remmed out 8/26/13 to use new sort_name from t_customer

                    a.street1,
                    a.street2,
                    a.city,
                    a.state,
                    a.postal_code,
                    '('+substring(p1.phone,1,3)+')'+substring(p1.phone,4,3)+'-'+substring(p1.phone,7,10) as phone1,

    sli.order_no,
    section = sc.description,
    s.seat_row,
    s.seat_num,
    o.notes,
    y.description as price_type_description,
    --sp.notes as Special_request,
    status = CASE
         WHEN sli.pkg_no > 0 THEN (slis.description + ' ' + '(Package)')
         ELSE (slis.description + ' ' + '(Single)')
             END,
    '('+substring(p2.phone,1,3)+')'+substring(p2.phone,4,3)+'-'+substring(p2.phone,7,10) as phone2,
    e.address as eaddress,
    pf.perf_dt,
    pf.perf_code,
    i.description as perf_desc
--Into #trequest
From    tx_perf_seat ps (NOLOCK)
    JOIN t_sub_lineitem sli (NOLOCK) ON ps.sli_no = sli.sli_no
    join t_order o (NOLOCK) ON o.order_no = sli.order_no
    join t_seat s (NOLOCK) ON s.seat_no = sli.seat_no
    join t_customer c (NOLOCK) ON c.customer_no = o.customer_no
    join t_address a on a.customer_no = c.customer_no --
    left outer join (    select p.customer_no,
                        max(phone) as phone
                        from t_phone p join t_address a on p.address_no=a.address_no and p.customer_no=a.customer_no
                        where p.type=1 and a.primary_ind='Y'
                        group by p.customer_no) p1 on p1.customer_no = c.customer_no
    left outer join (    select p.customer_no,
                        max(phone) as phone
                        from t_phone p join t_address a on p.address_no=a.address_no and p.customer_no=a.customer_no
                        where p.type=2 and a.primary_ind='Y'
                        group by p.customer_no) p2 on p2.customer_no = c.customer_no
    join tr_seat_status ss (NOLOCK) ON ss.id = sli.sli_status
    join #tsections sc (NOLOCK) ON sc.id = s.section
    join tr_sli_status slis (NOLOCK) ON slis.id = sli.sli_status
    join tr_price_type y on sli.price_type = y.id
    join t_lineitem l on o.order_no = l.order_no
    join t_sub_lineitem sl on l.li_seq_no = sl.li_seq_no
    join t_special_req sp on l.li_seq_no = sl.li_seq_no
    left outer join (select customer_no,max(address) as address from t_eaddress where primary_ind='Y' group by customer_no) e on c.customer_no=e.customer_no
    join #tperf tp on sl.perf_no=tp.perf_no
    join t_perf pf on tp.perf_no=pf.perf_no
    join t_inventory i on pf.perf_no=i.inv_no
   
Where    sli.perf_no = pf.perf_no and ps.perf_no = pf.perf_no
    and ISNULL(ps.sli_no,0) > 0
    and a.primary_ind = 'Y' --
    --and p.type <2
    and i.description like 'Baby Harmony%'
    and pf.perf_dt between @Perf_dt_start and @Perf_dt_end
    and sp.notes is not null
    and sp.notes != 'Test'
    and sp.notes != 'Test this line only'
    and sp.notes != ' '
    and o.customer_no >= CASE WHEN @gen_public = 1 THEN 0 ELSE 1 END

--select distinct seat_no, customer_no, name, sort_name, street1,
--street2, city, state, postal_code, order_no, section, seat_row, seat_num,
--notes, price_type_description, Special_request, status

--from #trequest

Return

Here are the results when I run it with the test parameter info at the top:

 

The results return 8 rows.

However, when I run the report through Tessitura I get this error:

Normal 0 false false false EN-US X-NONE X-NONE

Windowmenu: w_report_psr

Object: w_report_psr

Objectevent: open

Line: 24

Error Code: 2

Error Text: Null object reference at line 24 in open event of object w_report_psr.

 

Here is what the report setup looks like:

 

 

Anyone know why I am getting the null reference error?

Thanks in advance!

[edited by: Penny Tabor at 4:25 PM (GMT -6) on 15 Jan 2015] Sorry, guess the screenshots won't work