Database Mirrororing Monitor - error retrieving data

Hello,
I can't see data for mirror database in Database Mirrororing Monitor.
The mirroring is configured and runs properly between 2 locations. I can
even register mirrored database with both peers - it works the same with
principal and mirrored databases in registration process. I'm using windows
authentication and the account has sysadmin role in both server instances.
However upon registration, the principal database appears in state
Synchronized while mirror database appears with "Error retrieving data" in
Current Role, Mirroring State and Witness Connections columns.
Both SQL servers are 64-bit Enterprise 2005 SP2 versions on 64-bit Windows
Server 2003 SP2.
Could someone help me to setup the monitoring?
Thanks a lot
eXavierHello eXavier,
Can you please answer the following questions:
1. What version of SQL Server 2005 are you running? The build number
resembles
9.0.xxxx.
2. Can you please zip and send me the error logs for each of the servers.
3. Run the following SQL statements and please return the results to me in
an
output file
a. Select * from sys.databases (run this on principle and mirror server)
b. Select * from sys.database_mirroring (run this on principle and mirror
server)
c. Select * from sys.database_mirroring_witnesses (run this on the witness
server)
d. Select * from sys.database_mirroring_endpoints (run on all servers)
e. Select * from sys.tcp_endpoints (run on all servers)
f. Select * from sys.server_principals (run on all servers)
g. Select * from sys.database_recovery_status (run on principal and mirror
server)
h. Select * from msdb.dbo.backupset (run on principal server only)
i. Select * from sys.dm_os_wait_stats (run on principal and mirror servers)
j. Select * from sys.sysprocesses (run on all servers)
k. Verify the connectivity between the servers using Telnet and send the
results.
To do this:
l. Open a command prompt.
m. Run telnet <machine name> <port number> where machine name is the fully
qualified domain name and port number is the port number for the mirroring
endpoint.
n. Run this from the principal to the mirror and from the mirror to the
principal.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.sql

Database Mirroring Error

When I configured database mirroring between two servers in separate DMZ regions, I get the following error on the principle server when I execute the ALTER DATABASE xxxxxxxx SET PARTNER = '****':

The server network address %%% can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)

Ports have been opened on both machines and I can TELNET both without any problems. I have included the steps that I used.

DATABASE MIRRORING TEST CONFIGURATION

SQL Server 2005 STD

Principal: AA-AAA-AA01
Mirror: BB-BBB-BB07

A. Create certificates for outbound connection on principal server. Refer to
http://msdn2.microsoft.com/en-us/library/ms186384.aspx.

1. In the master database, create a database Master Key.

M:\DBADM\MIRRORING\CREATE_MAST_KEY_ENCRY
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
GO

2. In the master database, create an encrypted certificate on the server instance.

M:\DBADM\MIRRORING\CREATE_AA01_MIRRORING_CERT
USE master;
CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
WITH SUBJECT = 'AA-AAA-AA01 certificate for database mirroring';
GO
SELECT * FROM SYS.CERTIFICATES
GO

3. Create an endpoint for the server instance using its certificate.

M:\DBADM\MIRRORING\CREATE_AA01_ENDPOINT
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5999
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO

4. Back up the certificate to a file and securely copy it to the other system or systems.

M:\DBADM\MIRRORING\CREATE_AA01_CERT_BKUP
BACKUP CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
TO FILE = 'M:\DBADM\MIRRORING\AA_AAA_AA01_MIRRORING_CERT_BKUP.cer';
GO

B. Create certificates for outbound connection on mirror server. Refer to
http://msdn2.microsoft.com/en-us/library/ms186384.aspx.

1. In the master database, create a database Master Key.

M:\DBADM\MIRRORING\CREATE_MAST_KEY_ENCRY
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
GO

2. In the master database, create an encrypted certificate on the server instance.

M:\DBADM\MIRRORING\CREATE_BB07_MIRRORING_CERT
USE master;
CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
WITH SUBJECT = 'BB-BBB-BB07 certificate for database mirroring';
GO
SELECT * FROM SYS.CERTIFICATES
GO

3. Create an endpoint for the server instance using its certificate.

M:\DBADM\MIRRORING\CREATE_BB07_ENDPOINT
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5999
, LISTENER_IP = PARNTER
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO

4. Back up the certificate to a file and securely copy it to the other system or systems.

M:\DBADM\MIRRORING\CREATE_BB07_CERT_BKUP
BACKUP CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
TO FILE = 'M:\DBADM\MIRRORING\BB_BBB_BB07_MIRRORING_CERT_BKUP.cer';
GO

C. Configure server instances inbound mirroring connections on principal server. Refer to
http://msdn2.microsoft.com/en-us/library/ms187671.aspx.

1. Create a login for other system.

M:\DBADM\MIRRORING\CREATE_BB07_LOGIN
USE master;
CREATE LOGIN BB07_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@.#';
GO

2. Create a user for that login.

M:\DBADM\MIRRORING\CREATE_BB07_USER
USE master;
CREATE USER BB07_MIRROR_ADMIN FOR LOGIN BB07_MIRROR_ADMIN;
GO

3. Obtain the certificate for the mirroring endpoint of the other server instance.

4. Associate the certificate with the user created in step 2.

M:\DBADM\MIRRORING\CREATE_BB07_USER_CERT_LINK
USE master;
CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
AUTHORIZATION BB07_MIRROR_ADMIN
FROM FILE = 'M:\DBADM\MIRRORING\BB_BBB_BB07_MIRRORING_CERT_BKUP.cer'
GO

5. Grant CONNECT permission on the login for that mirroring endpoint.

M:\DBADM\MIRRORING\GRANT_BB07_LOG_ACCESS
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO BB07_MIRROR_ADMIN;
GO

D. Configure server instances inbound mirroring connections on master server. Refer to
http://msdn2.microsoft.com/en-us/library/ms187671.aspx.

1. Create a login for other system.

M:\DBADM\MIRRORING\CREATE_AA01_LOGIN
USE master;
CREATE LOGIN AA01_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@.#';
GO

2. Create a user for that login.

M:\DBADM\MIRRORING\CREATE_AA01_USER
USE master;
CREATE USER AA01_MIRROR_ADMIN FOR LOGIN AA01_MIRROR_ADMIN;
GO

3. Obtain the certificate for the mirroring endpoint of the other server instance.

4. Associate the certificate with the user created in step 2.

M:\DBADM\MIRRORING\CREATE_AA01_USER_CERT_LINK
USE master;
CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
AUTHORIZATION AA01_MIRROR_ADMIN
FROM FILE = 'M:\DBADM\MIRRORING\AA_AAA_AA01_MIRRORING_CERT_BKUP.cer'
GO

