Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Database Mail And Activation Error


Hi All,

I'm having some issues getting Database Mail to work with SQL Agent Job's which is doing my fruit !

 

I'm configuring the server using the following script:


 

DECLARE


@DataFile varchar(1000),

@LogFile varchar(1000),

@BackupFile varchar(1000),

@cmd Varchar(8000),

@ServerName varchar(1000),

@AccountName Varchar(1000),

@EmailAddress Varchar(1000),

@MailSubject VARCHAR(500),

@FullPath varchar(400),

@Drop varchar(100)

--Configure SQL Server DBMail

SET


@AccountName = (SELECT REPLACE(@@ServerName, '','-') + ' Email Profile')

SET

@EmailAddress = (SELECT REPLACE(@@ServerName, '','-') + '@OurDomain.com')

SET

@ServerName = (SELECT REPLACE(@@ServerName, '','-'))

IF EXISTS

(SELECT Name FROM msdb.dbo.sysmail_profile WHERE NAME = @AccountName)

BEGIN

PRINT 'Database Mail Profile Already Exists - Skiping Step'

END

ELSE

BEGIN

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = @AccountName,

@description = 'Mail account for administrative e-mail.',

@email_address = @EmailAddress,

@replyto_address = 'SQL_Notify@OurDomain.com',

@display_name = @ServerName,

@mailserver_name = 'MailServer.net' ;

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = @AccountName,

@description = 'Profile used for administrative mail.' ;

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = @AccountName,

@account_name = @AccountName,

@sequence_number =1 ;

-- Grant access to the profile to the DBMailUsers role

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = @AccountName,

@principal_name = 'Public',

@is_default = 1 ;

EXEC

msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', @AccountName

SET

@MailSubject = 'Database Mail Test Message from ' + @@SERVERNAME

PRINT

'Created DBMail Profile'

END

GO

DECLARE @MailSubject VARCHAR(500)

DECLARE @AccountName Varchar(1000)

SET

@AccountName = (SELECT REPLACE(@@ServerName, '','-') + ' Email Profile')

EXEC

msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1

EXEC

master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'Default Email Profile'

SET

@MailSubject = 'Database Mail Test Message from ' + @@SERVERNAME

--Check if DB Operator alredy exists; Create if not.

IF EXISTS (Select Name FROM MSDB.dbo.SysOperators WHERE NAME = 'SQL Notify')

BEGIN

PRINT 'Database Operator Already Exists - Skipping Step'

END

ELSE

BEGIN

EXEC msdb.dbo.sp_add_operator @name=N'SQL Notify',@enabled=1, @pager_days=0, @email_address=N'SQL_Notify@OurDomain.com'

END

EXEC

msdb.dbo.sp_send_dbmail

@profile_name = @AccountName,

@recipients = 'SQL_Notify@OurDomain.com',

@subject = @MailSubject,

@body = 'This is a Database Mail Test Message'

GO

 

OK - I get the test message, however when I setup a SQL Agent job later in the same script to alert me on failure; I dont get any mails through.

 

When I check the SysMail Event Log; I see the followign error:

 

error 2007-07-20 15:58:07.623 Activation failure. NULL NULL NULL 2007-07-20 15:58:07.623 sa

 

The SQLAgent is running under the same account as SQLServer; neither are local admin's on the machine. I've restatred SQLAgent using SSCM a number of times yet nothing comes through.

 

Mail Profile is started; Service Broker is enabled in MSDB

 

SQL Agent is configured with the correct profile; an operator is set as a failsafe operator.

 

Does anyone have any suggestions?????


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Device Activation Error Error When Trying To Restore From Database
Hi all.
I am getting the following error when trying to restore to an existing empty database from backup:
Device activation error. The physical file name 'C:SQL2000MSSQLBackupWSAdmin_Data.MDF' may be incorrect.
File Menu_DataFile Menu_Data cannot be restored to 'C:SQL2000MSSQLBackupWSAdmin_Data.mdf'. Use WITH MOVE to identify a valid location for the file.Device activation error. The physical file name 'C:SQL2000MSSQLBackupWSAdmin_Log.LDF' may be incorrect.File 'Menu_Log' cannot be restored to 'C:SQL2000MSSQLBackupWSAdmin_Log.LDF' . Use WITH MOVE to identify a valid location for the file.RESTORE DATABASE is terminating abnormally."
 
