database mail Vs. xp_smtp_sendmail

Hi,
we are in the process of upgrading SQL Server 2000 to 2005,
Now, a lot of emails are sent within stored procedures using
xp_smtp_sendmail.
Now this procedure is quite nice because it lets the caller
define diffrent From names and ReplyTo depending on certain conditions.
Since I can't reuse this procedure in SQL 2005, I am testing the
Database Mail using the sp_send_dbmail stored procedure along with
some profiles I created. It works great, but...
My problem here is that sp_send_dbmail doesn't let you dynamically change
the FROM and REPLYTO parameters, it uses the ones on the profile
sp_send_dbmail
recieves in parameter. This leaves me with a dilemna because the from and
replyTo
adresses possibility are too great (that is why it was made dynamic in our
SQL 2000 using xp_smtp_sendmail)
How should I proceed if I do not want to create 1 profile per possibility?
Is there another way or another SMTP send mail procedure or technique I
could use in SQL 2005
that will allow me to do the same thing as in XP_smtp_SendMail ?
Ideas anyone ?
Thanks
> Since I can't reuse this procedure in SQL 2005
Why not? As long as you are on 32-bit, you can install the DLL on 2005 the
same way you did on 2000.
A
|||Thanks, But it is a x64 bit SQL Server..
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%230invczaHHA.960@.TK2MSFTNGP03.phx.gbl...
> You should be able to use xp_smtp in 2005, as long as it is 32 bit SQL
> Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "msnews.microsoft.com" <spam@.spam.com> wrote in message
> news:e8h4gUzaHHA.5020@.TK2MSFTNGP05.phx.gbl...
>
|||> Thanks, But it is a x64 bit SQL Server..
In that case, you could learn the format and write a stored procedure that
creates a properly-formatted text file with a .msg extension and drops it
into the pickup folder of a local SMTP server (which doesn't necessarily
have to live on the same server, so could be 32-bit). This would allow you
to have full control over FROM/REPLYTO etc.
Another option is to set up a 32-bit edition of Express or Workgroup on
another server (even a web server), and route messages through it. As long
as the network path is reliable, not a huge difference between 'EXEC
master.dbo.xp_smtp_sendmail' and 'EXEC
linked_server_name.master.dbo.xp_smtp_sendmail'...
A
|||BTW, I submitted an enhancement request about this issue. You can vote on
it if you have a passport and feel strongly enough about it to have it
addressed in a future version of SQL Server:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=264498
A