I can not get Cyrstal Reports in Visual Studio .NET to see the output of a stored procedure containing dynamic sql. It runs fine in query analyzer but when adding a new Crystal Report in Visual Studion the wizard I select the stored procedure under data, click next and no fields are visible.
How do I get Crystal to see the output generated by thy dynamic sql executed by sp_executesql?
Thank you so much!!!
Here is the stored procedure. Is I uncomment the test select after the dynamic sql it will show up.
if exists (select name
from sysobjects
where name = N'sp_rpt_EmpHrsAvailToActualHrsByMonth' and
type = 'P')
drop procedure sp_rpt_EmpHrsAvailToActualHrsByMonth
set quoted_identifier on
go
set ansi_nulls on
go
set nocount on
go
create procedure sp_rpt_EmpHrsAvailToActualHrsByMonth @startyearpart int,
@startmonthpart int,
@endyearpart int,
@endmonthpart int,
@ccntrs varchar(200),
@error_message nvarchar(400) = null out
as
/**************************************************************************
procedure: sp_rpt_EmpHrsAvailToActualHrsByMonth
script: sp_rpt_EmpHrsAvailToActualHrsByMonth.sql
purpouse: This stored procedure returns worked employee hours
and available employee hours by month.
called by: [enter web page or other calling program names here]
params: @startyearpart int Year part of start date
@startmonthpart int Month part of start date
@endyearpart int Year part of end date
@endmonthpart int Month part of date end date
@ccntrs varchar(100) Comma delimted list of cost centers
'55100,55200,55300' or '55200'
@error_message nvarchar(400) Descriptive error message
returns: return = 0 All OK
<> 0 Error occured
syntax:
declare @error_code int
declare @error_message nvarchar(400)
exec @error_code =
sp_rpt_EmpHrsAvailToActualHrsByMonth @startyearpart = 2003,
@startmonthpart = 8,
@endyearpart = 2004,
@endmonthpart = 7,
@ccntrs = '55200,55300',
@error_message = @error_message out
print @error_code
print @error_message
OR
exec
sp_rpt_EmpHrsAvailToActualHrsByMonth @startyearpart = 2003,
@startmonthpart = 8,
@endyearpart = 2004,
@endmonthpart = 7,
@ccntrs = '55200,55300'
history: 20041004 Procedure created by Craig Faulkner, Sr. Systems Analyst.
20041005
notes:
**************************************************************************/
declare @error_code int
declare @return_code int
declare @procedure_name nvarchar(100)
declare @sqlstring nvarchar(2000)
declare @dtend varchar(20)
declare @dtbegin varchar(20)
select @procedure_name = 'sp_rpt_Task_For_Mgr_AndOr_CstCtr'
-- One Year
--set @dtbegin = convert(smalldatetime, ltrim(str(year(@dtend-350)))+'-'+
-- ltrim(str(month(@dtend-350)))+'-'+'01')
select @dtbegin = ltrim(str(yearpart))+'-'+ltrim(str(monthpart))+'-'+ltrim(str(lastdayofmonth))
from dbo.ldaytypepermonth
where yearpart = @startyearpart and
monthpart = @startmonthpart
select @dtend = ltrim(str(yearpart))+'-'+ltrim(str(monthpart))+'-'+ltrim(str(lastdayofmonth))
from dbo.ldaytypepermonth
where yearpart = @endyearpart and
monthpart = @endmonthpart
set @sqlstring =
'select
max(year(q1.ddate)) as yearpart,
max(month(q1.ddate)) as monthpart,
convert(int, round(sum(q1.hours),0)) as TtlHrs,
convert(int, round((max(q2.weekdays) * 7.5) *
(count(distinct q1.emplid)),0)) as TtlHrsAvail,
convert(int, round(sum(q1.hours),0)) -
(convert(int, round((max(q2.weekdays) * 7.5) *
(count(distinct q1.emplid)),0))) as TtlHrsOT,
count(distinct q1.emplid) as Emp,
max(q2.weekdays) as WkDayInMnth,
convert(int,round(((sum(q1.hours) /
count(distinct q1.emplid)) /
max(q2.weekdays) * 5),2)) as AvgWkPerFTE
from (
select
tt.ddate as ddate,
tt.hours as hours,
tt.emplid as emplid
from dbo.ttimetrk tt
inner join dbo.tcostctr tc
on tt.cstctrid = tc.cstctrid
where tc.GLCstCtr in ('+@ccntrs+') and
tt.ddate between '+char(39)+@dtbegin+char(39)+' and '+char(39)+@dtend+char(39)+') q1
inner join (
select yearpart, monthpart, weekdays
from dbo.ldaytypepermonth) q2
on year(q1.ddate) = q2.yearpart and
month(q1.ddate) = q2.monthpart
group by ltrim(str(year(q1.ddate)))+right('+char(39)+'0'+char(39)+'+ltrim(str(month(q1.ddate))),2)
order by ltrim(str(year(q1.ddate)))+right('+char(39)+'0'+char(39)+'+ltrim(str(month(q1.ddate))),2)'
exec sp_executesql @sqlstring --WILL THIS TRAP A SELECT ERROR FROM sp_executesql ? ? ?
--TEST TEST TEST TEST TEST TEST
-- select yearpart as yearp, monthpart as monthp, lastdayofmonth, weekdays, weekenddays, 'Test 1' as test
-- from dbo.ldaytypepermonth
--select * from ldaytypepermonth
set @error_code = @@error
if @error_code <> 0
begin
--set @error_message = 'SQL Error while selecting ttimetrk, temployees, tsubtask, ttypework.'
goto error_close
end
goto end_proc
/**************************************************************************
* ERROR_CLOSE - process error
**************************************************************************/
error_close:
select @error_message = @error_message +
' Stored Procedure: '+@@servername+'.'+db_name()+'..'+@procedure_name+
' User: '+suser_sname()
raiserror (@error_message, 16, 1)
goto end_proc
/**************************************************************************
* END STORED PROCEDURE
**************************************************************************/
end_proc:
return @error_code
---------------------------------------------------------------------------
go
set quoted_identifier off
go
set ansi_nulls off
go
set nocount off
go