5. Grant CONNECT permission on the login for that mirroring endpoint.

M:\DBADM\MIRRORING\GRANT_AA01_LOG_ACCESS
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO AA01_MIRROR_ADMIN;
GO

E. Back up the Principal Database and copy backup files to mirror server.

F. Restore the Principal Database on the standby database server (use NORECOVERY).

G. Configure the mirroring partners. Refer to http://msdn2.microsoft.com/en-us/library/ms191140.aspx.

1. On the mirror server instance on BB-BBB-BB07, set the server instance on AA-AAA-AA01 as the partner (making it the initial principal server instance).

M:\DBADM\MIRRORING\ACTIVATE_MIRROR_FOR_AA01
--At BB-BBB-BB07, set server instance on AA-AAA-AA01 as partner (principal server):
ALTER DATABASE BESmgmt
SET PARTNER = 'TCP://AA-AAA-AA01.mycompany.com:5999';
GO

2. On the principal server instance on AA-AAA-AA01, set the server instance on BB-BBB-BB07 as the partner (making it the initial mirror server instance).

M:\DBADM\MIRRORING\ACTIVATE_MIRROR_FOR_BB07

--At AA-AAA-AA01, set server instance on BB-BBB-BB07 as partner (mirror server).
ALTER DATABASE BESmgmt
SET PARTNER = 'TCP://BB-BBB-BB07.mycompany.com:5999';
GO

3. Execute the following SELECT on both servers to review mirroring information.

SELECT * FROM SYS.DATABASE_MIRRORING

Can ping both IP addresses and DNS names in both servers? If no, please try to add record in both networks DNS servers or add both SQL servers IP addresses in local hosts files (e.g. C:\Windows\System32\Drivers\Etc\hosts). Moreover, is it opened all SQL required ports in firewall? Detail information please refer to "How to: Configure a Firewall for SQL Server Access http://msdn2.microsoft.com/en-us/library/ms175043.aspx". Active Directory ports are required too.|||

Thanks for the info. During the meanwhile, I set up transactional replication.

|||

By the way, the servers have been added to the HOST files on each server. The ports appeared to be opened. I did a netstats -adn in the command prompt. I may stick with replication since I am on a tight deadline. I was hoping to get database mirroring to work in a short time since it appears to be easier in switching back and forth for failover scenerios.

Thanks again.

|||

Cause and Resolution of Database Engine Errors

http://msdn2.microsoft.com/en-us/library/aa337361.aspx

http://msdn2.microsoft.com/en-us/library/ms365262.aspx|||

Did you try to install two SQL servers in same network before?

|||

I wanted to first try going from server to server within the network but I could not find two server with similar configurations. So, I ended up testing database mirroring for the first time between two servers in their own DMZ space.

Thanks.

|||

I suggest you setup two testing servers in Virtual Environment, such as MS Virtual and VMware Server. It's free download and use. It easy to learn and test Database Mirroring.

Database Mirroring in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Database mirroring setup in SQL Server 2005

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1199004,00.html?bucket=ETA&topic=301326

|||

Thanks. I will review the posted website.

|||

I finally got pass the 1418 error message. The problem was with the way the server name was configured and the HOSTS file on the C drive.

sql

Database Mirroring Error

When I configured database mirroring between two servers in separate DMZ regions, I get the following error on the principle server when I execute the ALTER DATABASE xxxxxxxx SET PARTNER = '****':

The server network address %%% can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)

Ports have been opened on both machines and I can TELNET both without any problems. I have included the steps that I used.

DATABASE MIRRORING TEST CONFIGURATION

SQL Server 2005 STD

Principal: AA-AAA-AA01
Mirror: BB-BBB-BB07

A. Create certificates for outbound connection on principal server. Refer to
http://msdn2.microsoft.com/en-us/library/ms186384.aspx.

1. In the master database, create a database Master Key.

M:\DBADM\MIRRORING\CREATE_MAST_KEY_ENCRY
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
GO

2. In the master database, create an encrypted certificate on the server instance.

M:\DBADM\MIRRORING\CREATE_AA01_MIRRORING_CERT
USE master;
CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
WITH SUBJECT = 'AA-AAA-AA01 certificate for database mirroring';
GO
SELECT * FROM SYS.CERTIFICATES
GO

3. Create an endpoint for the server instance using its certificate.

M:\DBADM\MIRRORING\CREATE_AA01_ENDPOINT
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5999
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO

4. Back up the certificate to a file and securely copy it to the other system or systems.

M:\DBADM\MIRRORING\CREATE_AA01_CERT_BKUP
BACKUP CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
TO FILE = 'M:\DBADM\MIRRORING\AA_AAA_AA01_MIRRORING_CERT_BKUP.cer';
GO

B. Create certificates for outbound connection on mirror server. Refer to
http://msdn2.microsoft.com/en-us/library/ms186384.aspx.

1. In the master database, create a database Master Key.

M:\DBADM\MIRRORING\CREATE_MAST_KEY_ENCRY
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
GO

2. In the master database, create an encrypted certificate on the server instance.

M:\DBADM\MIRRORING\CREATE_BB07_MIRRORING_CERT
USE master;
CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
WITH SUBJECT = 'BB-BBB-BB07 certificate for database mirroring';
GO
SELECT * FROM SYS.CERTIFICATES
GO

3. Create an endpoint for the server instance using its certificate.

M:\DBADM\MIRRORING\CREATE_BB07_ENDPOINT
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5999
, LISTENER_IP = PARNTER
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO

4. Back up the certificate to a file and securely copy it to the other system or systems.

M:\DBADM\MIRRORING\CREATE_BB07_CERT_BKUP
BACKUP CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
TO FILE = 'M:\DBADM\MIRRORING\BB_BBB_BB07_MIRRORING_CERT_BKUP.cer';
GO

C. Configure server instances inbound mirroring connections on principal server. Refer to
http://msdn2.microsoft.com/en-us/library/ms187671.aspx.

1. Create a login for other system.

M:\DBADM\MIRRORING\CREATE_BB07_LOGIN
USE master;
CREATE LOGIN BB07_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@.#';
GO

2. Create a user for that login.

M:\DBADM\MIRRORING\CREATE_BB07_USER
USE master;
CREATE USER BB07_MIRROR_ADMIN FOR LOGIN BB07_MIRROR_ADMIN;
GO

3. Obtain the certificate for the mirroring endpoint of the other server instance.

4. Associate the certificate with the user created in step 2.