What Does this 'Menu_Log' file mean ? Please help, since I am short in time.
Thanks.

View Replies !   View Related
Device Activation Error: During MSDE Database Creation.
Hi,

I am trying to create the database TempDB on my MSDE instance:

This is my batch file:

@ECHO off
echo Creating Database.............
SETLOCAL

IF EXIST %ProgramFiles%Microsoft SQL ServerMSSQL$MyInstanceDataTempDB.mdf.
del "%ProgramFiles%Microsoft SQL ServerMSSQL$MyInstanceDataTempDB.mdf" 0>>NUL

IF EXIST %ProgramFiles%Microsoft SQL ServerMSSQL$MyInstanceDataTempDB_log.LDF.
del "%ProgramFiles%Microsoft SQL ServerMSSQL$MyInstanceDataTempDB_log.LDF" 0>>NUL

osql -U sa -S %COMPUTERNAME%MyInstance -P password -i createTableScript.sql 0>>Nul

ENDLOCAL
pause

and this is createTableScript.sql :
---------------------------------------
CREATE DATABASE TempDB
go
use TempDB
go


CREATE TABLE Table1 (
Name VARCHAR (64),
Age numeric,
)
go

----------------------------------------

Now ,if I run my batchfile for the first time ,it works fine.But from the second time onwards although it creates the database( new TempDB.mdf and TempDB.ldf files are created) successfully, the script shows following errror mesaages.

Device activation error. The physical file name 'C:Program FilesMicrosoft SQL
ServerMSSQL$MyInstanceDataTempDB.mdf' may be incorrect.
Device activation error. The physical file name 'C:Program FilesMicrosoft SQL
ServerMSSQL$MyInstanceDataTempDB_log.LDF' may be incorrect.

How can I overcome this?

Thanks .
KT.

View Replies !   View Related
SMTP Database Mail Showing Error While Sending Mail
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."

View Replies !   View Related
Device Activation Error
Hi All,
 
Currently I have a PC that has MSDE installed on it and is attached to database (MyData.MDF and a log file MYLog.LDF) located on its hard drive at c:data.  When I detach from the database, place a copy of the two files noted above on my network  drive @ u:data and try to attach I get the following error€™s: 
 
SQL[1] exec error = -1: Changed database context to 'master'.
€śDevice activation error. The physical file name u:dataMyData.MDF may be incorrect.€?
 
I have done some testing a have found that I can attach to a copy of my database if I move it anywhere on the c: drive, and or even to a 1Gb USB key attached to the system(e:).  So far it seems to only be an issue if I move it to a mapped network drive.    If anyone could please provide me with any info it would be greatly appreciated. 
 
Thanks.
 
James D.
 

View Replies !   View Related
Database Mail Error
Ive created an Account and a Profile using Configure Database Mail Wizard.

Ive tried to send an email using this account, but I get this error


The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-09-13T17:05:09). Exception Message: Cannot send mails to mail server. (Server does not support secure connections.). )


Can anyone help?

Regards

Steve


Steve Fouracre

View Replies !   View Related
Error Handling In Activation Procs
I am kinda curious how the rest of you are doing your error handling inside your activation stored procedures...best practices says you should not rollback the part of your transaction that receives the record off of the queue...but using a try...catch block will only allow you to rollback the entire transaction.  I tried using savepoints and starting the try...catch after the savepoint and the proc still gives me the error: "

The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction." when the error scenario is encountered.   

 

I tried using @@Error and checking for errors at the statement level which would allow me to do a partial rollback, but the type of errors I receive (i.e. invalid data types etc) are aborting the entire batch instead of passing the error and continuing.

 

Can other people on this forum using service broker give me an idea of how you are getting around this issue?

View Replies !   View Related
Cross Database Activation Security
I have two databases Basket_ODS and Intelligence_ODS.

