Data Type Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Data Type Problem

Author

Topic

sanjay5219
Posting Yak Master

240 Posts

Posted - 2010-05-26 : 07:51:42

Dear All,

I have created CDO Mail Procedure and it is working fine but when data is more than 8000 character it shows only half of the data.

declare @msgbdy as varchar(8000)
declare @msgbdyQC as varchar(8000)

I am using SQl 2000

Thanks,

Sanjay

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-26 : 07:53:35

What is "CDO Mail Procedure"?
All we can see is
declare @msgbdy as varchar(8000)
declare @msgbdyQC as varchar(8000)

So how should we understand the problem?

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2010-05-26 : 07:54:08

You will have to change the datatypes to TEXT.

PBUH

sanjay5219
Posting Yak Master

240 Posts

Posted - 2010-05-26 : 07:57:19

I am not sharing full code because it is too big and i have tried TEXT and getting this error
Server: Msg 2739, Level 16, State 1, Procedure MAPS_AUTO_MAIl_ATT_REPORT, Line 3
The text, ntext, and image data types are invalid for local variables.

Sachin.Nand

2937 Posts

Posted - 2010-05-26 : 08:05:17

Oh sorry completly overlooked thats it's a variable.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-05-26 : 08:08:33

If your text value is longer than 8000, you could use several varchar(8000) parameters and concatenate them together.With SQL 2005 you could had declared varchar(max) which had a maximum size of 2GB.

PBUH

sanjay5219
Posting Yak Master

240 Posts

Posted - 2010-05-26 : 08:16:09

I am not getting you. I am sending my code please see if you can help me
Alter procedure [dbo].[MAPS_AUTO_MAIl_ATT_REPORT]
as
declare @Team_Lead_Name as varchar(240)
declare @Total_Employee varchar(10)
declare @Present as varchar(10)
declare @Sch_Leave as varchar(10)
declare @UnSch_Leave as varchar(10)
declare @half_Day as varchar(10)
declare @msg as varchar(500)
declare @msgbdy as varchar(15000)
declare @msgbdyQC as varchar(8000)
declare @MailToList as varchar(1000)
declare @MailCcList as varchar(1000)
set @msgbdy = ''
declare @TTotal_Employee as int
declare @TPresent as int
declare @TSch_Leave as int
declare @TUnSch_Leave as int
declare @Thalf_Day as int

set @msg='Test Mail : Attendance Summary for ' + datename(d,getdate()) + '-' + datename(mm,getdate())+ '-' + datename(yy,getdate())
--delete from HTMT_DB_Temp_Mail_Data
set @TTotal_Employee = 0
set @TPresent = 0
set @TSch_Leave = 0
set @TUnSch_Leave = 0
set @Thalf_Day = 0
DECLARE c1 CURSOR FOR
Select TEam_Lead_Name,Sum(Total_Employee),sum(Present),sum(sch_leave),sum(unsch_Leave),sum(half_day) from MAIL_ATT_REPORT group by TEam_Lead_Name Order by TEam_Lead_Name
OPEN c1
FETCH NEXT FROM c1
INTO @Team_Lead_Name,@Total_Employee,@Present,@Sch_Leave,@UnSch_Leave,@half_Day
set @msgbdy = '<table border=2 bordercolor=gray><tr>
<th >Team Lead</th>
<th >Total Employee</th>
<th >Present</th>
<th >Schedule Leave</th>
<th >Un Schedule Leave</th>
<th >Half Day</th>
</tr>'
WHILE @@FETCH_STATUS = 0
BEGIN
set @TTotal_Employee = @TTotal_Employee + cast(@Total_Employee as int)
set @TPresent = @TPresent + cast(@Present as int)
set @TSch_Leave = @TSch_Leave + cast(@Sch_Leave as int)
set @TUnSch_Leave = @TUnSch_Leave + cast(@UnSch_Leave as int)
set @Thalf_Day = @Thalf_Day + cast(@half_Day as int)

set @msgbdy = @msgbdy + '<tr>
<td ALIGN=left>' + @Team_Lead_Name + '</td>
<td align=right>' + @Total_Employee + '</td>
<td align=right>' + @Present + '</td>
<td align=right>' + @Sch_Leave + '</td>
<td align=right>' + @UnSch_Leave + '</td>
<td align=right>' + @half_Day + '</td>
</tr>'
FETCH NEXT FROM c1
INTO @Team_Lead_Name,@Total_Employee,@Present,@Sch_Leave,@UnSch_Leave,@half_Day
END
set @msgbdy = @msgbdy + '<tr>
<th bgcolor=tan align=left colspan=2>Total</th>
<th align=right bgcolor=tan>' + cast(@TTotal_Employee as varchar(10)) + '</th>
<th align=right bgcolor=tan>' + cast(@TPresent as varchar(10)) + '</th>
<th align=right bgcolor=tan>' + cast(@TSch_Leave as varchar(10)) + '</th>
<th align=right bgcolor=tan>' + cast(@TUnSch_Leave as varchar(10)) + '</th>
<th align=right bgcolor=tan>' + cast(@Thalf_Day as varchar(10)) + '</th>
</tr>'
set @msgbdy = @msgbdy + '</table>'
Print @msgbdy
exec sp_send_cdosysmail TEst,'abc@abc.com','abc@abc.com',@msg ,@msgbdy,'1'
CLOSE c1
DEALLOCATE c1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources