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|||> Since I can't reuse this procedure in SQL 2005, I am testing the
You should be able to use xp_smtp in 2005, as long as it is 32 bit SQL Serve
r.
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...
> 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
>
>|||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..
Well, you can't use a 32 bit DLL in 64 bit code. See Russell's reply to Fred
's post for suggestion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"msnews.microsoft.com" <spam@.spam.com> wrote in message
news:euuEqS8aHHA.1508@.TK2MSFTNGP06.phx.gbl...
> Thanks, But it is a x64 bit SQL Server..
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%230invczaHHA.960@.TK2MSFTNGP03.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/SQLSe...=2644
98
A