I created a user in the Basket_ODS and Intelligence_ODS databases as follows:

USE Basket_ods

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*******'

CREATE USER BasketServiceUser WITHOUT LOGIN

ALTER AUTHORIZATION ON SERVICE::[Order Send] TO BasketServiceUser

GRANT CONTROL ON SERVICE::[Order Send]

TO BasketServiceUser

CREATE CERTIFICATE BasketServiceCertPriv

AUTHORIZATION BasketServiceUser

WITH SUBJECT = 'ForBasketService'

BACKUP CERTIFICATE BasketServiceCertPriv

TO FILE = 'BasketServiceCertPub'

In the other database...

I created the following:

USE Intelligence_ODS

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '************

USE Intelligence_ODS

GO

CREATE USER BasketServiceUser WITHOUT LOGIN

CREATE CERTIFICATE BasketServiceCertPub

AUTHORIZATION BasketServiceUser

FROM FILE = 'BasketServiceCertPub'

My Queue is  in  BASKET_ODS and is set up as:

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4,

EXECUTE AS 'BasketServiceUser'

)

I have performed the following grants in Basket_ODS

grant execute on ODS.ProcessOrderQueue to BasketServiceUser

ProcessOrderQueue calls [ODS].[MoveOrderTotals_Core] in the Intelligence_ODS database.

grant execute on [ODS].[MoveOrderTotals_Core] to BasketServiceUser

ProcessOrderQueue proc is set as follows:

ALTER procedure [ODS].[ProcessOrderQueue]

WITH EXECUTE AS 'BasketServiceUser'

 [ODS].[MoveOrderTotals_Core] is set up as follows:

when I run ProcessOrderQueue I get an error message:

ALTER procedure [ODS].[MoveOrderTotals_Core](@Orderid uniqueidentifier)

with execute as 'BasketServiceUser'

I just don't understand when I run ProcessOrderQueue I get the following error message (when database trust is turned off)

The server principal "sa" is not able to access the database "Intelligence_ODS" under the current security context.

Can you help me figure out what I'm doing wrong.  I've spent so much time on this security stuff.  Is there another way to do this that is more straight forward without using database trust? 

 

View Replies !   View Related
Error In Database Mail In Sql2005
hi

i have use database mail in sql server2005 to send mail.
i have complete all step to configure databasemail.but test the mail
the error is "database mail stoped.Use sysmail_start_up to start database mail .

View Replies !   View Related
Device Activation Error . The Physical File Name
rajesh writes "Hi All

When i attach my production database i got an device activation Error
cannot
attach database."

View Replies !   View Related
Device Activation Error On Sp_attach_db To Local Drive
Here is the error, I receive:
 
Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name 'L:Program FilesMicrosoft SQL ServerMSSQLLOGTestDB_Log.LDF' may be incorrect.
Device activation error. The physical file name 'L:Program FilesMicrosoft SQL ServerMSSQLLOGTestDB_Log.LDF' may be incorrect.
Msg 5170, Level 16, State 1, Line 1
Cannot create file 'L:Program FilesMicrosoft SQL ServerMSSQLLOGTestDB_log.LDF' because it already exists.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'TestDB'. CREATE DATABASE is aborted.
 
Here is my attach script:
USE [master]
GO
sp_attach_db @dbname= N'TestDB',
 @filename1 = N'S:Program FilesMicrosoft SQL ServerMSSQLDataTestDB.MDF',
 @filename2 = N'L:Program FilesMicrosoft SQL ServerMSSQLLOGTestDB_Log.LDF'
 
 This server has a Raid 5 Disk Array partitioned into three Logic Drives C:, S:, L:.  Not sure, why the server was configured this way.  If I attach the database with the data and log files on the same logical drive, the DB attaches successfully.  When I try to put the data and log files on separate logical drives, it fails.  Issue seems to be with the drive partitioning.  Any Ideas?

View Replies !   View Related
SQL Server 2005 Database Mail And Agent Error
Hello, hoping someone might be able to help.

I've set up Database Mail on an instance of 2005 and the test mail works just fine. However, when testing a job (notify on fail, success, whatever) the mail fails to send. Job History last information reads NOTE: Failed to notify 'My Name' via email.

Checked out the Database Mail logs - nothing interesting.

Restarted the Agent service and then checked out the current Agent Log. Found message:

[260] Unable to start mail session (reason: No mail profile defined).

The 'DatabaseMailProfile' key is most certainly defined in the Registry
(at 'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1SQLServerAgent')

I'm at a loss...why is it telling me no mail profile defined?
I'd be most grateful for any help if anybody else has run across this problem.

Cheers,
Megan

View Replies !   View Related
SQL Server 2005 Database Mail Command Not Supported (502) SMTP Error
Hi all,At this moment I'm trying to get database mail working. According tosome people it should be easy. Well...that is not the case for me.I'm having the following error:The mail could not be sent to the recipients because of the mail serverfailure. (Sending Mail using Account 2 (2006-11-24T08:48:15). ExceptionMessage: Cannot send mails to mail server. (Command not implemented.The server response was: Command not Supported).)SQL Server 2005 is installed on a separate server and the SMTP Serveris Lotus Notes. connecting with port 25.Start and stopping the SQL Server and SQL Server agent i tried already.i tried sysmail_stop_sp and sysmail_start_sp.Sometimes when starting or stopping the Mail with the followingcommands :exec sysmail_stop_spGOexec sysmail_start_spGOI will get the following error:Msg 233, Level 20, State 0, Line 0A transport-level error has occurred when sending the request to theserver. (provider: Shared Memory Provider, error: 0 - No process is onthe other end of the pipe.)So any help would be appreciated.Hennie

View Replies !   View Related
Database Mail Problem...Query Execution Failed: Error Initializing COM
Hello -

 

I'm having a problem sending the query set as an email text attachment.  Test transmissions from Database mail working fine.

Send simple messages with the sp_send_dbmail sproc works fine as well.

 

It is only when I try and send a query result that things blow up.  The query itself is working fine also, so I'm now down to think there is some esoteric problem with the sproc itself.

 

Surface config features have database mail on, and SQL Mail off.

 

Anyone know the solution to this?

 

Thanks in advance!

 

Actual SQL ...

 

use epic
go

 EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'SQLMail',
  @recipients = 'rvolters@whaleyfoodservice.com',
  @body = 'Testing sqlmail with sproc.',
  @subject = 'Orders with invalid tech numbers',
  @query = 'SELECT order_no, order_ext, line_no from epic..STG_BNERPT_INVALIDTECHNOS' ,
  @attach_query_result_as_file = 1 ;

 

 

Message Results...

 

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476
Query execution failed: Error initializing COM
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

 

Investigating the sproc itself shows...

 


Line 476 in the sproc is the beginning of a 'trap'
   --Raise an error it the query execution fails
   -- This will only be the case when @append_query_error is set to 0 (false)
   IF( (@RetErrorMsg IS NOT NULL) AND (@exclude_query_output=0) )
   BEGIN
      RAISERROR(14661, -1, -1, @RetErrorMsg)
   END

    RETURN (@rc)
this is the last section of code in the sproc

View Replies !   View Related
DB Mail And Notify Operator Through DB Mail Error Could Not Retrieve Item From The Queue
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?

View Replies !   View Related
Error Sending Mail With Send Mail Task
I keep getting a generic "Error Sending Mail" error. For testing purposes I am just trying to send using my own email account. What goes in the SMTP Server box in the connection manager? I have tried:
 
A - the exchange server address (SOMETHING.us.company.com)
B - The SMTP properties I see when I look at the properties of my email address: (my.name@abcd.efgh.company.com)
C - Just the end portion of the SMTP properties: (abcd.efgh.company.com)
D - My email address (my.name@company.com)
 
I don't know what to enter, or what is giving me such a generic error message.

View Replies !   View Related
Database Mail With Yahoo Business Mail Server
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

View Replies !   View Related
Help With Mail That Send Mail When Database Bakcup Fails
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

 

View Replies !   View Related
Problem With Sending Mail Via Database Mail
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

View Replies !   View Related
SQL Mail : Mail Error 0x80040106
When trying to setup SQL Mail to use POP3 and SMTP (not using Exchange), I am having no luck. This is what I've done:

1. Made sure Windows Messaging was installed
2. Created a profile using the same account SQL Server is running under
3. Tested the mailbox by sending and receiving mail

However, when going into Enterprise Manager / Support Services / SQL Mail / Properties, the Profile I have created isn't in the list. When I type it in, I get the error:

xp_sendmail: failed with mail error 0x80040106

Any help would be appreciated!

Thanks in advance,

Keith R. Mangold
kmangold@linkline.com
linkLINE Communications, Inc.

View Replies !   View Related
Database Mail & SQL Mail
Hi,
Can anyone please tell me the difference between Database Mail and SQL Mail.

Thanks in Advance
R.K.Nair


RKNAIR

View Replies !   View Related
Subscription Error : Failure Sending Mail: An Error Has Occurred During Report Processing
 

Hello,
 
    On the development server, I am trying to work with subscriptions . Report Server is windows authenticated.
 
When no paramters exist for the report, the sucbscription is successful.
But if there are paramters for the report, email delivery fails.
 
These are not data driven subscriptions.
 
Did anyone face the same problem ? Can anyone tell me where to start debugging since logfiles just say failure to send the email.
 
Thanks,
SqlNew

View Replies !   View Related
On Error I Want To Send Error Description As Mail
I have create a SSIS package, for data export import process, but if my task get failed then i have to send a mail with proper error description as the SSIS generate in output window.

Can any one sugget me to, how can i store that error desciption in my variable.?

Thanks in advance.

View Replies !   View Related
Changing Database Mail Host Database
Hi

I am trying to change the host database (MSDB) of Database Mail to some new database (say NewDB) and

unable to make DatabaseMail90.exe point to this new database (NewDB).

Is it possible to do this?

Thanks

Uddemarri

