How do I get the option my database mail profile to appear in the drop down list on the "Notifications Page" in my job properties?
I have configured the mail profile and sucessfully sent a test E-mail.
I have also set "Enable mail profile" in the SQL Agent Properties and restarted the agent.
What else do I need to do, help please?
I've set up DB mail and sent a test e-mail and that comes through fine.
I set up an Operator with email Name: DWhelpton@k-and-s.com;MWeaver@k-and-s.com
I created a job and set up the notifications to e-mail the operator on failure.
When the job runs and fails, I do not get an e-mail and I get the following exception in the db mail log:
Date 2/2/2007 8:35:00 AM
Log Database Mail (Database Mail Log)
Log ID 402
Process ID 3936
Last Modified 2/2/2007 8:35:00 AM
Last Modified By NT AUTHORITYSYSTEM
Message
1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: Could not retrieve item from the queue.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Controller.ICommand CreateSendMailCommand(Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DBSession)
HelpLink: NULL
Source: DatabaseMailEngine
StackTrace Information
===================
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)
What step am I missing?
I'm running sqlserver 2000 EE on windows nt 4.0 server. We are using
Microsoft outlook express. I would like to setup our sqlserver to
notify me via e-mail when a job finishes.
1) What value should I have for mail profile. Under
sql server age properties.
Please let me know.
Thanks,
Ranjan
We need to set up an email alert to activate when the ODBC connection fails to link the database to the application.Is it possible?We ahve the SQL mail working already.What shall we do to create such an alert?
Thanks!
I am havig trouble getting database mail to work. I setup IIS on the same box as sql. I setup SMTP with the relay to 127.0.0.0. In sql server, I setup database mail to point to localhost, with no authentication. After the setup, when I test the email (RMB Send test message) it works fine.
I created a job that will fail every time. I setup myself as the notify party on the job. When I run the job, it fails. I get no email. The job log has the following error:
Message
The job failed. The Job was invoked by User xxxxAdministrator. The last step to run was step 1 (test 1). NOTE: Failed to notify 'Dan Jones' via email.
I have spent lots of hours trying to make this simple thing work. What is wrong?
Hi all...
Sorry if this is a real simple question, but I just had a SQL 6.5 server dropped in my lap. I need to transfer all the data to a new box, which I did using the Tools mneu (Database/Object Transfer) in Enterprise Manager. I checked the databases after the transfer and all the data seems to be there, including the logins. However, if I try to connect the database, all logins fail.
Connection failed.
SQL State '28000'
SQL Server Error 4002
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed
Does any body know how to fix this easily without resetting every single user id?
Any help
would be greatly appreciated.
Thanks
Greg
Lokendra writes "I have configured the Database mail profile and account in Sql Server 2005 but the mail is not sending and showing the following error message:
Error,235,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-03-05T15:16:07). Exception Message: Cannot send mails to mail server. (Mailbox name not allowed. The server response was: Sorry<c/> that domain isn't in my list of allowed rcpthosts.).
),3000,90,,3/5/2007 3:16:07 PM,sa
but while in the same mail set up in previous instance of sql server 2005 the message was sending very well. After installing new instance of sql server 2005 the problem is arising.
Anybody can tell me that what I can do so that i can send mail using the SMTP databasemail account."
Hi all....
Our company use yahoo business mail server for our corporate mails. I know that I can configure database mail with SMTP. But when I try to configure database mail account with yahoo bizmail, I cannot do that. It gets configured but when I test it it doesn't send any mails. Do I need to have any special condiguration for this. SMTP address is smtp.bizmail.yahoo.com. Also I have specified the Authentication using my user name and password. Please help
Thanks
Rajesh
Hello
I have got a script which gives the mail to the dba mail box when database backup fails.
In the script I want to make a change so that I get the particular database name , on what ever database i implement.
Can you tell me some suggestions.
The script I am using is :
use master
go
alter PROCEDURE dbo.SendMail
@to VARCHAR(255),
@subject VARCHAR(255),
@message VARCHAR(8000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@rv INT,
@from VARCHAR(64),
@server VARCHAR(255);
SELECT
@from = 'testsql2000@is.depaul.edu',
@server = 'smtp.depaul.edu';
select @message = @message + char(13) + Char(13) + @@servername + '-'+ db_name()+ '-' + 'Backup Status Failed' + Char(13)
EXEC @rv = dbo.xp_smtp_sendmail
@to = @to,
@from = @from,
@message = @message,
@subject = @subject,
@server = @server;
END
GO
--- After the above script is run the following should be given in the 2nd step when
--- the backup jobs are scheduled ------
exec master.dbo.sendmail
@to = 'dvaddi@depaul.edu',
@subject =' Test sqlserver 2000',
@message = '' ;
Thanks
Hi every body
I want to send a simple mail using DATABASE MAIL feature in SQL SERVER 2005.
I've defined a public profile.
I've enabled Database Mail stored procedures through the Surface Area Configuration .
but I can't send a mail with sp_send_dbmail stored procedure in 'msdb' database .
when I execute sp_send_dbmail in the Managment Studio the message is
"Mail queued" but the mail is not sent.
Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?
here is the log file after executing sp_send_dbmail:
1) "DatabaseMail process is started"
2) "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it)."
The DatabaseMail90.exe is triggred ,so the mail is transfered to the mail queue but DatabaseMail90.exe couldn't give the mail to SMTP server.The promlem is what should I do to make DatabaseMail90.exe able to connect to the server?
please help me.
POUYAN
Hi,
Can anyone please tell me the difference between Database Mail and SQL Mail.
Thanks in Advance
R.K.Nair
RKNAIR
Hello,
I have a problem with the notification. I have enabled Database mail and i have created an Operator.
Under SQL Server AgentJobs and then in the job properties i have enabled notification but i don`t receive a mail. Test e-mail works fine but "real" e-mail not.
I have also tried with the Database Maintenance Wizard but even then it just not work.
I have noticed that others have experienced the same problem but i have seen no solution yet.
Could you please help me? What can cause this problem? What`s the best i can do to start searching for the solution? Thanks!
Currently, I have set up a SQL 7.0 database backup job and have one person notified if the job succeeds, but HOW do I choose an option to notify a different person if the job fails ?
Thank you in advance
I can not get SQLServerAgent to Send Mail. Help!
SQLMail started successfully.
In the server agent properties I use the Test button and I receive the
response.
"Successfully started(and stopped) a mail session with this profile."
Indicating that mail should work.
If I send a email notification from a scheduled job it gives me the error:
NOTE: Failed to notify 'operations' via email
In the SQL Server Agent Error log I get.
"[264] An attempt was made to send an email when no email session has been
established"
xp_sendmail works fine.
Any ideas would be greatly appreciated.
TIA...
Hi Guys,
Is there a way to notify DBA when a new stored procedure,table,view is created by any user...
Help is greatly appericated..
Jessi..
I have a requirement to create a sorted list of objects, stored withinan assembly, and I would like to use a After Insert/Update trigger tonotify that assembly that something has changed, rather than pollingthe database for changes all the time.My initial problem is that I need to create a static list so that itcan be dynamically looked at by other assemblies, but SQL Server won'tallow me to attach a dll which contains a static method.Has anyone done something like this? Are there any decent examplesavailable anywhere?any help will be appreciated.simo
View 4 Replies View RelatedHi,
I am developing desktop application with SQL Server 2005 in C#. I need your help. My Question is "How to notify a Database trigger is executed thru C# application?".
Now I am going to explain clearly. I have a table "Employee" and I created one trigger (it may be INSERT/UPDATE/DELETE) for that table. Then I created a C# application to show a msg box when that table is modified. Now my question is If that table is modified a message box will be shown. I know about trigger (If trigger assigned table is modified that trigger will automatically executed.) But how do i know that trigger is executed thru a C# application. Please send your suggestion or links.
Thanx,
GANESAN MURUGESAN.
Hi all,
I have a VB.NET 2005 application that needs to monitor constantly each change of a field on a SQL server 2005.
Is there a way of setting a trigger up in the SQL Server that notifies the VB.NET application to avoid having the VB.NET apllication querying the SQL table all day for changes?
The table in SQL stores a temperature value. I need the VB.NET application to receive this value everytime there's a change. I could do a timer on the VB.NET application to check for changes in the table every 1 second but I don't think this is efficient. The temperature value may change once every two or three seconds.
The VB.NET application will process the temperature value.
Is there a way of notifying the VB.NET application (which runs from a different server) from within SQL Server? If so, how? If not, what would you go about implementing this?
Thanks again for your comments,
ST
I have two question
1.In SSIS is there a way to unzip the text files
I have set an automated process in sql server agent
I receive ziped text files.I want to unzip the files before loading the data
2.Is there a way to notify when an error occurs in the package in sql server agent.
Please let me know
hi,
there is any way to a bd notify the user by creat a file or send a e-mail or even connect to a ip and send a message when any record is changed ( update/insert/delete)
i have seen a event called triger but i donīt know if is that what i want and i think this event canīt do what i need.
any one can help me?
thanks a lot
Hello
I have a number of DTS packages which when run manually complete successfully however, when run as scheduled tasks they always fail. Can anyone offer any advice?
Many thanks
Where I work, we use a lot of triggers on our tables. And most of the time we use them to send email out (using xp_sendmail). For example, a user enters data and there is an insert trigger on that table to send email out to the appropriate individuals. This is all well and good until the SQL Mail Agent stops running for one reason or another. People try to enter their data and because there is an insert trigger on the table which tries to send mail out the entire transaction fails and the data can't be saved simply because the trigger can't successfully execute xp_sendmail. Does anyone know of a way around this? A better way to accomplish this? Any suggestions are appreciated!
View 2 Replies View RelatedHi. I am running SQL2000 standard edition on a Windows 2K server. I have this annoying problem that the jobs I create through the maintenance plan wizard fail consistantly on a certain database that has one table in it. Just the database integrity check and update statistics part of the maintenence plan fail, the backups are fine.
This will occur on a brand new database, with the default options selected and with only one empty table in it. The script to create the table and indexes is below. The errors I get from the maintenance plan are:
--Check Data and Index Linkage
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
--Update QP Statistics
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
I have messed around with setting these db options on and off with no effect on the success of the maintainence plan. I am wondering if anyone can replicate the problem on there own installation or have any thoughts on how to fix. Oh if I take the formula off of the table the jobs run successfully.
Thanks in advance.
Script:
/****** Object: Table [dbo].[UserLog] Script Date: 4/10/2001 2:39:48 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UserLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[UserLog]
GO
/****** Object: Table [dbo].[UserLog] Script Date: 4/10/2001 2:39:51 PM ******/
CREATE TABLE [dbo].[UserLog] (
[LogId] [int] NOT NULL ,
[Website] [tinyint] NOT NULL ,
[SessionID] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Time] [datetime] NULL ,
[DayOfYear] AS (datepart(dayofyear,[Time])) ,
[PageID] [smallint] NULL ,
[ZipCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReferringDomain] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_Time_PageID] ON [dbo].[UserLog]([Time], [PageID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UserLog] WITH NOCHECK ADD
CONSTRAINT [DF_UserLog_Website] DEFAULT (1) FOR [Website]
GO
CREATE INDEX [IX_Time_SessionID] ON [dbo].[UserLog]([Time], [SessionID]) ON [PRIMARY]
GO
CREATE INDEX [IX_Time_SessionID_ReferringDomain] ON [dbo].[UserLog]([Time], [SessionID], [ReferringDomain]) ON [PRIMARY]
GO
CREATE INDEX [IX_Time_SessionID_PageID_ReferringDomain] ON [dbo].[UserLog]([Time], [SessionID], [PageID], [ReferringDomain]) ON [PRIMARY]
GO
set ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS on
set NUMERIC_ROUNDABORT off
CREATE UNIQUE INDEX [IX_SessionID_DayOfYear_PageID] ON [dbo].[UserLog]([SessionID], [DayOfYear], [PageID]) WITH IGNORE_DUP_KEY ON [PRIMARY]
GO
set arithabort OFF
GO
set numeric_roundabort OFF
GO
set quoted_identifier OFF
GO
I have a DTS Job that is reporting failures but it looks to me as ifthe job is actually completing successfully. The job only has acouple steps. Step 1 (DTSStep_DTSExecuteSQLTask_1) Execute SQL Task,runs a stored procedure to export blobs (pdf files) out of sql serverand onto the local machine.Here is the code in the storedprocedure called sp_PDFExportCREATE PROCEDURE [dbo].[sp_PDFExport] ASbeginset quoted_identifier offdeclare @pk intdeclare @where_clause varchar(100)declare @file_name varchar (50)declare @debug varchar (50)Declare @cmd varchar (50)--debug/*if @Debug = 1print @cmdexec Master..xp_cmdShell @cmd */-- begin cursorDECLARE LOOKUP CURSOR FOR select pr.[id]from plan_report pr, plan_version pvwhere pv.plan_id = pr.plan_id and pv.status = '30' and pr.create_time
Quote:
I have job in SQL Server 2000 that is setup to notify an email operator upon successful completion.
When I test the notification to the set email operator it sends the email just fine.
When the job is run manually it emails the operator just fine.
When the job is run on a schedule it does not email the operator upon completion. It gives the details in view job history "NOTE: Failed to notify '<operator>' via email.
I have other jobs on the same server that run and notify via email to other operators upon successful completion just fine.
I have parsed MS Technet and the web and did not find anything.
Any help is greatly appreciated.
When my SQL Agent job completes successfully, I'd like to send a notification to QlikView. The idea is to stop QlikView having to constantly check if the job has been run, and just get notified when it needs to update its data.
I don't know how to start. Can the job notifications push a success message to QlikView? I don't think I want to send an email, but something that indicates to QlikView to update the tables.
Hi,
can the Notify Operator Task send the output error message of its predecessor task(e.g Execute T-SQL Statement Task) to the operator? If yes, how?
Thanks,
Greg
Simple question:
I have two servers S1 and S2. Inmediately after new data on S1 is available I want to perform some actions on S2.
I can use a trigger on S1, but if S2 is down the transaction on S1 will be lost. I could use database replication but I only need one single table in S1 to report changes to S2
Is there any other approach I could use?
Thanks.
We have a job created by the maintenance job wizard that backs up the transaction logs for all of our databases on an hourly basis. At random intervals, one of the tranaction log backups will fail with the following message in the job history:
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
The next scheduled transaction log backup will run fine the next hour. The sqlmaint.exe is present and executable. There are no additional messages in the SQL Server error log or SQL Agent error log. Any ideas what causes this random failure?
Hi all,
I could really use some assistance. I have been researching this problem for over a month now and I have not made any headway or progress.
I am running SQL Server 2000 on Windows 2000 Server. Hardware is Dual Xeon 2.4/400 Procs, 2GB ram and 1 Raid10 Array with 4x 36 GB 10K RPM drives.
The server has about 50 dbs on it. All are primarly used in conjuction with some web application or site. On average the server sees about 270ish connections/sessions.
About 1 - 2 months ago, we started seeing random login failures. We have no explanation for these failures. Our coldfusion code gives us detailed logging information regarding the exact statement that was being executed when the login failed. We try to reproduce the failed login, we cannot. There are no misspelling, code inconsitencies in this regards because the logins are set in the data source which verify.
We are using per-processor licensing, so unless there is a hidden limit we are hitting or MS is lying about per-processor licensing having unlimmited connections, that is not the issue. Also, I've ruled out some kind of network issue because if that were the case, the login would have timed out, as opposed to failing. I've been running a trace and viewing the failed logins.
Has anyone dealt with this issue before?
Best Regards,
Errol Neal
/*
This SP has 2 functions.
a) if @method='duration' gives the average run duration in minutes for successful jobs
b) if @method='failures' displays failures/cancels/still executing jobs
It defaults to today's date. Specify @xdate for a different date
-- Louis Nguyen
*/
CREATE PROCEDURE UtilityJobsHistory
(
@method varchar(100)='duration'
,@xdate datetime=null
)
AS
set nocount on
set transaction isolation level read uncommitted
if @method='duration' begin
select @xdate=isnull(@xdate,getdate())
/*run_duration is in HHMMSS format; drop SS*/
/*run_staus: 1 complete 2 retry*/
/*step_id: 0 is final job outcome*/
/*run_date: yyyymmdd format*/
/*today's performance*/
select a.name,minutes=avg((b.run_duration / 100)/100*60 + (b.run_duration / 100)%100)
into #today
from msdb..sysjobs as a
join msdb..sysjobhistory as b
on a.job_id=b.job_id
where run_status in ('1','2') and step_id=0 and run_date =convert(varchar,@xdate,112)
group by a.name
/*7 day average performance*/
/*populate #D with dates in yyyymmdd format*/
create table #D (run_date varchar(50))
declare @idate datetime set @idate=@xdate
while @idate>dateadd(day,-7,@xdate) begin
insert into #D
select run_date=convert(varchar,@idate,112)
select @idate=dateadd(day,-1,@idate)
end
/*Avg7Days*/
select a.name,minutes=avg((b.run_duration / 100)/100*60 + (b.run_duration / 100)%100)
into #avg7Days
from msdb..sysjobs as a
join msdb..sysjobhistory as b
on a.job_id=b.job_id
join #D as c
on b.run_date = c.run_date
where run_status in ('1','2') and step_id=0
group by a.name
/*output*/
select name=cast(a.name as varchar(35)),OneDayAvg=a.minutes,SevenDayAvg=b.minutes
from #today as a
join #avg7days as b
on a.name=b.name
order by a.name
return end
if @method='failures' begin
select @xdate=isnull(@xdate,getdate())
select status=case run_status when 0 then 'FAILED' when 3 then 'CANCELED' when 4 then 'EXECUTING' end
,name=cast(a.name as varchar(35)),step_name
,time=replace(convert(varchar,@xdate,107),' ','')+' '+right('0000'+cast(b.run_time/100 as varchar),4)
,b.message
from msdb..sysjobs as a
join msdb..sysjobhistory as b
on a.job_id=b.job_id
where run_status in ('0','3','4') and run_date=convert(varchar,@xdate,112)
order by run_status,a.name
return end
I've installed the SP2 on three dev/test servers. I went to install on production and the install failed on the Nofication and Client updates.
The error message was -
MSP Error: 29549 Failed to install and configure assemblies C:Program FilesMicrosoft SQL Server90NotificationServices9.0.242Binmicrosoft.sqlserver.notificationservices.dll in the COM+ catalog. Error: -2146233087
Error message: Unknown error 0x80131501
Error description: The transaction has aborted.
MSP Error: 29549 Failed to install and configure assemblies C:Program FilesMicrosoft SQL Server90NotificationServices9.0.242Binmicrosoft.sqlserver.notificationservices.dll in the COM+ catalog. Error: -2146233087
Error message: Unknown error 0x80131501
Error description: The transaction has aborted.
Any ideas?
I have a package that loops through a list of servers. I keep the list in a table, read it, then loop through it and dynamically set the ServerName in the Connection Manager so that I can connect to one server after another without having to set up separate Connection Managers for each. A Data Flow task queries each server for configuration information and writes that to a central database. Everything works well, unless a server is offline for some reason.
As long as it doesn't exceed the max number of errors, the package logs the error, skips over that server and continues along just fine. What I'd like to do is trap that error and manually write a row to the central database with the server name and an error message, so that at least all the servers show up in the report, even if they don't all have configuration data listed.
How do I handle this type of connection error? Everything I've seen on error handling either assumes it's a data error or that you want to log the error in some external log file. I want to execute a SQL script that writes the value of a variable (the server name) to a table.