Change "from" portion of the email when calling sp_send_dbmail?

I want to be able to call sp_send_dbmail and be able to change the "from" portion of the email (the email and the display name) programmatically/data-driven as a sproc constructs and sends the email.

Is this possible?
I believe the only way to do this is to use different profiles|||Take a look at xp_smtp_sendmail http://www.sqldev.net/xp/xpsmtp.htm.|||To enhance Euan's reply refer to - http://www.sql-server-performance.com/da_email_functionality.asp & http://www.sqlservercentral.com/columnists/cBunch/introtodatabasemailinsql2005.asp links.|||

Your answer is not very practical when you have hundreds of users.

In the older SQL Server 2000 we used

Code Snippet

exec @.rc = master.dbo.xp_smtp_sendmail

@.FROM = @.in_FROM_ADDRESS,

@.FROM_NAME = @.in_FROM_NAME,

@.TO = @.in_TO_ADDRESS,

@.CC = @.in_CC,

@.BCC = @.in_BCC,

@.priority = @.in_priority,

@.subject = @.in_subject,

@.type = @.in_type,

@.message = @.in_message

Which had the first paramater @.From and the second one was @.From_Name.

Both of which are now gone in the new send dbmail

Code Snippet

exec @.rc = msdb.dbo.sp_send_dbmail

@.profile_name = 'MIS',

@.recipients = @.in_TO_ADDRESS,

@.copy_recipients = @.in_CC,

@.blind_copy_recipients = @.in_BCC,

@.importance = @.in_priority,

@.subject = @.in_subject,

@.body = @.in_message

The resulting emails are coming from whatever you set the Profile name to be. As you would by default set the profile to be generic, something like 'MIS Server Mailer' or whatever your application name is. Hense all the mails now come from this generic user.

This is a problem, and MS should extend the DB Mail to include this rather simple option.

Change "from" portion of the email when calling sp_send_dbmail?

I want to be able to call sp_send_dbmail and be able to change the "from" portion of the email (the email and the display name) programmatically/data-driven as a sproc constructs and sends the email.

Is this possible?
I believe the only way to do this is to use different profiles|||Take a look at xp_smtp_sendmail http://www.sqldev.net/xp/xpsmtp.htm.|||To enhance Euan's reply refer to - http://www.sql-server-performance.com/da_email_functionality.asp & http://www.sqlservercentral.com/columnists/cBunch/introtodatabasemailinsql2005.asp links.|||

Your answer is not very practical when you have hundreds of users.

In the older SQL Server 2000 we used

Code Snippet

exec @.rc = master.dbo.xp_smtp_sendmail

@.FROM = @.in_FROM_ADDRESS,

@.FROM_NAME = @.in_FROM_NAME,

@.TO = @.in_TO_ADDRESS,

@.CC = @.in_CC,

@.BCC = @.in_BCC,

@.priority = @.in_priority,

@.subject = @.in_subject,

@.type = @.in_type,

@.message = @.in_message

Which had the first paramater @.From and the second one was @.From_Name.

Both of which are now gone in the new send dbmail

Code Snippet

exec @.rc = msdb.dbo.sp_send_dbmail

@.profile_name = 'MIS',

@.recipients = @.in_TO_ADDRESS,

@.copy_recipients = @.in_CC,

@.blind_copy_recipients = @.in_BCC,

@.importance = @.in_priority,

@.subject = @.in_subject,

@.body = @.in_message

The resulting emails are coming from whatever you set the Profile name to be. As you would by default set the profile to be generic, something like 'MIS Server Mailer' or whatever your application name is. Hense all the mails now come from this generic user.

This is a problem, and MS should extend the DB Mail to include this rather simple option.

Catching return values of a SP

I have calling a stored procedure that returns two values, and I want to catch these values and to store them into a variable.

Here is a piece of my SP inside SQL Server that shows the returned values:

…
SELECT @.Id = SCOPE_IDENTITY()
SELECT @.Id AS user_id
SELECT 1 AS Value
END
GO

In my aspx page I am trying to call the first value like this:

Dim nID
CmdInsert.Parameters.Add(New SqlParameter("@.RETURN_VALUE", SqlDbType.bigint, 8, "user_id"))
 CmdInsert.Parameters("@.RETURN_VALUE").Direction = ParameterDirection.ReturnValue
CmdInsert.Parameters("@.RETURN_VALUE").Value = nID