View Replies !   View Related
Activation
I was testing around with a sample service broker app using activation, and came across an interesting question.  The little app sends a series of four messages to a queue, either on the same conversation or on seperate ones.  Each message invokes one stored procedure in my activation procedure.  All the procedure does is enter a record into a test table and then wait for an allotted amount of time.  In my example, the first message called a proc that waited 20 sec, the 2nd one that waited 10 seconds, the third 5 seconds, and the 4th 1 second.  I am using internal activation on the queue.  It seemed that in both scenarios (sending on 4 separate conversations and on one conversation) the procedures executed "almost" sequentially.  "Almost" meaning that the first procedure was done before the last one started executing.  It makes sense to me that this would happen where I sent them on the same conversation, but not really when I sent them on 4 seperate ones.  Is it because when I call a procedure from my activation procedure it locks the queue so that another message cannot be processed (I'm processing a message at a time)?  How could I make it so that the 4th procedure (the one that only waits 1 second) returns before the 1st procedure (the one that waits 20 seconds)? 

View Replies !   View Related
Xp_cmdshell And Activation
We are trying to use xp_cmdshell commands in service broker.  If I kick off the process without activation, the process succeeds.  If I enable activation, the tasks with xp_cmdshell fail.

The xp_cmdshell task is either a echo command so that users know that we are processing a database, or a file copy.

I used the TechNet article by Roger Wolter to create the queues and stored procedures.

We will be working across domains that have one-way trusts.

Wayne

View Replies !   View Related
Activation Not Working
Hi There

Everything works 100% until i activate my sp.

I specify to execute as 'dbo' in the alter queue statement. I also define the activated sp to execute as 'dbo'.

But i keep getting permission errors from my activated sp. i have tried to excute as 'sa' , i have even tried to write a job that excutes to stored procedure but it also get weird errors. Bottom line if i exceute the sp in management studio logged in as sa it works , but thorugh activation or a job nothing works, as 'dbo' or 'sa'.

???

Thanx

 

View Replies !   View Related
More Problems With Activation
I have two databases on the same instance.

One is Basket_ODS and the other is Intelligence_ODS.  I am using service broker activation on a queue to move data from the Basket_ODS table to the Intelligence_ODS database.  Previously I was able to move from table to table in Basket_ODS, however now that I am moving it to another database on the same instance it is no longer working.

If I set my active connection in SQL Management Studio to this user(BrokerUser) and execute the "move" procedure it works.  When activated by Service Broker however, it does not.  Here is the error message:

2006-05-09 14:47:52.940 spid86s The activated proc [ODS].[ProcessOrderQueue] running on queue Basket_ODS.ODS.Order Process Queue output the following: 'The server principal "BrokerUser" is not able to access the database "Intelligence_ODS" under the current security context.'

 I'm sure I missed something becasue it works fine in the same database.  BrokerUser has datareader and datawriter in both databases.

Thanks for any help on this matter.

Gary

 

View Replies !   View Related
Changing Activation Key
I need to change the Activation Key for a copy of SQL Server 2000 that I already have installed and up and running. I have the new key but can't figure out how to change from the old, no longer valid, key.

How can I change keys w/o dumping the DB, uninstalling, and reinstalling all of my instances?

 

View Replies !   View Related
SQL Mail Error
I've just set up SQL Mail, and I think it's working -- at any rate, the envelope icon in Enterprise Manager
is green. Problem is, no matter what address I try to mail to, I get an 'Unknown recipient' error.

My input:

xp_sendmail 'srenaker@alexa.com', 'This is a test message.'

The results:

Msg 17914, Level 18, State 1
Unknown recipient: Parameter '@recipients', recipient 'srenaker@alexa.com'

Thanks in advance for any help,

Steve Renaker
srenaker@alexa.com

View Replies !   View Related
SQL Mail Error
Hi everyone,

I am very new to this and am having a problem with SQL mail. I want to be able to send view or query results via email. I have set up SQL mail and tested it and it works fine for sending notification messages to me. When I go to a query analyzer and issue the xp_sendmail 'jyoungbar@shorehealth.org','this is a test message'
I always get the error that follows:
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x8004011d

I am probably missing something very minor here. Can anyone help me??

Thanks,
Jason

View Replies !   View Related
SQL MAIL ERROR
I've set up my sql server to use Outlook, and keep getting this message when I try to start sql mail:
Error: 18009, Severity: 18, State: 1
EXCEPTION error encountered in MAPI extended procedures, exception error code = 0xC0000005.

I did the same install on another of my servers, and Mail works fine? The Admin user of the NT box starts all the SQL services...any help would be greatly appreciated.

View Replies !   View Related
Sql Mail Error
I'm trying to get sql mail up and running. I am able to get the service started. But when I go under SQL Mail/Properties and test the profile that I created it comes back with the following error. Error 22030 a MAPI error(error 273) occurred. Unable to get the default MAPI Message store due to MAPI error. the information store could not be opened. I am running sql server 7.0. I have set up a profile under the exchange. If I bring up outlook on the server I can send and receive messages. I have the both the mssqlserver and the sqlserveragent running under the same account. If I go under operators and do a test on one of the operators I get the following message. The sqlmail session is not running but I sit it running under enterprise manager. We are using the outlook. Any ideas would be greatly appreciated. Thanks

View Replies !   View Related
Database Mail
HI,when configuring database mail in sql server 2005 i am getting the below error.The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2006-09-13T13:50:18). Exception Message: Could not connect to mail server. (An established connection was aborted by the software in your host machine). )when i ping the smtp server, i could successfully connect to the smtp server. but when i send a test email i am getting the above error message saying not able to connect to the server.please help

View Replies !   View Related
Database Mail
Hello,

I am trying to use database mail to send out a list of data. I am using a number of queries to build a string and each query needs to have its results displayed on a new line when the email is viewed using Outlook.

I am having trouble adding a line break into the string. Any pointers would be great.

Thanks Alot

Steve

View Replies !   View Related
Database Mail
Hi, i need to send emails from sql server. to configure the setting i need to open database mail configuration windows right? But i dont have database mail icon or tab under management folder. I only have archieve under server roles. Help me.. Urgent.Thanks

View Replies !   View Related
Database Mail
hi,
In database mail,
while execting sp_send_dbmail i want foolowing thing to be done.

@recipient=select emaild from <tablename>