M:\DBADM\MIRRORING\CREATE_BB07_USER_CERT_LINK
USE master;
CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
AUTHORIZATION BB07_MIRROR_ADMIN
FROM FILE = 'M:\DBADM\MIRRORING\BB_BBB_BB07_MIRRORING_CERT_BKUP.cer'
GO

5. Grant CONNECT permission on the login for that mirroring endpoint.

M:\DBADM\MIRRORING\GRANT_BB07_LOG_ACCESS
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO BB07_MIRROR_ADMIN;
GO

D. Configure server instances inbound mirroring connections on master server. Refer to
http://msdn2.microsoft.com/en-us/library/ms187671.aspx.

1. Create a login for other system.

M:\DBADM\MIRRORING\CREATE_AA01_LOGIN
USE master;
CREATE LOGIN AA01_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@.#';
GO

2. Create a user for that login.

M:\DBADM\MIRRORING\CREATE_AA01_USER
USE master;
CREATE USER AA01_MIRROR_ADMIN FOR LOGIN AA01_MIRROR_ADMIN;
GO

3. Obtain the certificate for the mirroring endpoint of the other server instance.

4. Associate the certificate with the user created in step 2.

M:\DBADM\MIRRORING\CREATE_AA01_USER_CERT_LINK
USE master;
CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
AUTHORIZATION AA01_MIRROR_ADMIN
FROM FILE = 'M:\DBADM\MIRRORING\AA_AAA_AA01_MIRRORING_CERT_BKUP.cer'
GO

5. Grant CONNECT permission on the login for that mirroring endpoint.

M:\DBADM\MIRRORING\GRANT_AA01_LOG_ACCESS
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO AA01_MIRROR_ADMIN;
GO

E. Back up the Principal Database and copy backup files to mirror server.

F. Restore the Principal Database on the standby database server (use NORECOVERY).

G. Configure the mirroring partners. Refer to http://msdn2.microsoft.com/en-us/library/ms191140.aspx.

1. On the mirror server instance on BB-BBB-BB07, set the server instance on AA-AAA-AA01 as the partner (making it the initial principal server instance).

M:\DBADM\MIRRORING\ACTIVATE_MIRROR_FOR_AA01
--At BB-BBB-BB07, set server instance on AA-AAA-AA01 as partner (principal server):
ALTER DATABASE BESmgmt
SET PARTNER = 'TCP://AA-AAA-AA01.mycompany.com:5999';
GO

2. On the principal server instance on AA-AAA-AA01, set the server instance on BB-BBB-BB07 as the partner (making it the initial mirror server instance).

M:\DBADM\MIRRORING\ACTIVATE_MIRROR_FOR_BB07

--At AA-AAA-AA01, set server instance on BB-BBB-BB07 as partner (mirror server).
ALTER DATABASE BESmgmt
SET PARTNER = 'TCP://BB-BBB-BB07.mycompany.com:5999';
GO

3. Execute the following SELECT on both servers to review mirroring information.

SELECT * FROM SYS.DATABASE_MIRRORING

Can ping both IP addresses and DNS names in both servers? If no, please try to add record in both networks DNS servers or add both SQL servers IP addresses in local hosts files (e.g. C:\Windows\System32\Drivers\Etc\hosts). Moreover, is it opened all SQL required ports in firewall? Detail information please refer to "How to: Configure a Firewall for SQL Server Access http://msdn2.microsoft.com/en-us/library/ms175043.aspx". Active Directory ports are required too.|||

Thanks for the info. During the meanwhile, I set up transactional replication.

|||

By the way, the servers have been added to the HOST files on each server. The ports appeared to be opened. I did a netstats -adn in the command prompt. I may stick with replication since I am on a tight deadline. I was hoping to get database mirroring to work in a short time since it appears to be easier in switching back and forth for failover scenerios.

Thanks again.

|||

Cause and Resolution of Database Engine Errors

http://msdn2.microsoft.com/en-us/library/aa337361.aspx

http://msdn2.microsoft.com/en-us/library/ms365262.aspx|||

Did you try to install two SQL servers in same network before?

|||

I wanted to first try going from server to server within the network but I could not find two server with similar configurations. So, I ended up testing database mirroring for the first time between two servers in their own DMZ space.

Thanks.

|||

I suggest you setup two testing servers in Virtual Environment, such as MS Virtual and VMware Server. It's free download and use. It easy to learn and test Database Mirroring.

Database Mirroring in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Database mirroring setup in SQL Server 2005

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1199004,00.html?bucket=ETA&topic=301326

|||

Thanks. I will review the posted website.

|||

I finally got pass the 1418 error message. The problem was with the way the server name was configured and the HOSTS file on the C drive.

Database Mirroring - 'NT AUTHORITY\ANONYMOUS LOGON.' failed

Hello,

I have set up 3 servers - Primary, Mirror and Witness. When I run the database mirroring wizard all my endpoints are configured, but when i start the mirroring service i get a 1418 error - in the logs in says -

Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 10.20.1.5]

I have looked through the BOL and can't find anything helpful. - I'm pretty new to SQL server, so any help is very much appreciated!

Thanks

Kerpoise

This is not a SQL problem but an authentication issue. The right approach is to follow the guidance of the authentication troubleshooting whitepaper: http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx

HTH,
~ Remus

|||But why is my Primary server trying to logon as NT AUTHORITY/ANONYMOUS LOGON? When I have defined my logon through the Mirroring Wizard as a sysadmin account, with enabled Connect privileges on all servers.

I can't find a simple solution in the white paper. Isn't there a simple way to either - authenticate as a sysadmin account, or to allow NT AUTHORITY/ANONYMOUS to have CONNECT privileges to all servers?

It seems like this should be a very quick thing to do, but I really can't figure it out.

Please help!|||

Do not grant connect permission to ANONYMOUS LOGON.

When connecting the two instances what gets authenticated is the 'service account', the Windows account that runs the SQL Server instance. Depending on how you insnstalled the instances, the appropiate action is:
- if installed as 'LOCAL SERVICE': you must change the service account, it will never work. The SQL Management console in mmc has an option to change the service account, selecta different account.
- if installed as 'NETWORK SERVICE' or 'LocalSystem': you have to register the service SPN for Kerberos authentication to succeed. Use a tool like setspn.exe (available at microsoft download center) and register the SPN. Mirroring will use an SPN on the format 'MSSQLSvc/<partnername>:<partnerport>'. Also, make sure the AUTHENTICATION option on the mirroring endpoint is WINDOWS, WINDOWS NEGOTIATE or WINDOWS KERBEROS (in other words NTLM will not work). After registering the two SPNs (both for mirror and principal!), the authentication will resolve to the machine account ('DOMAIN\MachineName$'), you must create a login for this account and grant connect permission to it.
- if installed as a local account ('PrincipalMachine\LocalAccount' and/or 'MirrorMachine\LocalAccount'): it will not work, you must change the service account(s).
- if installed as domain account ('DOMAIN\UserName'), then it should work as long as both the mirror and the principal are in the same domain, or there is a trust relationship between their domains.