And to check if the right value is returned I use:

strConnection.open()
 cmdInsert.ExecuteNonQuery
 'Set the value of a textbox
 ident.text = nID
 strConnection.close()

But now no value appears in the textbox, How can I achieve it? What is wrong?You are sort of combining a few different approaches to solving this problem. Since only one ReturnValue can be returned from a stored procedure and you need 2 values, that approach won't work. And since you only have 2 values, I think that you should use OUTPUT parameters.

The stored procedure would look like this:

CREATE PROCEDURE
 myProcedure
AS
 @.myInput1 varchar(50),
 @.myInput2 varchar(50),
 @.myOutput1 bigint OUTPUT,
 @.myOutput2 bigint OUTPUT
INSERT <etc etc
SET @.myOutput1 = SCOPE_IDENTITY
SET @.myOutput2 = 2

Your aspx page code would look like this:

CmdInsert.Parameters.Add("@.myOutput1", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput1").Direction = ParameterDirection.Output
CmdInsert.Parameters.Add("@.myOutput2", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput2").Direction = ParameterDirection.Output
strConnection.open()
cmdInsert.ExecuteNonQuery
'Set the value of a textbox
ident.text = CmdInsert("@.myOutput1")
strConnection.close()

Terri|||I have followed all your steps, and now this error message appears:

BC30367: Class 'System.Data.SqlClient.SqlCommand' cannot be indexed because it has no default property.

What does it mean?|||Sorry, the line afected is this:

Line 105: ident.text = CmdInsert("@.Id")|||I'm the one who's sorry. The correct syntax for that line is:

ident.text = CmdInsert.Parameters("@.Id").Value

Terri|||i think its something like :

ident.text=convert.toint32(CmdInsert.Parameters("@.Id").Value)

hth|||Good! now runs fine

Thank you very much,
Cesar

CATCH block question

Hello,
With the new TRY - CATCH block feature in 2005, is there any way for
the calling application to know about errors unless I use RAISERROR?
The reason I ask is, I'd like to start using the new TRY - CATCH
syntax, but I don't know if our code is set up to receive the specific
error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
In fact, I don't even know if our apps are set up to handle return
codes. I think the way things have generally worked is - the stored
procedure is just allowed to break, with no error handling at all.
DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
using RAISERROR, but still have the calling app know about it?
If not, that means we have to rewrite a lot of code, which is not
desireable.
Thanks
Hi
You could return a status value, but your client code should always cater
for RAISERROR and other scenarios where the procedure does not return a value.
John
"tootsuite@.gmail.com" wrote:

> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR?
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
> If not, that means we have to rewrite a lot of code, which is not
> desireable.
> Thanks
>

CATCH block question

Hello,
With the new TRY - CATCH block feature in 2005, is there any way for
the calling application to know about errors unless I use RAISERROR?
The reason I ask is, I'd like to start using the new TRY - CATCH
syntax, but I don't know if our code is set up to receive the specific
error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
In fact, I don't even know if our apps are set up to handle return
codes. I think the way things have generally worked is - the stored
procedure is just allowed to break, with no error handling at all.
DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
using RAISERROR, but still have the calling app know about it?
If not, that means we have to rewrite a lot of code, which is not
desireable.
Thanks
You have to use RAISERROR to pass a logic error back to the application.
Of course, any unhandled errors, or critical SQL Server errors 'may' send an
error back to the application without having to raise an error.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1160429120.981006.283160@.e3g2000cwe.googlegro ups.com...
> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR?
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
> If not, that means we have to rewrite a lot of code, which is not
> desireable.
> Thanks
>
|||<tootsuite@.gmail.com> wrote in message
news:1160429120.981006.283160@.e3g2000cwe.googlegro ups.com...
> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR?
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
>
No. Applications typically expect to receive an error message when
something goes wrong. For instance .NET clients will receive the error
message and raise a SqlException, thus invoking the client-side structured
exception handling. Conceptually this allows an error to propagate from
server code to client code.

> If not, that means we have to rewrite a lot of code, which is not
> desireable.
>
Probably not. For most clients, using RAISERROR to "re-raise" the error
"just works".
David

CATCH block question

Hello,
With the new TRY - CATCH block feature in 2005, is there any way for
the calling application to know about errors unless I use RAISERROR'
The reason I ask is, I'd like to start using the new TRY - CATCH
syntax, but I don't know if our code is set up to receive the specific
error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
In fact, I don't even know if our apps are set up to handle return
codes. I think the way things have generally worked is - the stored
procedure is just allowed to break, with no error handling at all.
DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
using RAISERROR, but still have the calling app know about it?
If not, that means we have to rewrite a lot of code, which is not
desireable.
ThanksYou have to use RAISERROR to pass a logic error back to the application.
Of course, any unhandled errors, or critical SQL Server errors 'may' send an
error back to the application without having to raise an error.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1160429120.981006.283160@.e3g2000cwe.googlegroups.com...
> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR'
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
> If not, that means we have to rewrite a lot of code, which is not
> desireable.
> Thanks
>|||<tootsuite@.gmail.com> wrote in message
news:1160429120.981006.283160@.e3g2000cwe.googlegroups.com...
> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR'
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
>
No. Applications typically expect to receive an error message when
something goes wrong. For instance .NET clients will receive the error
message and raise a SqlException, thus invoking the client-side structured
exception handling. Conceptually this allows an error to propagate from
server code to client code.

> If not, that means we have to rewrite a lot of code, which is not
> desireable.
>
Probably not. For most clients, using RAISERROR to "re-raise" the error
"just works".
David

CATCH block question

Hello,
With the new TRY - CATCH block feature in 2005, is there any way for
the calling application to know about errors unless I use RAISERROR'
The reason I ask is, I'd like to start using the new TRY - CATCH
syntax, but I don't know if our code is set up to receive the specific
error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
In fact, I don't even know if our apps are set up to handle return
codes. I think the way things have generally worked is - the stored
procedure is just allowed to break, with no error handling at all.
DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
using RAISERROR, but still have the calling app know about it?
If not, that means we have to rewrite a lot of code, which is not
desireable.
ThanksHi
You could return a status value, but your client code should always cater
for RAISERROR and other scenarios where the procedure does not return a valu
e.
John
"tootsuite@.gmail.com" wrote:

> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR'
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
> If not, that means we have to rewrite a lot of code, which is not
> desireable.
> Thanks
>

CATCH block question

Hello,
With the new TRY - CATCH block feature in 2005, is there any way for
the calling application to know about errors unless I use RAISERROR'
The reason I ask is, I'd like to start using the new TRY - CATCH
syntax, but I don't know if our code is set up to receive the specific
error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
In fact, I don't even know if our apps are set up to handle return
codes. I think the way things have generally worked is - the stored
procedure is just allowed to break, with no error handling at all.
DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
using RAISERROR, but still have the calling app know about it?
If not, that means we have to rewrite a lot of code, which is not
desireable.
ThanksYou have to use RAISERROR to pass a logic error back to the application.
Of course, any unhandled errors, or critical SQL Server errors 'may' send an
error back to the application without having to raise an error.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1160429120.981006.283160@.e3g2000cwe.googlegroups.com...
> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR'
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
> If not, that means we have to rewrite a lot of code, which is not
> desireable.
> Thanks
>|||<tootsuite@.gmail.com> wrote in message
news:1160429120.981006.283160@.e3g2000cwe.googlegroups.com...
> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR'
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
>
No. Applications typically expect to receive an error message when
something goes wrong. For instance .NET clients will receive the error
message and raise a SqlException, thus invoking the client-side structured
exception handling. Conceptually this allows an error to propagate from
server code to client code.
> If not, that means we have to rewrite a lot of code, which is not
> desireable.
>
Probably not. For most clients, using RAISERROR to "re-raise" the error
"just works".
David

CATCH block question

Hello,
With the new TRY - CATCH block feature in 2005, is there any way for
the calling application to know about errors unless I use RAISERROR'
The reason I ask is, I'd like to start using the new TRY - CATCH
syntax, but I don't know if our code is set up to receive the specific
error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
In fact, I don't even know if our apps are set up to handle return
codes. I think the way things have generally worked is - the stored
procedure is just allowed to break, with no error handling at all.
DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
using RAISERROR, but still have the calling app know about it?
If not, that means we have to rewrite a lot of code, which is not
desireable.
ThanksHi
You could return a status value, but your client code should always cater
for RAISERROR and other scenarios where the procedure does not return a value.
John
"tootsuite@.gmail.com" wrote:
> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR'
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
> If not, that means we have to rewrite a lot of code, which is not
> desireable.
> Thanks
>