Is it possible ?

View Replies !   View Related
Database Mail
Please Concentrate on colored TSQL;
I should i pass dynamic text to database mail in @body.
Declare @charstrn varchar(20);
Declare @C_name varchar(20);
Declare @l_code nvarchar(20);
SET @charstrn = @S_name+'Welcome to India.You are now registered Student under counslerName'+@C_name+'BranchOffice'+@l_code;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Pradeepvarun',
@recipients = @emailid ,
@body = (select @charstrn),
@subject = 'You are now Registered in APT';

View Replies !   View Related
Database Mail
What is equavalent in oracle database which does the job similar to Database Mail in sql server.

i use database mail in sql server to send mail.

I do have jobs set, which gets fired every ten minutes. it uses msdb.dbo.sp_send_dbmail

and i have a profile created under database mail which has all the info related to the smtp email acct.

our client now wants our project to also work on oracle.

Thank you very much for the information.

View Replies !   View Related
Database Mail.
I have an application which allows communication through an internal blog. We have to monitor all communications for compliance but I'm unable to find a solution to my problem and hope someone can help.

The application allows IM/Email/Blog entries to authenticated members. We can take these communications and send them to our exchange server. However the FROM address is always the profile owner setup in SQL.

Question:

I want the FROM address to list the person starting the conversation and not setup individual profiles for each person using the application. We already have the ability to capture the communication through the application and send it to exchange but the FROM address doesn't reflect the person initiating the conversations.

Is this possible?

Thank you, MP

View Replies !   View Related
Database Mail
Hi All,

I am going made here. I set up database mail on a 2005 box. It was working fine, untill I needed to reboot it. Now it does not.

If I run a test with 2000 it works fine. However 2005 does not.

I go to the test mail bit and it says its sent, but it never turns up. So I ran:-

SELECT * FROM msdb.dbo.sysmail_event_log

And I get the fowwloing error:-

259error2008-06-06 16:38:24.493Could not retrieve item from the queue.5708NULLNULL2008-06-06 16:38:24.493UK_domainUSER


Any ideas at all would be gratfull aprisiated!!!

Thanks

Dave


Dave Dunckley says there is a law for the rich and a law for the poor and a law for
Dirty Davey.

View Replies !   View Related
Database Mail
I'm keen to use database Mail in SQL 2005. It seems to work well and save's me having to implement a queue, audit logging for sent/errored etc, as SQL2005 can take care of all of that for me.

heres the but..

Is there any way to specify the "From" email address in sp_send_dbmail I know you specify it as part of email account & profile but I want to override that.

I just wanted to be able to keep an audit of all the emails that where sent from an asp.net website and thought i could get that functionality "out of the box" with SQL 2005 and DB Mail, but with out being able to set the from: address its not going to work


Any one have a sugesstion?

Thanks

View Replies !   View Related
Database Mail
I am trying to configure Database Mail on a new installation of SQL Server 2005 and the 'DatabaseMailUserRole' does not exist. 

How do I add this role to the server?

View Replies !   View Related
Database Mail
I'm trying to use SQL server email, I am able to use it from the administrator account, but anyone else gets the "EXECUTE permission was denied on the object 'sp_send_dbmail'.  The MSDN says "To send Database mail, users must be a user in the msdb database and a member of the DatabaseMailUserRole database role in the msdb database. To add msdb users or groups to this role use SQL Server Management Studio or execute the following statement for the user or role that needs to send Database Mail." .   The only problem is - I have no idea what the user name / role database is.  I've plunked around the MSDB database and I can't find any documentation, help, anything.  I know the login name of the user I want to enable to use database mail, but obviously that's not what the bit of SQL code that MS provided is looking for.  Is there an "AddMember" function somewhere??  I don't see it.  Should it really be this complicated to do simple email tasks - am I looking in the wrong place?

View Replies !   View Related
Database Mail
 

Hi,

I have setup a stored procedure which sends an email. It is working fine and I am also receiving all the emails. But, when I checked the Database Mail log in SQL Server, there are hundreds of error messages which I am not able to decide why?

Can someone please provide information on these?


 
 
