No 'Database Email' available on DiscountASP SQL Server 2005?

Am I correct, in checking the forum, that DASP does not allow emailfrom SQL server? Major bummer if not.

How does one get emails then from certain database scheduled processes?!

Thanks

 

Hi,
Give me some details on what you need and I'll whip up some tips or code for you.
With ASP.NET 3.5, LINQand SQL Server 2005 you can do much of what the SQL Server mail component offers.
Salute,
Mark

 

Here is the SP I was setting up to run every day after12 midnight using scheduler




set ANSI_NULLS ON


set QUOTED_IDENTIFIER ON


GO


ALTER PROCEDURE [dbo].[orientation_reminders_sp]


as


BEGIN


SET NOCOUNT ON


DECLARE @period_count int


DECLARE @period_id int


DECLARE @Start_date smalldatetime


DECLARE @reminder_days int


----SELECT @period_count = (SELECT COUNT(*) FROM dbo.orientation_time_period)


--print @period_count





SET @period_count = (SELECT min(period_order) FROM dbo.orientation_time_period)


WHILE @period_count IS NOT NULL


BEGIN


set @period_id=(SELECT period_id FROM dbo.orientation_time_period WHERE period_order = @period_count)


set @reminder_days = (SELECT days_to_reminder FROM dbo.orientation_time_period WHERE period_order = @period_count)


--set @Start_date = (SELECT work_start_date FROM dbo.orientation_user_profile WHERE userid='


print 'Period Order:'+convert(varchar(4),@period_count)+' Period Id:'+convert(varchar(4),@period_id)+' Reminder days:'+convert(varchar(4),@reminder_days)


SELECT DISTINCT LoweredEmail


,ocui.completed


,oup.work_start_date


,otp.days_to_reminder


,DATEDIFF(day,oup.work_start_date,GetDate()) as Reminder_in


FROM dbo.orientation_checklist_user_info ocui


,dbo.orientation_user_profile oup


,dbo.aspnet_Membership am


,dbo.orientation_time_period otp


WHERE ocui.userid = oup.userid


AND ocui.userid = am.userid


AND ocui.period_id = otp.period_id


AND ocui.completed = 0


AND ocui.period_id = @period_id


AND (DATEDIFF(day,oup.work_start_date,GetDate()) = @reminder_days)


--print DATEDIFF(day,'05/16/2008',GetDate())


SET @period_count = (SELECT min(period_order)


FROM dbo.orientation_time_period


WHERE period_order > @period_count )


END


-- EXEC master..xp_smtp_sendmail


-- @TO = '[email protected]',


-- @BCC = @BCCList,


-- @from = '[email protected]',


-- @subject = 'Your orientation reminder',


-- @message = 'This is an automated reminder that you need to complete'+@period+' before '+@date,


-- @server = 'smtp.yourdomain.com'


END

 

Yes you can create an ASP.NET page and schedule to hit it from your Control Panel.

As forSQL Server mapi. . .
You will find Dedicated Server hosts that allow you to activate the SQL Server e-mail component, however...
Any Shared Host allowing that would be looking for trouble.
Use Google and search for all the problems with running the SQL Server mail component.
I'm a longtime fan of SQL Server but even at some of the large companies I've coded for that component was not allowed.
http://support.microsoft.com/kb/311231

 

With respect Wisemx,

I am not referring to SQLMail.

Do not mean to be rude or disrespectfulas you are very helpfull in this forum but do you actually work for DASP or are you their board moderator?

I am referring to 'Database mail' which is much more reliable and efficient and is the new and preferred way of emailing in SQL server 2005.

So why isnt DASP running that?

Cheers

 

Hi,
I don't work for DASP but I am here to help and I do work hand in hand with them as help is needed.
Especially with Video tutorials now.
If you want a direct answer for this SQL Mail question open a support ticket and mention this forum thread.
Salute,
Mark
http://blogcastrepository.com/blogs/wisemx/default.aspx

 

OK...One thing you could use isthe new ListView control to build a page with the data you need for that schedule.
Your Procedure could make use of a temp table that you truncate then populate before the ListView builds the page.
Finally your page sends the mail message from your server.
Would take the same amount of time as what you wanted and it will work just fine as a scheduled event.
Salute,
Mark

 

Sorry its late here but are you saying I would use an aspx page to get the data then email those particular users?
And how on earth would you schedule that?

And why does DASP not support the best email afcility to come to SQl server?

Thanks

Post Edited (Kiwi Media Productions) : 6/3/2008 11:34:58 AM GMT

 

I suppose the posibility to down the Sql Server sending a multiple messages per second and a long period ;-)