SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 cannot display many records in html table using SP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anilr499
Starting Member

18 Posts

Posted - 01/16/2013 :  09:34:03  Show Profile  Reply with Quote
I'm trying to dynamically create an HTML email using stored procedures. The actual source for the HTML is too large to fit in a varchar(8000) field..when i execute my SP it is not displaying the data in table which contains more than aprroximately 164rows...
how should i solve my problem...i have maximum of 700 rows

here is my SP:

USE [Tea]
GO
/****** Object: StoredProcedure [dbo].[SP_TS_USAGE_INPUT] Script Date: 01/10/2013 00:50:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



--Created By Anil on 15 jan 2013

ALTER PROCEDURE [dbo].[SP_TS_USAGE_INPUT]
(
@BGPID varchar(50),@TOO varchar(50),@VIA varchar(500)
)
AS
DECLARE @TO VARCHAR(8000), @CC VARCHAR(8000), @BCC VARCHAR(8000), @SUBJECT VARCHAR(500), @MESSAGE VARCHAR(max), @MESSAGE2 VARCHAR(max), @cmd varchar(50)
DECLARE @CR CHAR(1), @COUNT int, @prefix varchar(50), @status varchar(20)
Declare @value_a varchar(2000), @value_b varchar(2000), @value_c varchar(2000)
Declare @admin varchar(255), @webroot varchar(500), @adminmail varchar(255), @system varchar(255)
Declare @to_rcpt varchar(2000), @cc_rcpt varchar(2000), @bcc_rcpt varchar(2000)
Declare @TotalUser VARCHAR(3), @ActiveUser VARCHAR(3), @InactiveUser VARCHAR(3), @NonUser VARCHAR(3),@Name varchar(50)
DECLARE @emp_name varchar(50), @aud_date varchar(11), @c_aud_date varchar(10), @ndiff int, @Userstatus varchar(15), @c_department varchar(100)

-- GET PARAMETERS from TSSQLREMINDER
exec sp_get_parameters 'ADMIN', 'GENERAL', @value_a output, @value_b output, @value_c output
set @admin = @value_a
set @adminmail = @value_b

exec sp_get_parameters 'MAIL', 'GENERAL', @value_a output, @value_b output, @value_c output
set @prefix = @value_a

select @to_rcpt = coalesce(par_value_a, ''), @cc_rcpt = coalesce(par_value_b, ''), @bcc_rcpt = coalesce(par_value_b, '')
from tsparameters
where par_id = 'TSSUPPORT'

SET DATEFIRST 7
SET @CR = CHAR(10)



select distinct t3.emp_name, B2.BGP_DESC AS c_department,convert(varchar(11), t1.aud_date, 106) as aud_date, t1.aud_date as c_aud_date,
datediff(d, t1.aud_date, getdate()) as ndiff, ' ' as UserStatus
into #temp from EMPLOYEE t3
LEFT JOIN EMPBIZGROUP B1 ON B1.EMP_ID = t3.EMP_ID
LEFT JOIN BIZGROUP B2 ON B2.BGP_ID = B1.BGP_ID
left join
(
select max(aud_date) as aud_date, usr_id
from tsaudit
group by usr_id
) as t1 on t1.USR_ID = t3.EMP_ALIAS
left join tsaudit t2 on t2.usr_id = t3.EMP_ALIAS
where B2.BGP_ID in
((SELECT BGP_ID FROM BIZGROUP WHERE BGP_PARENT= @BGPID) union all
(SELECT BGP_ID FROM BIZGROUP WHERE BGP_PARENT IN( SELECT BGP_ID FROM BIZGROUP WHERE BGP_PARENT= @BGPID))
union all (SELECT BGP_ID FROM BIZGROUP WHERE BGP_PARENT IN (SELECT BGP_ID FROM BIZGROUP WHERE BGP_PARENT IN( SELECT BGP_ID FROM BIZGROUP WHERE BGP_PARENT= @BGPID))))

AND (t3.emp_ter_date is null and t3.EMP_ID not in ('EMP000001','1542'))
or (t3.emp_ter_date is not null and t3.EMP_REHIRED_DATE is not null and (t3.EMP_REHIRED_DATE >= t3.emp_ter_date)and t3.EMP_ID not in ('EMP000001','1542') )
order by ndiff

update #temp
set UserStatus = 'Inactive User'
where ndiff > 7

update #temp
set UserStatus = 'Active User'
where ndiff <= 7

update #temp
set UserStatus = 'Non User'
where ndiff is null

SELECT @TotalUser = COUNT(*) FROM #temp
SELECT @ActiveUser = COUNT(*) FROM #temp where UserStatus = 'Active User'
SELECT @InactiveUser = COUNT(*) FROM #temp where UserStatus = 'Inactive User'
SELECT @NonUser = COUNT(*) FROM #temp where UserStatus = 'Non User'
SELECT @Name = BGP_DESC FROM BIZGROUP where BGP_ID = @BGPID

SET @SUBJECT = 'TeamSYNthesis Usage'
SET @MESSAGE= @CR + @CR
SET @MESSAGE= @MESSAGE + 'TeamSYNthesis usage as at ' + CONVERT(varchar(11), getdate(), 106) + ' of ' + @Name + @CR + @CR
SET @MESSAGE = @MESSAGE +
'<br/><br/>
<table border="1">
<tr align="center" style="background-color:Gray;color:white;">
<td>Total Number<br/> Of User </td>
<td><nobr>Active Users</nobr></td>
<td><nobr>Inactive Users</nobr></td>
<td><nobr>Non Users</nobr></td>
</tr>
<tr align="center">
<td>'+@TotalUser +' (100%)</td>
<td>'+@ActiveUser + ' (' + CASE WHEN @ActiveUser = '0' THEN '0' ELSE
convert(varchar,cast(round(CONVERT(INT,replace(@ActiveUser,'','0'))*100.0/CONVERT(INT, replace(@TotalUser,'','0')),1) as numeric(10,1))) END +'%)</td>
<td>'+@InactiveUser+ ' ('+ CASE WHEN @InactiveUser = '0' THEN '0' ELSE
convert(varchar,cast(round(CONVERT(INT,replace(@InactiveUser,'','0'))*100.0/CONVERT(INT, replace(@TotalUser,'','0')),1) as numeric(10,1)))END+'%)</td>
<td>'+@NonUser + ' (' + CASE WHEN @NonUser = '0' THEN '0' ELSE
convert(varchar,cast(round(CONVERT(INT,replace(@NonUser,'','0'))*100.0/CONVERT(INT, replace(@TotalUser,'','0')),1) as numeric(10,1)))END +'%)</td>
</tr>
</table>'

SET @MESSAGE = @MESSAGE +
'<br/>
<table border="1">
<tr align="center" style="background-color:Gray;color:white;">
<td> No. </td>
<td style="width:15%;"><nobr>Employee Name</nobr> </td>
<td><nobr>Department</nobr></td>
<td><nobr>Last Access Date</nobr></td>
<td><nobr>Day Difference </nobr> </td>
<td><nobr>Status</nobr> </td>
</tr>'



SET NOCOUNT ON


DECLARE MYCURSOR CURSOR FOR

select * from #temp
SET @MESSAGE2 = ''
OPEN MYCURSOR

FETCH NEXT FROM MYCURSOR
INTO @emp_name, @c_department, @aud_date, @c_aud_date, @ndiff, @Userstatus

SET @COUNT = 1

WHILE @@FETCH_STATUS=0
BEGIN
SET @MESSAGE2 = @MESSAGE2 +
'
<tr>
<td align="center"> '+convert(varchar,@COUNT)+' </td>
<td align="left"> '+@emp_name+' </td>
<td align="center"> '+isnull(@c_department,'-')+' </td>
<td align="center"> '+isnull(@aud_date,'-') +' </td>
<td align="center"> '+convert(varchar,isnull(@ndiff,'0')) +' </td>
<td align="center"> '+@Userstatus +' </td>
</tr>'

SET @COUNT = @COUNT + 1
FETCH NEXT FROM MYCURSOR
INTO @emp_name, @c_department, @aud_date, @c_aud_date, @ndiff , @Userstatus
END


CLOSE MYCURSOR
DEALLOCATE MYCURSOR



SET @MESSAGE2 = @MESSAGE2 +'</table><br/>'+ @CR + @CR +
'Kindly contact ' + @admin + ' if you need any assistance.'

SET @MESSAGE2= @MESSAGE2 + @CR+@CR+ 'Thank you,'+@CR+ @admin + @CR+
'Email : ' + @adminmail


SET @MESSAGE = @MESSAGE + @MESSAGE2


--print @MESSAGE


SET @TO = @TOO
SET @CC = @VIA


IF @TO <> ''
BEGIN
--EXEC sp_ts_sendmail @RECIPIENTS=@TO, @COPY_RECIPIENTS=@CC, @BLIND_COPY_RECIPIENTS=@BCC, @QUERY=@cmd, @SUBJECT=@SUBJECT
insert into JOBMAIL_HTML (JOM_SUBJECT, JOM_MESSAGE, JOM_TO, JOM_CC, JOM_BCC, JOM_ATTACH, JOM_DATE_CREATED, JOM_STATUS) values
(@SUBJECT, @MESSAGE, @TO, @CC , null, null, GETDATE(), 'NEW')
END


drop table #temp





Please will somebody help me
Thank you
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000