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 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...
> 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...
>> 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
> 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...
>> 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..
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 in message
> news:%230invczaHHA.960@.TK2MSFTNGP03.phx.gbl...
>> 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 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...
>> 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..
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