An alternative is to use certificate based authentication, then the whole domain/user/SPNs issue vanishes as the authentication will use a diferent protocol (SChannel's TLS).

HTH,
~ Remus

Database Mirror + Snapshot + Replication Questions

All,

We have SQL 2005 db mirror configured with a witness server for high availability. Node 1 is the principal and Node 2 is the mirror. A nightly job creates a snapshot on Node 2. The snapshot is used for previous day reporting queries. We have now been asked to present another copy of the database for near-time reporting. I thought about possibly adding a peer-to-peer replication as part of my environment but was hoping to see what everyone else out there is doing.

Regards,

Ian

I tend to use transactional replication with a pollinginterval of 1 and replicate the execution of stored procedures as much as possible.

Database Maintenance Plans for Backup

Hello

We created maintenance plans for Backup, we configured as:

1. Backup set expires after 2 days. (but we still see backup files are at the location from day one)

2. There is Overwrite and Append in backup file settings. what eaxactly overwrites means, in case we set up expire the backup set after 2 days.

please advice. Thanks, Jay

If you are using a single backup device (whether it's a tape, file location...it's the same thing), overwrite means it will replace the backup on that device with the current backup. So that device will just hold the one backup. Append means it will add the current backup to the device. That device will hold multiple backups.

If you use a single device, set it to overwrite and the backup has not expired and you have selected the option to check the expiration date of the backup, you will get an error. It is designed to prevent you from accidentally overwritting a backup that you want to keep.

-Sue

Database Maintenance Plan Issue with non-default instances

I have a SQL Server (2000 sp3) with one default instance and many named
instances. I have configured, tested, and monitored a database maintenance
plan for the default instance - works fine. I can't seem to get any of the
default instances to perform the maintenance plan. I have examined the logs,
but I don't see any any relevant entries (I just tried to kick off the job
manaully by executing the associated SQL job). I do see one message in the
log across all of the named instances I tried to run the maintenance plan
against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
stored procedure 'xp_qv'". I have googled this message, but I came up empty.
I should also note that the maintenance plan isn't even writing the history
report.
The only difference I'm aware of is that I performed a 'minimum install' on
the named instances. My transaction logs are growing and growing. The only
way I know to keep the size of these in check is to back them up. Please help
if you can.
Thank you,
- Johnny
Hi
It is not clear how you are running this maintenance plan, and if you
created a different plan in the other instances (which if you did will
probably have a different Ids).
You may want to use create a job that calls xp_slqmaint and specifies the
database and options separately rather than in a plan.
John
"JohnnyMagz" wrote:

> I have a SQL Server (2000 sp3) with one default instance and many named
> instances. I have configured, tested, and monitored a database maintenance
> plan for the default instance - works fine. I can't seem to get any of the
> default instances to perform the maintenance plan. I have examined the logs,
> but I don't see any any relevant entries (I just tried to kick off the job
> manaully by executing the associated SQL job). I do see one message in the
> log across all of the named instances I tried to run the maintenance plan
> against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
> stored procedure 'xp_qv'". I have googled this message, but I came up empty.
> I should also note that the maintenance plan isn't even writing the history
> report.
> The only difference I'm aware of is that I performed a 'minimum install' on
> the named instances. My transaction logs are growing and growing. The only
> way I know to keep the size of these in check is to back them up. Please help
> if you can.
> Thank you,
> - Johnny

Database Maintenance Plan Issue with non-default instances

I have a SQL Server (2000 sp3) with one default instance and many named
instances. I have configured, tested, and monitored a database maintenance
plan for the default instance - works fine. I can't seem to get any of the
default instances to perform the maintenance plan. I have examined the logs,
but I don't see any any relevant entries (I just tried to kick off the job
manaully by executing the associated SQL job). I do see one message in the
log across all of the named instances I tried to run the maintenance plan
against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
stored procedure 'xp_qv'". I have googled this message, but I came up empty.
I should also note that the maintenance plan isn't even writing the history
report.
The only difference I'm aware of is that I performed a 'minimum install' on
the named instances. My transaction logs are growing and growing. The only
way I know to keep the size of these in check is to back them up. Please help
if you can.
Thank you,
- JohnnyHi
It is not clear how you are running this maintenance plan, and if you
created a different plan in the other instances (which if you did will
probably have a different Ids).
You may want to use create a job that calls xp_slqmaint and specifies the
database and options separately rather than in a plan.
John
"JohnnyMagz" wrote:
> I have a SQL Server (2000 sp3) with one default instance and many named
> instances. I have configured, tested, and monitored a database maintenance
> plan for the default instance - works fine. I can't seem to get any of the
> default instances to perform the maintenance plan. I have examined the logs,
> but I don't see any any relevant entries (I just tried to kick off the job
> manaully by executing the associated SQL job). I do see one message in the
> log across all of the named instances I tried to run the maintenance plan
> against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
> stored procedure 'xp_qv'". I have googled this message, but I came up empty.
> I should also note that the maintenance plan isn't even writing the history
> report.
> The only difference I'm aware of is that I performed a 'minimum install' on
> the named instances. My transaction logs are growing and growing. The only
> way I know to keep the size of these in check is to back them up. Please help
> if you can.
> Thank you,
> - Johnny

Database Maintenance Plan Issue with non-default instances

I have a SQL Server (2000 sp3) with one default instance and many named
instances. I have configured, tested, and monitored a database maintenance
plan for the default instance - works fine. I can't seem to get any of the
default instances to perform the maintenance plan. I have examined the logs,
but I don't see any any relevant entries (I just tried to kick off the job
manaully by executing the associated SQL job). I do see one message in the
log across all of the named instances I tried to run the maintenance plan
against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
stored procedure 'xp_qv'". I have googled this message, but I came up empty.
I should also note that the maintenance plan isn't even writing the history
report.
The only difference I'm aware of is that I performed a 'minimum install' on
the named instances. My transaction logs are growing and growing. The only
way I know to keep the size of these in check is to back them up. Please hel
p
if you can.
Thank you,
- JohnnyHi
It is not clear how you are running this maintenance plan, and if you
created a different plan in the other instances (which if you did will
probably have a different Ids).
You may want to use create a job that calls xp_slqmaint and specifies the
database and options separately rather than in a plan.
John
"JohnnyMagz" wrote:

> I have a SQL Server (2000 sp3) with one default instance and many named
> instances. I have configured, tested, and monitored a database maintenance
> plan for the default instance - works fine. I can't seem to get any of the
> default instances to perform the maintenance plan. I have examined the log
s,
> but I don't see any any relevant entries (I just tried to kick off the jo
b
> manaully by executing the associated SQL job). I do see one message in the
> log across all of the named instances I tried to run the maintenance plan
> against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
> stored procedure 'xp_qv'". I have googled this message, but I came up empt
y.
> I should also note that the maintenance plan isn't even writing the histor
y
> report.
> The only difference I'm aware of is that I performed a 'minimum install' o
n
> the named instances. My transaction logs are growing and growing. The only
> way I know to keep the size of these in check is to back them up. Please h
elp
> if you can.
> Thank you,
> - Johnny

Database Mail SMTP authentication

SS 2005 Standard 64bit

I configured database mail and I suspect the SMTP authentication credentials are not being sent. Here are the two scenarios I have tried:

1) Use a local exchange server with basic authentication passing network credentials that have access to use the exchange service. I have a SS 2000 machine that is configure this way. Sending email to an address outside the domain fails with

"mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2006-08-25T14:02:23). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for ...."

I can send emails within the domain no matter what the credentials specified in basic authentication are, including invalid users.

2) Use a remote smtp server with basic authentication passing username (email address) and password, which fails with "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2006-08-25T12:56:22). Exception Message: Cannot send mails to mail server. (Transaction failed. The server response was: 5.7.1 myaddress@.me.com: Sender address rejected: Access denied)."

any idears?

Hi,

I have the same problem, I can't even send emails within the domain.

Help Please!!!!

|||

You'll probably have more luck in another forum or newsgroup. This one is dedicated to SQLNS.

But for starters, try this link:

http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/fc3ca8d7d6589a0f/8c9a329972d2feeb?lnk=st&q=database+mail+sql+server+authentication&rnum=1&hl=en#8c9a329972d2feeb

HTH...

Joe

Database Mail SMTP authentication

SS 2005 Standard 64bit

I configured database mail and I suspect the SMTP authentication credentials are not being sent. Here are the two scenarios I have tried:

1) Use a local exchange server with basic authentication passing network credentials that have access to use the exchange service. I have a SS 2000 machine that is configure this way. Sending email to an address outside the domain fails with

"mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2006-08-25T14:02:23). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for ...."

I can send emails within the domain no matter what the credentials specified in basic authentication are, including invalid users.

2) Use a remote smtp server with basic authentication passing username (email address) and password, which fails with "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2006-08-25T12:56:22). Exception Message: Cannot send mails to mail server. (Transaction failed. The server response was: 5.7.1 myaddress@.me.com: Sender address rejected: Access denied)."

any idears?

Hi,

I have the same problem, I can't even send emails within the domain.

Help Please!!!!

|||

You'll probably have more luck in another forum or newsgroup. This one is dedicated to SQLNS.

But for starters, try this link:

http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/fc3ca8d7d6589a0f/8c9a329972d2feeb?lnk=st&q=database+mail+sql+server+authentication&rnum=1&hl=en#8c9a329972d2feeb

HTH...

Joe

Database Mail SMTP authentication

SS 2005 Standard 64bit

I configured database mail and I suspect the SMTP authentication credentials are not being sent. Here are the two scenarios I have tried:

1) Use a local exchange server with basic authentication passing network credentials that have access to use the exchange service. I have a SS 2000 machine that is configure this way. Sending email to an address outside the domain fails with

"mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2006-08-25T14:02:23). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for ...."

I can send emails within the domain no matter what the credentials specified in basic authentication are, including invalid users.

2) Use a remote smtp server with basic authentication passing username (email address) and password, which fails with "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2006-08-25T12:56:22). Exception Message: Cannot send mails to mail server. (Transaction failed. The server response was: 5.7.1 myaddress@.me.com: Sender address rejected: Access denied)."

any idears?

Hi,

I have the same problem, I can't even send emails within the domain.

Help Please!!!!

|||

You'll probably have more luck in another forum or newsgroup. This one is dedicated to SQLNS.

But for starters, try this link:

http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/fc3ca8d7d6589a0f/8c9a329972d2feeb?lnk=st&q=database+mail+sql+server+authentication&rnum=1&hl=en#8c9a329972d2feeb

HTH...

Joe

Database Mail Operator Question