Date  10/3/2007 5:31:13 AM
Log  Database Mail (Database Mail Log)
Log ID  2385
Process ID  5464
Last Modified  10/3/2007 5:31:13 AM
Last Modified By  MMITask_PWDXServer
Message
1) Exception Information
===================
Exception Type: System.NullReferenceException
Message: Object reference not set to an instance of an object.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32)
HelpLink: NULL
Source: DatabaseMailEngine
StackTrace Information
===================
   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)
   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)

View Replies !   View Related
Database Mail
Dear Folks,I am trying to finish my configuration of Database Mail on my sp1 build 2153 sql server. I believe to have set it up properly but i still can't get a test message through. My error message reads:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2006-08-09T15:14:00). Exception Message: Cannot send mails to mail server. (Transaction failed. The server response was: Mail from <account email address> rejected for policy reasons.). )

Everything that needs to be enabled, i.e. service broker, SQL Server Agent properties, deafult profile in msdb table, services restarted. Is this message a result of Sql server policy or the smtp server? Is there a process in the transaction Sql server initiates that could be causing the policy violation that i need to tell the email admin about?
Thanks for your reply.
-C

View Replies !   View Related
SP1 Vs. Database Mail
There have been at least 2 other threads mentioning that updating to SP1 after Database mail has been set up causes problems, but a real solution has not been described. So for what it's worth, here's the problem again:

From an original developer edition release, Database mail is set up 1) in Surface Area Configuration, 2) a Database account and profile set up, and 3) SQL Server Agent set to send alerts to mail.

After upgrading to SP1, the following error will occur at each startup:

SQLSERVERAGENT reports: [298] SQLServer Error: 2812, Could not find procedure 'msdb.dbo.xp_sqlagent_notify'.

Restarting services, changing profiles to public, and other past suggestions didn't help.

But what really gets me is that if I completely remove the SQL agent alert setting,  DELETE the mail profile and account, and turn off database mail in the surface area configuration (and restart a few times), the ERROR STILL OCCURS! You can't get rid of it, even if database mail is completely removed!!

I finally gave up and re-installed the whole SQL Server, added SP1, and then added database mail again. But this problem needs a resolution.

Any comments or suggestions?

- Richard

 

View Replies !   View Related
Database Mail Qs
Dear Profetionals ,
I am new in SQL I  would like to set up a Database mail but I have no Mail Server in my Domain. So, I have to use yahoo pop3 and smtp whitch are mentioned here :
 




Incoming Mail (POP3) Server:
plus.pop.mail.yahoo.com (Use SSL, port: 995)

Outgoing Mail (SMTP) Server:
plus.smtp.mail.yahoo.com (Use SSL, port: 465, use authentication)

Account Name/Login Name:
Your Yahoo! Mail ID (your email address without the "@yahoo.com")

Email Address:
Your Yahoo! Mail address (e.g., user@yahoo.com)

Password:
Your Yahoo! Mail password

 
I used my yahoo email account and plus.smtp.mail.yahoo.com port: 465 to configure the Databast Mail
But no test mail recieved , Then I tried SQL help and found this SELECT * FROM msdb.dbo.sysmail_event_log ;i used this SP and the result was :
1 information 2008-04-08 14:53:53.397 DatabaseMail process is started 1188 NULL NULL 2008-04-08 14:53:53.397 IMPSDevuser2 error 2008-04-08 14:54:11.190 The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2008-04-08T14:54:10). Exception Message: Could not connect to mail server. (No such host is known). ) 1188 1 NULL 2008-04-08 14:54:11.190 saWould you please help me find the error?  

View Replies !   View Related
Database Mail
 



We have a user set up for our .net framework (webuser) with access to a database we created for our website.  "webuser" needs to be able to send out emails using Databse Mail (SQL Server 2005) via a stored proc by excuting msdb.dbo.sp_send_dbmail.  This stopred proc works fine when I am logged in as administrator but won't work for "webuser" and I can't seem to grant DatabaseMailUserRole to our that user either.  What do I need to do?

 
I have bee trying to find a "how to"  on setting this up but have been unsuccessful.

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved