easier email alerts from sql 7/2000?

hi,

i get just as frustrated each time i try to configure email alerts on failed jobs on ms sql, it is beyond me why microsoft couldn't just let you point out an SMTP server to send through and be done with it.

is there a way to avoid having to setup an email client on our sql 7 and 2000 servers through some 3rd party app or other simple solution?

thanks in advance,
danielhi,

is there a way to avoid having to setup an email client on our sql 7 and 2000 servers through some 3rd party app or other simple solution?

thanks in advance,
daniel

Amen, brother!

You do have some options:

1. There are some 3rd party tools to (sort of) accomplish what you are trying to do. They are not supported, however. Look at www.sqldev.net.

2. You can add additional steps to the jobs you wish to monitor (and set the task order to on failure).

3. You can upgrade to SQL 2005. This is not quite as awful as it sounds and getting to Database Mail (which is SMTP) is a major benefit!

Regards,

hmscott|||Amen, brother!

You do have some options:

1. There are some 3rd party tools to (sort of) accomplish what you are trying to do. They are not supported, however. Look at www.sqldev.net.

2. You can add additional steps to the jobs you wish to monitor (and set the task order to on failure).

3. You can upgrade to SQL 2005. This is not quite as awful as it sounds and getting to Database Mail (which is SMTP) is a major benefit!

Regards,

hmscott
1. thanks, i'll check them out. but maybe it's just easier to parse the errorlogs of both servers for failed backups from some php script.

2. do you mean add a step with the "operating system command" type and execute some external program to send alerts?

3. the sql7 server hosts 67 databases, the sql2000 server hosts 38, most of them production databases so it's still a scary thought time-wise :)

Dynamically change the sender email address on a SQL2005 Windows 2003 server 64-bit box using da

We currently use xp_smtp_sendmail to send the emails on a sql2000 32-bit box. We are trying to move our databases to a new sql2005 64-bit box. One solution is to replace xp_smtp_sendmail with database mail since xp_smtp_sendmail does not work on 64-bit box. The only problem is that we need to change the sender email dynamically based on the query results and I don't know how to accomplish this by using database mail. Does anyone have an idea? We are not restricted to database-mail solution though.

Thanks for help.

EXECUTE msdb.dbo.sysmail_update_account_sp
@.account_name = 'AdventureWorks Administrator',
@.email_address = 'dba@.Adventure-Works.com'

See SQL Server 2005 Books Online topic:
sysmail_update_account_sp (Transact-SQL)
http://msdn2.microsoft.com/fr-fr/library/ms188381.aspx

EXECUTE msdb.dbo.sysmail_update_account_sp
@.account_name = 'AdventureWorks Administrator',
@.email_address = 'dba@.Adventure-Works.com'

|||Thanks. This answers my question.

Dynamically change the sender email address on a SQL2005 Windows 2003 server 64-bit box using da

We currently use xp_smtp_sendmail to send the emails on a sql2000 32-bit box. We are trying to move our databases to a new sql2005 64-bit box. One solution is to replace xp_smtp_sendmail with database mail since xp_smtp_sendmail does not work on 64-bit box. The only problem is that we need to change the sender email dynamically based on the query results and I don't know how to accomplish this by using database mail. Does anyone have an idea? We are not restricted to database-mail solution though.

Thanks for help.

EXECUTE msdb.dbo.sysmail_update_account_sp
@.account_name = 'AdventureWorks Administrator',
@.email_address = 'dba@.Adventure-Works.com'

See SQL Server 2005 Books Online topic:
sysmail_update_account_sp (Transact-SQL)
http://msdn2.microsoft.com/fr-fr/library/ms188381.aspx

EXECUTE msdb.dbo.sysmail_update_account_sp
@.account_name = 'AdventureWorks Administrator',
@.email_address = 'dba@.Adventure-Works.com'

|||Thanks. This answers my question.

Dynamically change From address for data driven subscription?

Is this possible? We need to set the from address differently for groups of
people so if the email bounces the right person sees it. Any help would be
greatly appreciated!I don't think it is possible. As a workaround consider implementing a rule
that will examine the subject of the bounced e-mail and forward it to the
right e-mail.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"MurrayT" <MurrayT@.discussions.microsoft.com> wrote in message
news:BE29E3E6-1536-4588-A129-99F07B3F7A01@.microsoft.com...
> Is this possible? We need to set the from address differently for groups
of
> people so if the email bounces the right person sees it. Any help would be
> greatly appreciated!
>

Dynamically add update parameter to formview

I have a formview with name, email, and password. I bind all fields to sql except the password which is blank.

In my sqldatasource, I define parameters for name, email and id:

UpdateCommand

="UPDATE UserProfile SET Name = @.Name,Email = @.Email WHERE (ID = @.ID)">
<UpdateParameters>
<asp:ParameterName="Name"/>
<asp:ParameterName="Email"/>
<asp:ParameterName="ID"/>
</UpdateParameters>

In code I want to add a password parameter if there is value in the password field otherwise I don't want the password field updated. If I add define a password parameter like above then if a user left the password field blank then their new is blank. That's way I think adding it dynamically is the way. But I am having problems with the code to add the parameter in sqldatasource_updating event.

Protected

Sub SqlProfile_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.Updating
Dim passwordAs TextBox = FormView1.FindControl

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not password.Text.ToString &"" =""ThenSqlProfile.UpdateParameters.Add(New Parameter("@.Password", TypeCode.String, password.Text.ToString))End IfEnd Sub

ThanksYou're close:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Thene.Command.Parameters.Add(password.Text)End IfEnd Sub

|||

I think you should add the parameter manually, and check for a null / blank parameter in the sql statement. That way you just pass what ever you have in your form (blank password or populated password) and let the SQL statement figure it out for you. If not, then you have do add a new parameter to the updateparameters AND modify your UpdateCommand to have the additional line.

need help with the SQL?

|||

ecbruck:

You're close:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Thene.Command.Parameters.Add(password.Text)End IfEnd Sub

if he does it that way, he will need to modify his command as well... adding "Password = @.Something"

|||

pixelsyndicate:

I think you should add the parameter manually, and check for a null / blank parameter in the sql statement.

I agree. I would personally let me Stored Procedure handle the case when the Password parameter was passed in as null.

|||

Thanks for the help.

This is what I have so far but still doesn't work.

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then SqlProfile.UpdateParameters.Add("password", password.Text) SqlProfile.UpdateCommand ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

|||

Thanks for the help.

This is what I have so far but still doesn't work.

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then SqlProfile.UpdateParameters.Add("password", password.Text) SqlProfile.UpdateCommand ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

 It updates the name field with no errors but the password doesn't get updated.

|||You need to be modifying the members of the SqlDataSourceCommandEventArgs class rather than the SqlDataSource class as I did in my previous example.|||

When I did your example:

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then e.Command.Parameters.Add(password.Text) e.Command.CommandText ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

I get this error:

The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.InvalidCastException: The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

Source Error:

Line 31: Dim password As TextBox = FormView1.FindControl("tb_password1")Line 32: If Not String.IsNullOrEmpty(password.Text) ThenLine 33: e.Command.Parameters.Add(password.Text)Line 34: e.Command.CommandText = "UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)"Line 35: End If

|||

Thanks for all the help. This finally work with this code:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Then Dim pAs SqlParameter =New SqlParameter("@.Password", SqlDbType.NVarChar) p.Value = password.Text e.Command.Parameters.Add(p) e.Command.CommandText ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)"End If End Sub

Dynamic Subscriptions in SQL Server 2005

Is there a way to dynamically populate the To: email address from a
database when setting up a subscription?
We would like to search a database for user activity and only deliver
reports to the user that that activity pertains to.
If not in native Reporting Services is there an add-on that will do
this?On Apr 14, 11:59=A0am, DaveK <1027...@.gmail.com> wrote:
> Is there a way to dynamically populate the To: email address from a
> database when setting up a subscription?
> We would like to search a database for user activity and only deliver
> reports to the user that that activity pertains to.
> If not in native Reporting Services is there an add-on that will do
> this?
Yes, data driven subscriptions, available in the Enterprise version,
has the ability to set all delivery specific settings and report
parameters based on a query executed at run-time.