I have two jobs configured to e-mail myself on completion, but I am not currently receiving these notifications.
The SQL Agent is configured to use database mail, with a specific private profile. Of note is that the button to "Test" the mail profile is grayed out on the Alert System tab. When I execute sp_send_dbmail using the e-mail address from the operator and use the profile name defined in the SQL Agent properties, I receive an e-mail. However the jobs themselves are not sending me any e-mail.
I've also confirmed the correct name of the profile in the sysmail_profile table. The e-mail does not appear in sysmail_mailitems and sysmail_send_retries is empty. So the job notification is not getting queued.
Any ideas?
Thank-you,
Rubens
It is running under a domain account that is an administrator on the server, which is the same account the SQL Server service is running under (I haven't split them up and restricted it as is recommended).
Thank-you,
Rubens
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:O6%231lsNmIHA.1208@.TK2MSFTNGP05.phx.gbl...
Hi
What account is SQL Agent running under?
John
"Rubens" <rubensrose@.hotmail.com> wrote in message news:%23Tec6mLmIHA.3780@.TK2MSFTNGP06.phx.gbl...
I have two jobs configured to e-mail myself on completion, but I am not currently receiving these notifications.
The SQL Agent is configured to use database mail, with a specific private profile. Of note is that the button to "Test" the mail profile is grayed out on the Alert System tab. When I execute sp_send_dbmail using the e-mail address from the operator and use the profile name defined in the SQL Agent properties, I receive an e-mail. However the jobs themselves are not sending me any e-mail.
I've also confirmed the correct name of the profile in the sysmail_profile table. The e-mail does not appear in sysmail_mailitems and sysmail_send_retries is empty. So the job notification is not getting queued.
Any ideas?
Thank-you,
Rubens
|||Hmmm, come to think of it, no I wasn't. I will have to test that when back in the office tomorrow. I will let you know.
Thanks John.
Rubens
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:erH9oGOmIHA.1184@.TK2MSFTNGP04.phx.gbl...
Hi
And were you logged in as this account when you tested sp_send_dbmail?
John
"Rubens" <rubensrose@.hotmail.com> wrote in message news:%23A5C6DOmIHA.5084@.TK2MSFTNGP04.phx.gbl...
It is running under a domain account that is an administrator on the server, which is the same account the SQL Server service is running under (I haven't split them up and restricted it as is recommended).
Thank-you,
Rubens
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:O6%231lsNmIHA.1208@.TK2MSFTNGP05.phx.gbl...
Hi
What account is SQL Agent running under?
John
"Rubens" <rubensrose@.hotmail.com> wrote in message news:%23Tec6mLmIHA.3780@.TK2MSFTNGP06.phx.gbl...
I have two jobs configured to e-mail myself on completion, but I am not currently receiving these notifications.
The SQL Agent is configured to use database mail, with a specific private profile. Of note is that the button to "Test" the mail profile is grayed out on the Alert System tab. When I execute sp_send_dbmail using the e-mail address from the operator and use the profile name defined in the SQL Agent properties, I receive an e-mail. However the jobs themselves are not sending me any e-mail.
I've also confirmed the correct name of the profile in the sysmail_profile table. The e-mail does not appear in sysmail_mailitems and sysmail_send_retries is empty. So the job notification is not getting queued.
Any ideas?
Thank-you,
Rubens
|||Yes, I was able to send an e-mail using T-SQL when logged in with the account the SQL Server service / agent was using. I was able to fix the problem, although I am not sure if it's a permanent solution.
The job operator e-mail address was a distribution list on Exchange. I changed the e-mail address to include my own, ran the job, and one alert came through fine. Then I sent it only to my account, again, worked fine. I then retyped the operator e-mail address back in of the distribution list and it succeeded. The one thing I am positive of is that the e-mail address was correct right from the start because I use the same e-mail address in the Pagers section. This was and is identical to what was in the E-mail name of the operator.
Does this sound like a bug? I guess it's a little concerning considering one of the benefits of database mail is it being SMTP based and supposed to be a lot more reliable.
Thanks,
Rubens
"Rubens" <rubensrose@.hotmail.com> wrote in message news:e1A60tPmIHA.2268@.TK2MSFTNGP02.phx.gbl...
Hmmm, come to think of it, no I wasn't. I will have to test that when back in the office tomorrow. I will let you know.
Thanks John.
Rubens
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:erH9oGOmIHA.1184@.TK2MSFTNGP04.phx.gbl...
Hi
And were you logged in as this account when you tested sp_send_dbmail?
John
"Rubens" <rubensrose@.hotmail.com> wrote in message news:%23A5C6DOmIHA.5084@.TK2MSFTNGP04.phx.gbl...
It is running under a domain account that is an administrator on the server, which is the same account the SQL Server service is running under (I haven't split them up and restricted it as is recommended).
Thank-you,
Rubens
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:O6%231lsNmIHA.1208@.TK2MSFTNGP05.phx.gbl...
Hi
What account is SQL Agent running under?
John
"Rubens" <rubensrose@.hotmail.com> wrote in message news:%23Tec6mLmIHA.3780@.TK2MSFTNGP06.phx.gbl...
I have two jobs configured to e-mail myself on completion, but I am not currently receiving these notifications.
The SQL Agent is configured to use database mail, with a specific private profile. Of note is that the button to "Test" the mail profile is grayed out on the Alert System tab. When I execute sp_send_dbmail using the e-mail address from the operator and use the profile name defined in the SQL Agent properties, I receive an e-mail. However the jobs themselves are not sending me any e-mail.
I've also confirmed the correct name of the profile in the sysmail_profile table. The e-mail does not appear in sysmail_mailitems and sysmail_send_retries is empty. So the job notification is not getting queued.
Any ideas?
Thank-you,
Rubens
|||I've just noticed another issue. I've created all my maintenance plans using the wizard. When I go into the "Reporting and Logging" section for the maintenance plan, I've selected the "Send report to an email recipient" check box, the recipient being the same one that the job alert sends to. However, I am not receiving the e-mail report. Instead, I am only getting an e-mail from the job saying it has completed. I don't get a report from the maintenance plan. I have also confirmed that the logging file is created in a folder on the server.
Any idea why I am not getting this report in an e-mail?
Thank-you,
Rubens
"Rubens" <rubensrose@.hotmail.com> wrote in message news:eC7QYMXmIHA.5684@.TK2MSFTNGP03.phx.gbl...
Yes, I was able to send an e-mail using T-SQL when logged in with the account the SQL Server service / agent was using. I was able to fix the problem, although I am not sure if it's a permanent solution.
The job operator e-mail address was a distribution list on Exchange. I changed the e-mail address to include my own, ran the job, and one alert came through fine. Then I sent it only to my account, again, worked fine. I then retyped the operator e-mail address back in of the distribution list and it succeeded. The one thing I am positive of is that the e-mail address was correct right from the start because I use the same e-mail address in the Pagers section. This was and is identical to what was in the E-mail name of the operator.
Does this sound like a bug? I guess it's a little concerning considering one of the benefits of database mail is it being SMTP based and supposed to be a lot more reliable.
Thanks,
Rubens
"Rubens" <rubensrose@.hotmail.com> wrote in message news:e1A60tPmIHA.2268@.TK2MSFTNGP02.phx.gbl...
Hmmm, come to think of it, no I wasn't. I will have to test that when back in the office tomorrow. I will let you know.
Thanks John.
Rubens
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:erH9oGOmIHA.1184@.TK2MSFTNGP04.phx.gbl...
Hi
And were you logged in as this account when you tested sp_send_dbmail?
John
"Rubens" <rubensrose@.hotmail.com> wrote in message news:%23A5C6DOmIHA.5084@.TK2MSFTNGP04.phx.gbl...
It is running under a domain account that is an administrator on the server, which is the same account the SQL Server service is running under (I haven't split them up and restricted it as is recommended).
Thank-you,
Rubens
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:O6%231lsNmIHA.1208@.TK2MSFTNGP05.phx.gbl...
Hi
What account is SQL Agent running under?
John
"Rubens" <rubensrose@.hotmail.com> wrote in message news:%23Tec6mLmIHA.3780@.TK2MSFTNGP06.phx.gbl...
I have two jobs configured to e-mail myself on completion, but I am not currently receiving these notifications.
The SQL Agent is configured to use database mail, with a specific private profile. Of note is that the button to "Test" the mail profile is grayed out on the Alert System tab. When I execute sp_send_dbmail using the e-mail address from the operator and use the profile name defined in the SQL Agent properties, I receive an e-mail. However the jobs themselves are not sending me any e-mail.
I've also confirmed the correct name of the profile in the sysmail_profile table. The e-mail does not appear in sysmail_mailitems and sysmail_send_retries is empty. So the job notification is not getting queued.
Any ideas?
Thank-you,
Rubens

Database Mail errors

This summary is not available. Please click here to view the post.

Database Mail errors

Hi,
I thought that I had configured Database Mail correctly but I am getting
this message:-
Message
The mail could not be sent to the recipients because of the mail server
failure. (Sending Mail using Account 1 (2006-08-30T11:36:43). Exception
Message: Cannot send mails to mail server. (Failure sending mail.).
)
What is causing this?
Thanks
ChrisI am also getting this error on my Win2k3 Server with Sql 2005
installed. I can ping mail.xxx.com, I haven't tried using OE or
Outlook, but this is one server on a whole network of people sending
mail via SMTP to our WIN2kServer running 3rd party mail server software
that allows relay from internal IP's. I also have another SQL server
with the same problem. This leads me to think that there is something
on the Server's that I am not doing properly. I see a lot of post for
people who have resorted to using 3rd party apps and CDO because this
apparently doens't work for a lot of people... Any ideas or help?
Thanks all
Chris Wood wrote:
> Hi,
> I thought that I had configured Database Mail correctly but I am getting
> this message:-
> Message
> The mail could not be sent to the recipients because of the mail server
> failure. (Sending Mail using Account 1 (2006-08-30T11:36:43). Exception
> Message: Cannot send mails to mail server. (Failure sending mail.).
> )
> What is causing this?
> Thanks
> Chris|||John,
We are trying to use the SQL Server machine itself to send the mail and this
does not work. If we do not want to use our Exchange server then do we need
a server dedicated to just relay SMTP traffic?
Thanks
Chris
<john.dean@.gmail.com> wrote in message
news:1157553993.669998.218770@.m79g2000cwm.googlegroups.com...
>I am also getting this error on my Win2k3 Server with Sql 2005
> installed. I can ping mail.xxx.com, I haven't tried using OE or
> Outlook, but this is one server on a whole network of people sending
> mail via SMTP to our WIN2kServer running 3rd party mail server software
> that allows relay from internal IP's. I also have another SQL server
> with the same problem. This leads me to think that there is something
> on the Server's that I am not doing properly. I see a lot of post for
> people who have resorted to using 3rd party apps and CDO because this
> apparently doens't work for a lot of people... Any ideas or help?
> Thanks all
>
> Chris Wood wrote:
>

Database mail configuration

I have configured database mail using the wizard, but when I try to send a test message I receive the following error:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 Exception Message: Cannot send mails to mail server. (Failure sending mail.)

Is there some more configuration I must do? I have enabled mail using the surface area configurator.

Thanks

I would try using xpsmtp mail and forget standard SQL mail...it is a pain in the rear. You can get it (yes it is free) here http://www.sqldev.net/xp/xpsmtp.htm

It is easy to set up and use and has teriffic documentation.

I know this is not an answer to your question but I hope this helps some.

Regards,

Brad Feaker
|||We are using SQL Server 2005 and its database mail option, not the old SQL mail that 7 and 2000 use.|||Sorry about that - I should have noticed your mention of the Surface Area Configurator. My bad! I don't have any experience setting up SQL Mail in 2005 yet. Hope you get an answer soon. No one seems to be able to answer my question either...Good luck!!!
|||

Hi Tim,

I had the same problem as yours and I solved it configuring the Relay options of my SMTP server.

HTH,

Database Mail

MSSQL 2005 9.00.1399.06
Win2003 server
I have configured database Mail, and tried the Sent Test E-Mail
and it's working, sp_send_dbmail is working too
When I do some testing on jobs and Notification/Operators on a specific
job I get theese message in the error log:
Date 05.05.2006 12:20:00
Log SQL Agent (Current - 05.05.2006 12:20:00)
Message
[264] An attempt was made to send an email when no email session has bee
n
established
and in the job history I get theese error:
Date 05.05.2006 12:30:00
Log Job History (Replication agents checkup)
Step ID 0
Server RDASP21
Job Name Replication agents checkup
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job succeeded. The Job was invoked by Schedule 2 (Replication agent
schedule.). The last step to run was step 1 (Run agent.). NOTE: Failed to
notify 'Roger Nygrd' via email.
What is wrong in my setup?
-Roger NygrdThis should be fixed if you update to SP1. At least it was for me.
"Roger Nygrd" <roger@.askit.no> wrote in message
news:125maitjqfkc493@.corp.supernews.com...
> MSSQL 2005 9.00.1399.06
> Win2003 server
> I have configured database Mail, and tried the Sent Test E-Mail
> and it's working, sp_send_dbmail is working too
> When I do some testing on jobs and Notification/Operators on a specific
> job I get theese message in the error log:
> Date 05.05.2006 12:20:00
> Log SQL Agent (Current - 05.05.2006 12:20:00)
> Message
> [264] An attempt was made to send an email when no email session has b
een
> established
>
> and in the job history I get theese error:
> Date 05.05.2006 12:30:00
> Log Job History (Replication agents checkup)
> Step ID 0
> Server RDASP21
> Job Name Replication agents checkup
> Step Name (Job outcome)
> Duration 00:00:00
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job succeeded. The Job was invoked by Schedule 2 (Replication agent
> schedule.). The last step to run was step 1 (Run agent.). NOTE: Failed
> to notify 'Roger Nygrd' via email.
> What is wrong in my setup?
> -Roger Nygrd
>|||I have applied the SP1 to MSSQL2005(Microsoft SQL Server 2005 - 9.00.2047.00
(Intel X86)) but the problem still persists. Any idea? Thanks in advnace!
"Michael D'Angelo" wrote:

> This should be fixed if you update to SP1. At least it was for me.
> "Roger Nyg?rd" <roger@.askit.no> wrote in message
> news:125maitjqfkc493@.corp.supernews.com...
>
>|||I forgot to restart the SQLAgent after configuring 'Alert System'. Thanks a
ll
"stephanie" wrote:
[vbcol=seagreen]
> I have applied the SP1 to MSSQL2005(Microsoft SQL Server 2005 - 9.00.2047.
00
> (Intel X86)) but the problem still persists. Any idea? Thanks in advnace
!
> "Michael D'Angelo" wrote:
>

Database Mail

MSSQL 2005 9.00.1399.06
Win2003 server
I have configured database Mail, and tried the Sent Test E-Mail
and it's working, sp_send_dbmail is working too
When I do some testing on jobs and Notification/Operators on a specific
job I get theese message in the error log:
Date 05.05.2006 12:20:00
Log SQL Agent (Current - 05.05.2006 12:20:00)
Message
[264] An attempt was made to send an email when no email session has been
established
and in the job history I get theese error:
Date 05.05.2006 12:30:00
Log Job History (Replication agents checkup)
Step ID 0
Server RDASP21
Job Name Replication agents checkup
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job succeeded. The Job was invoked by Schedule 2 (Replication agent
schedule.). The last step to run was step 1 (Run agent.). NOTE: Failed to
notify 'Roger Nygård' via email.
What is wrong in my setup?
-Roger NygårdThis should be fixed if you update to SP1. At least it was for me.
"Roger Nygård" <roger@.askit.no> wrote in message
news:125maitjqfkc493@.corp.supernews.com...
> MSSQL 2005 9.00.1399.06
> Win2003 server
> I have configured database Mail, and tried the Sent Test E-Mail
> and it's working, sp_send_dbmail is working too
> When I do some testing on jobs and Notification/Operators on a specific
> job I get theese message in the error log:
> Date 05.05.2006 12:20:00
> Log SQL Agent (Current - 05.05.2006 12:20:00)
> Message
> [264] An attempt was made to send an email when no email session has been
> established
>
> and in the job history I get theese error:
> Date 05.05.2006 12:30:00
> Log Job History (Replication agents checkup)
> Step ID 0
> Server RDASP21
> Job Name Replication agents checkup
> Step Name (Job outcome)
> Duration 00:00:00
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job succeeded. The Job was invoked by Schedule 2 (Replication agent
> schedule.). The last step to run was step 1 (Run agent.). NOTE: Failed
> to notify 'Roger Nygård' via email.
> What is wrong in my setup?
> -Roger Nygård
>|||I have applied the SP1 to MSSQL2005(Microsoft SQL Server 2005 - 9.00.2047.00
(Intel X86)) but the problem still persists. Any idea? Thanks in advnace!
"Michael D'Angelo" wrote:
> This should be fixed if you update to SP1. At least it was for me.
> "Roger Nygård" <roger@.askit.no> wrote in message
> news:125maitjqfkc493@.corp.supernews.com...
> > MSSQL 2005 9.00.1399.06
> > Win2003 server
> >
> > I have configured database Mail, and tried the Sent Test E-Mail
> > and it's working, sp_send_dbmail is working too
> >
> > When I do some testing on jobs and Notification/Operators on a specific
> > job I get theese message in the error log:
> > Date 05.05.2006 12:20:00
> > Log SQL Agent (Current - 05.05.2006 12:20:00)
> >
> > Message
> > [264] An attempt was made to send an email when no email session has been
> > established
> >
> >
> >
> > and in the job history I get theese error:
> > Date 05.05.2006 12:30:00
> > Log Job History (Replication agents checkup)
> >
> > Step ID 0
> > Server RDASP21
> > Job Name Replication agents checkup
> > Step Name (Job outcome)
> > Duration 00:00:00
> > Sql Severity 0
> > Sql Message ID 0
> > Operator Emailed
> > Operator Net sent
> > Operator Paged
> > Retries Attempted 0
> >
> > Message
> > The job succeeded. The Job was invoked by Schedule 2 (Replication agent
> > schedule.). The last step to run was step 1 (Run agent.). NOTE: Failed
> > to notify 'Roger Nygård' via email.
> >
> > What is wrong in my setup?
> >
> > -Roger Nygård
> >
>
>|||I forgot to restart the SQLAgent after configuring 'Alert System'. Thanks all
"stephanie" wrote:
> I have applied the SP1 to MSSQL2005(Microsoft SQL Server 2005 - 9.00.2047.00
> (Intel X86)) but the problem still persists. Any idea? Thanks in advnace!
> "Michael D'Angelo" wrote:
> > This should be fixed if you update to SP1. At least it was for me.
> >
> > "Roger Nygård" <roger@.askit.no> wrote in message
> > news:125maitjqfkc493@.corp.supernews.com...
> > > MSSQL 2005 9.00.1399.06
> > > Win2003 server
> > >
> > > I have configured database Mail, and tried the Sent Test E-Mail
> > > and it's working, sp_send_dbmail is working too
> > >
> > > When I do some testing on jobs and Notification/Operators on a specific
> > > job I get theese message in the error log:
> > > Date 05.05.2006 12:20:00
> > > Log SQL Agent (Current - 05.05.2006 12:20:00)
> > >
> > > Message
> > > [264] An attempt was made to send an email when no email session has been
> > > established
> > >
> > >
> > >
> > > and in the job history I get theese error:
> > > Date 05.05.2006 12:30:00
> > > Log Job History (Replication agents checkup)
> > >
> > > Step ID 0
> > > Server RDASP21
> > > Job Name Replication agents checkup
> > > Step Name (Job outcome)
> > > Duration 00:00:00
> > > Sql Severity 0
> > > Sql Message ID 0
> > > Operator Emailed
> > > Operator Net sent
> > > Operator Paged
> > > Retries Attempted 0
> > >
> > > Message
> > > The job succeeded. The Job was invoked by Schedule 2 (Replication agent
> > > schedule.). The last step to run was step 1 (Run agent.). NOTE: Failed
> > > to notify 'Roger Nygård' via email.
> > >
> > > What is wrong in my setup?
> > >
> > > -Roger Nygård
> > >
> >
> >
> >

Database Mail

All,
I'm trying to test sending mail using SQL Server 2005. I have configured
database mail but I'm getting the following error:
The mail could not be sent to the recipients because of the mail server
failure. (Sending Mail using Account 10 (2007-02-13T12:38:53). Exception
Message: Could not connect to mail server. (An established connection was
aborted by the software in your host machine).
Has anyone ran into this. I have been able to setup the same thing on my
local machine and it works fine. Any suggestions would be appreciated.
JamalHi,
try sending a mail using the information entered in the GUI using the
telnet command. there should be a more specific error than the actual
posted.
http://www.activexperts.com/activemail/telnet/
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Jamal,
Any luck finding a solution? I'm getting the same error message.
Ron
"Jamal" wrote:
> All,
> I'm trying to test sending mail using SQL Server 2005. I have configured
> database mail but I'm getting the following error:
> The mail could not be sent to the recipients because of the mail server
> failure. (Sending Mail using Account 10 (2007-02-13T12:38:53). Exception
> Message: Could not connect to mail server. (An established connection was
> aborted by the software in your host machine).
> Has anyone ran into this. I have been able to setup the same thing on my
> local machine and it works fine. Any suggestions would be appreciated.
> Jamal
>
>|||Problem turned out to be that port 25 was blocked. In the VirusConsole
=> Access Protection => Prevent mass mailing worms from sending mail
Ports 25
Added these to the exception list
SQLWb.exe,MsDtsSrvr.exe,SQLAgent90.exe,DtsDebugHost.exe,DatabaseMail90.exe