Database Mirroring Problems - Can't drop database

Doing some mirroring testing and have run into problems.
Firstly, the database failed over to the secondary server. This is fine
and I wanted to manually fail back to the Primary but the databases
would not synchronize. The database is < 4mb and I let it try to
synchronize for an hour, it had clearly got itself into a mess. I then
deleted the "restoring database" on the Primary server and restored
from backup, intending to setup mirroring again. The restore was fine.
However now on the secondary server the database thinks it is in the
"principal disconnected" state.
If I try and delete it, I cannot drop the database because it is
currently in use. (No user connections are active.)
"Alter database X set partner off" fails as it says it cannot connect
to the mirror.
Any "alter database" variation (like setting it to single user), will
fail as SQL thinks the database "is involved in a database mirroring
session".
It fails if I try and detach it with - 'only user processes can be
killed.
I can't think of anything else to try. So how can I drop this database
and get it going again?
TIA,
S.
Hi
Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
John
"Calculated" wrote:

> Doing some mirroring testing and have run into problems.
> Firstly, the database failed over to the secondary server. This is fine
> and I wanted to manually fail back to the Primary but the databases
> would not synchronize. The database is < 4mb and I let it try to
> synchronize for an hour, it had clearly got itself into a mess. I then
> deleted the "restoring database" on the Primary server and restored
> from backup, intending to setup mirroring again. The restore was fine.
> However now on the secondary server the database thinks it is in the
> "principal disconnected" state.
> If I try and delete it, I cannot drop the database because it is
> currently in use. (No user connections are active.)
> "Alter database X set partner off" fails as it says it cannot connect
> to the mirror.
> Any "alter database" variation (like setting it to single user), will
> fail as SQL thinks the database "is involved in a database mirroring
> session".
> It fails if I try and detach it with - 'only user processes can be
> killed.
> I can't think of anything else to try. So how can I drop this database
> and get it going again?
> TIA,
> S.
>
|||John Bell wrote:
> Hi
> Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
> John
>
Thanks for the reply John. Yes I have. The command returns:
Msg 1404, Level 16, State 11, Line 3
The command failed because the database mirror is busy. Reissue the
command later.
Which is not helpful as the "mirror" in this case is a new database
sans any mirroring. This error also occurs if I attempt to pause the
mirroring in the Management Studio.
|||Have now been able to delete the database - restarting the SQL service
on the secondary database put the database into a state where it was
able to be deleted. I didn't really want to do this but it has worked
ok and the database is no more.

Database Mirroring Problems - Can't drop database

Doing some mirroring testing and have run into problems.
Firstly, the database failed over to the secondary server. This is fine
and I wanted to manually fail back to the Primary but the databases
would not synchronize. The database is < 4mb and I let it try to
synchronize for an hour, it had clearly got itself into a mess. I then
deleted the "restoring database" on the Primary server and restored
from backup, intending to setup mirroring again. The restore was fine.
However now on the secondary server the database thinks it is in the
"principal disconnected" state.
If I try and delete it, I cannot drop the database because it is
currently in use. (No user connections are active.)
"Alter database X set partner off" fails as it says it cannot connect
to the mirror.
Any "alter database" variation (like setting it to single user), will
fail as SQL thinks the database "is involved in a database mirroring
session".
It fails if I try and detach it with - 'only user processes can be
killed.
I can't think of anything else to try. So how can I drop this database
and get it going again?
TIA,
S.Hi
Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
John
"Calculated" wrote:

> Doing some mirroring testing and have run into problems.
> Firstly, the database failed over to the secondary server. This is fine
> and I wanted to manually fail back to the Primary but the databases
> would not synchronize. The database is < 4mb and I let it try to
> synchronize for an hour, it had clearly got itself into a mess. I then
> deleted the "restoring database" on the Primary server and restored
> from backup, intending to setup mirroring again. The restore was fine.
> However now on the secondary server the database thinks it is in the
> "principal disconnected" state.
> If I try and delete it, I cannot drop the database because it is
> currently in use. (No user connections are active.)
> "Alter database X set partner off" fails as it says it cannot connect
> to the mirror.
> Any "alter database" variation (like setting it to single user), will
> fail as SQL thinks the database "is involved in a database mirroring
> session".
> It fails if I try and detach it with - 'only user processes can be
> killed.
> I can't think of anything else to try. So how can I drop this database
> and get it going again?
> TIA,
> S.
>|||John Bell wrote:
> Hi
> Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
> John
>
Thanks for the reply John. Yes I have. The command returns:
Msg 1404, Level 16, State 11, Line 3
The command failed because the database mirror is busy. Reissue the
command later.
Which is not helpful as the "mirror" in this case is a new database
sans any mirroring. This error also occurs if I attempt to pause the
mirroring in the Management Studio.|||Have now been able to delete the database - restarting the SQL service
on the secondary database put the database into a state where it was
able to be deleted. I didn't really want to do this but it has worked
ok and the database is no more.

Database Mirroring Problems - Can't drop database

Doing some mirroring testing and have run into problems.
Firstly, the database failed over to the secondary server. This is fine
and I wanted to manually fail back to the Primary but the databases
would not synchronize. The database is < 4mb and I let it try to
synchronize for an hour, it had clearly got itself into a mess. I then
deleted the "restoring database" on the Primary server and restored
from backup, intending to setup mirroring again. The restore was fine.
However now on the secondary server the database thinks it is in the
"principal disconnected" state.
If I try and delete it, I cannot drop the database because it is
currently in use. (No user connections are active.)
"Alter database X set partner off" fails as it says it cannot connect
to the mirror.
Any "alter database" variation (like setting it to single user), will
fail as SQL thinks the database "is involved in a database mirroring
session".
It fails if I try and detach it with - 'only user processes can be
killed.
I can't think of anything else to try. So how can I drop this database
and get it going again?
TIA,
S.Hi
Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
John
"Calculated" wrote:
> Doing some mirroring testing and have run into problems.
> Firstly, the database failed over to the secondary server. This is fine
> and I wanted to manually fail back to the Primary but the databases
> would not synchronize. The database is < 4mb and I let it try to
> synchronize for an hour, it had clearly got itself into a mess. I then
> deleted the "restoring database" on the Primary server and restored
> from backup, intending to setup mirroring again. The restore was fine.
> However now on the secondary server the database thinks it is in the
> "principal disconnected" state.
> If I try and delete it, I cannot drop the database because it is
> currently in use. (No user connections are active.)
> "Alter database X set partner off" fails as it says it cannot connect
> to the mirror.
> Any "alter database" variation (like setting it to single user), will
> fail as SQL thinks the database "is involved in a database mirroring
> session".
> It fails if I try and detach it with - 'only user processes can be
> killed.
> I can't think of anything else to try. So how can I drop this database
> and get it going again?
> TIA,
> S.
>|||John Bell wrote:
> Hi
> Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
> John
>
Thanks for the reply John. Yes I have. The command returns:
Msg 1404, Level 16, State 11, Line 3
The command failed because the database mirror is busy. Reissue the
command later.
Which is not helpful as the "mirror" in this case is a new database
sans any mirroring. This error also occurs if I attempt to pause the
mirroring in the Management Studio.|||Have now been able to delete the database - restarting the SQL service
on the secondary database put the database into a state where it was
able to be deleted. I didn't really want to do this but it has worked
ok and the database is no more.

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 Maintenance Plan Setup Failed

I attempted to setup a Database Maintenance Plan in SQL Server 2005 for a nightly full backup of all system & user databases on my development server. When I tried to save the job, the error, "Create failed for job x" popped up. Any suggestions/ideas? No errors in event log.

This isn't really a question about setup, so I've moved your post to the Tools forum where you're more likely to get help.

Paul

|||

Can you please check whether job with that name already exists ? If that is the case please try with a new name for the mainteance plan.

Thanks,

Gops Dwarak

database maint plan failed

Hi ,
i am running sql server 2000 (evaluation copy) on Win 2003 server and i
just set up a Database Maint Plan to baackup my system database
of all the 4 jobs created only the optimization job suceed and rest such
as txn log bkup ,db bkup failed with the following errors :
sqlmaint.exe failed (SQLSTATE 42000) (Error 22029) the step failed
however, i did go to the location where the files were supposed to be saved
to and i found there are some files (but i did not check if the data is ok)
what could be the reason ? appreciate any advice
tk s& rdgsSpecify a report file for the maint plan and check that report file for specific error messages
after an execution of the job(s).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:083F3AEB-13DB-474C-B1BC-EC78501D139D@.microsoft.com...
> Hi ,
> i am running sql server 2000 (evaluation copy) on Win 2003 server and i
> just set up a Database Maint Plan to baackup my system database
> of all the 4 jobs created only the optimization job suceed and rest such
> as txn log bkup ,db bkup failed with the following errors :
> sqlmaint.exe failed (SQLSTATE 42000) (Error 22029) the step failed
> however, i did go to the location where the files were supposed to be saved
> to and i found there are some files (but i did not check if the data is ok)
> what could be the reason ? appreciate any advice
> tk s& rdgs|||MS says it's related to SP but mine is with the latest sp3a but the problem
still there...
http://support.microsoft.com/kb/295022/EN-US/
MS guys, can you guys please help
"Tibor Karaszi" wrote:
> Specify a report file for the maint plan and check that report file for specific error messages
> after an execution of the job(s).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:083F3AEB-13DB-474C-B1BC-EC78501D139D@.microsoft.com...
> > Hi ,
> >
> > i am running sql server 2000 (evaluation copy) on Win 2003 server and i
> > just set up a Database Maint Plan to baackup my system database
> >
> > of all the 4 jobs created only the optimization job suceed and rest such
> > as txn log bkup ,db bkup failed with the following errors :
> >
> > sqlmaint.exe failed (SQLSTATE 42000) (Error 22029) the step failed
> >
> > however, i did go to the location where the files were supposed to be saved
> > to and i found there are some files (but i did not check if the data is ok)
> >
> > what could be the reason ? appreciate any advice
> >
> > tk s& rdgs
>
>|||That article is for MSDE. You are running eval edition. So the article doesn't apply to you.
Do don't you do as suggested? Specify a report file so we have an error message to go on. "My car
doesn't work. What is the problem." Pretty hard to answer. Inside the report file, you find detailed
error messages, which will tell us why the job failed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:C9C496F5-5DEA-433A-9C1E-6DC76E21659D@.microsoft.com...
> MS says it's related to SP but mine is with the latest sp3a but the problem
> still there...
> http://support.microsoft.com/kb/295022/EN-US/
> MS guys, can you guys please help
> "Tibor Karaszi" wrote:
>> Specify a report file for the maint plan and check that report file for specific error messages
>> after an execution of the job(s).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:083F3AEB-13DB-474C-B1BC-EC78501D139D@.microsoft.com...
>> > Hi ,
>> >
>> > i am running sql server 2000 (evaluation copy) on Win 2003 server and i
>> > just set up a Database Maint Plan to baackup my system database
>> >
>> > of all the 4 jobs created only the optimization job suceed and rest such
>> > as txn log bkup ,db bkup failed with the following errors :
>> >
>> > sqlmaint.exe failed (SQLSTATE 42000) (Error 22029) the step failed
>> >
>> > however, i did go to the location where the files were supposed to be saved
>> > to and i found there are some files (but i did not check if the data is ok)
>> >
>> > what could be the reason ? appreciate any advice
>> >
>> > tk s& rdgs
>>

database maint plan failed

Hi ,
i am running sql server 2000 (evaluation copy) on Win 2003 server and i
just set up a Database Maint Plan to baackup my system database
of all the 4 jobs created only the optimization job suceed and rest such
as txn log bkup ,db bkup failed with the following errors :
sqlmaint.exe failed (SQLSTATE 42000) (Error 22029) the step failed
however, i did go to the location where the files were supposed to be saved
to and i found there are some files (but i did not check if the data is ok)
what could be the reason ? appreciate any advice
tk s& rdgs
Specify a report file for the maint plan and check that report file for specific error messages
after an execution of the job(s).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:083F3AEB-13DB-474C-B1BC-EC78501D139D@.microsoft.com...
> Hi ,
> i am running sql server 2000 (evaluation copy) on Win 2003 server and i
> just set up a Database Maint Plan to baackup my system database
> of all the 4 jobs created only the optimization job suceed and rest such
> as txn log bkup ,db bkup failed with the following errors :
> sqlmaint.exe failed (SQLSTATE 42000) (Error 22029) the step failed
> however, i did go to the location where the files were supposed to be saved
> to and i found there are some files (but i did not check if the data is ok)
> what could be the reason ? appreciate any advice
> tk s& rdgs
|||MS says it's related to SP but mine is with the latest sp3a but the problem
still there...
http://support.microsoft.com/kb/295022/EN-US/
MS guys, can you guys please help
"Tibor Karaszi" wrote:

> Specify a report file for the maint plan and check that report file for specific error messages
> after an execution of the job(s).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:083F3AEB-13DB-474C-B1BC-EC78501D139D@.microsoft.com...
>
>
|||That article is for MSDE. You are running eval edition. So the article doesn't apply to you.
Do don't you do as suggested? Specify a report file so we have an error message to go on. "My car
doesn't work. What is the problem." Pretty hard to answer. Inside the report file, you find detailed
error messages, which will tell us why the job failed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:C9C496F5-5DEA-433A-9C1E-6DC76E21659D@.microsoft.com...[vbcol=seagreen]
> MS says it's related to SP but mine is with the latest sp3a but the problem
> still there...
> http://support.microsoft.com/kb/295022/EN-US/
> MS guys, can you guys please help
> "Tibor Karaszi" wrote:

database maint plan failed

Hi ,
i am running sql server 2000 (evaluation copy) on Win 2003 server and i
just set up a Database Maint Plan to baackup my system database
of all the 4 jobs created only the optimization job suceed and rest such
as txn log bkup ,db bkup failed with the following errors :
sqlmaint.exe failed (SQLSTATE 42000) (Error 22029) the step failed
however, i did go to the location where the files were supposed to be saved
to and i found there are some files (but i did not check if the data is ok)
what could be the reason ? appreciate any advice
tk s& rdgsSpecify a report file for the maint plan and check that report file for spec
ific error messages
after an execution of the job(s).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:083F3AEB-13DB-474C-B1BC-EC78501D139D@.microsoft.com...
> Hi ,
> i am running sql server 2000 (evaluation copy) on Win 2003 server and i
> just set up a Database Maint Plan to baackup my system database
> of all the 4 jobs created only the optimization job suceed and rest such
> as txn log bkup ,db bkup failed with the following errors :
> sqlmaint.exe failed (SQLSTATE 42000) (Error 22029) the step failed
> however, i did go to the location where the files were supposed to be save
d
> to and i found there are some files (but i did not check if the data is ok
)
> what could be the reason ? appreciate any advice
> tk s& rdgs|||MS says it's related to SP but mine is with the latest sp3a but the problem
still there...
http://support.microsoft.com/kb/295022/EN-US/
MS guys, can you guys please help
"Tibor Karaszi" wrote:

> Specify a report file for the maint plan and check that report file for sp
ecific error messages
> after an execution of the job(s).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:083F3AEB-13DB-474C-B1BC-EC78501D139D@.microsoft.com...
>
>|||That article is for MSDE. You are running eval edition. So the article doesn
't apply to you.
Do don't you do as suggested? Specify a report file so we have an error mess
age to go on. "My car
doesn't work. What is the problem." Pretty hard to answer. Inside the report
file, you find detailed
error messages, which will tell us why the job failed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:C9C496F5-5DEA-433A-9C1E-6DC76E21659D@.microsoft.com...[vbcol=seagreen]
> MS says it's related to SP but mine is with the latest sp3a but the proble
m
> still there...
> http://support.microsoft.com/kb/295022/EN-US/
> MS guys, can you guys please help
> "Tibor Karaszi" wrote:
>

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

I have a very similar issue.

When i execute the following everything work 100%.

EXEC msdb.dbo.sp_send_dbmail

@.profile_name = 'DBA Mail',

@.recipients = me@.whatever.co.za',

@.body = 'Test email from server using dbmail',

@.subject = 'Test email from server using dbmail',

@.query = 'SELECT TOP 1 * FROM TABLE A Order by AlertDTTM DESC' ;

As soon as i put this exact code in a trigger i get the following error:

Msg 22050, Level 16, State 1, Line 0

Error formatting query, probably invalid parameters

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478

Query execution failed: Error Intiailizing COM . CoInitialize failed with Hresult: 0x80010106

Anyone ?

|||

We have same problem for some time. it has been fixed for us after I made some changes to the security setting and rebooted the server..

We are running SQL 2005 on Windows 2003 SP1. Check the following link

http://support.microsoft.com/default.aspx/kb/926642/en-us

Faeed

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

I have a very similar issue.

When i execute the following everything work 100%.

EXEC msdb.dbo.sp_send_dbmail

@.profile_name = 'DBA Mail',

@.recipients = me@.whatever.co.za',

@.body = 'Test email from server using dbmail',

@.subject = 'Test email from server using dbmail',

@.query = 'SELECT TOP 1 * FROM TABLE A Order by AlertDTTM DESC' ;

As soon as i put this exact code in a trigger i get the following error:

Msg 22050, Level 16, State 1, Line 0

Error formatting query, probably invalid parameters

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478

Query execution failed: Error Intiailizing COM . CoInitialize failed with Hresult: 0x80010106

Anyone ?

|||

We have same problem for some time. it has been fixed for us after I made some changes to the security setting and rebooted the server..

We are running SQL 2005 on Windows 2003 SP1. Check the following link

http://support.microsoft.com/default.aspx/kb/926642/en-us

Faeed

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

I have a very similar issue.

When i execute the following everything work 100%.

EXEC msdb.dbo.sp_send_dbmail

@.profile_name = 'DBA Mail',

@.recipients = me@.whatever.co.za',

@.body = 'Test email from server using dbmail',

@.subject = 'Test email from server using dbmail',

@.query = 'SELECT TOP 1 * FROM TABLE A Order by AlertDTTM DESC' ;

As soon as i put this exact code in a trigger i get the following error:

Msg 22050, Level 16, State 1, Line 0

Error formatting query, probably invalid parameters

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478

Query execution failed: Error Intiailizing COM . CoInitialize failed with Hresult: 0x80010106

Anyone ?

database mail isn't working in the SQL Agent job notification

Hi,

Starting a few weeks ago I stopped receiving notifications when SQL Server Agent Jobs failed. The only error I see from the Error logs is the following: "[264] An attempt was made to send an email when no email session has been established"

I've followed all of the postings about stoping and restarting the Agent service, confirming that the database mail can send out test emails and making sure that database mail account name matches the actual SMTP account.

From my testing, I can successfully send out a test email by right-clicking on "Database Mail" - "Send Test E-Mail."

Here's my setup:
Active/Passive SQL Server 2005 Cluster.
32 bit
SS2005 SP1 installed

Database Mail worked for around 6 months and then stopped. Not sure if it's a permissions issue, patch issue or a configuration issue.

Any help resolving this would be great.

Sincerely,

Steve

Ditto for me. Standard SQL 2005 setup. Engine, AS, Integration, etc. with SP1. I've done all the troubleshooting steps detailed here because this was the closest related issue I found on the Web.
The stored proc sp_send_dbmail works. So I can craft a workaround.

I found the same issue posted under Technet here. Any ideas?

|||

Well this is a slap in the face.

My manager suggested I do a restart of the SQL Server Agent. I had tried that before but since he is the boss I did it again expecting the same results. Well as you might have guessed - its working. And not just on the development server but the production too. Can't figure it.

|||

Restarting has become my habit in debugging now thanks to this

"Restart" has been mentioned in couple threads though if you searched by your error message here (or Google)

It's really weird, SQL Mail stopped working randomly IMO, thank god this doesn't happen to SQL Server

|||

Steve,

I've seen this from time-to-time on our servers, as well. After a restart of SQL Server Agent, database mail will usually run fine for a month and then the error will come back again. It is frustrating especially when it happens on production boxes with replication, but at least it doesn't require a server reboot like SQL Mail always did when it locked up.

Database Mail Error Object reference not set to an instance of an object

We have SQL Server 2005 setup to send out emails on job failure... This am no emails went out for failed jobs and there were several (identical) log entries as shown below

---

Date 2/27/2007 4:30:04 AM
Log Database Mail (Database Mail Log)

Log ID 48
Process ID 2180

Mail Item ID 29
Last Modified 2/27/2007 4:30:04 AM
Last Modified By sa

Message
Object reference not set to an instance of an object.

---

How can I diagnose this? I searched the forums and google groups and couldnt find this error message in the context of the database mail component of SQLServer. Thanks in advance

PS: This has been moved from the SSIS forum ... The moderator there can delete that thread

got these yesterday am and today am ... any insights?
|||deleted the mail profile and all associated accounts and re-created them and that fixed the issue

Database Logon Failed SSRS

Hi All,

My Report Server works find but after apply SQL Server 2005 Service Pack2. I can't connect to ReportServer Database. When Trying to connect from SQL Server Management Studio i get following Page.

TITLE: Connect to Server

Cannot connect to VISTA.

ADDITIONAL INFORMATION:

Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<html>
<head>
<title>
SQL Server Reporting Services
</title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.3054.00" />
<meta name="HTTP Status" content="500" />
<meta name="ProductLocaleID" content="9" />
<meta name="CountryLocaleID" content="1033" />
<style>
BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black}
H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt}
LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline}
.ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray}
A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none}
A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLORFF3300; TEXT-DECORATION:underline}
A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none}
A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; colorFF3300; TEXT-DECORATION:underline}

</style>
</head><body bgcolor="white">
<h1>
Reporting Services Error<hr width="100%" size="1" color="silver" />
</h1><ul>
<li>The report server cannot open a connection to the report server database. The logon failed. (rsReportServerDatabaseLogonFailed) <a href="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsReportServerDatabaseLogonFailed&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.3054.00" target="_blank">Get Online Help</a></li><ul>
<li>For more information about this error navigate to the report server on the local server machine, or enable remote errors</li>
</ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span>
</ul>
</body>
--. (Microsoft.SqlServer.Management.UI.RSClient)

BUTTONS:

OK

Colud anyone help me to resolve this issue please?

Thank you very much.

Regards,

Balwant Patel

Try using the Reporting Services Configuration Utility (Database tab) to generate a database script and apply this script manually to your ReportServer database.|||

Did you just put on SP2? I had the same error very shortly after I put either SP2 or a hotfix (can't remember which), and it was fixed after a complete system restart and hasn't returned.

|||Make sure that the Service Accoutn the Reporting Server is running under has appropiate permissions to access the Report Server Database.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Teo,

Thank you very much for your response... I tried with Database script but it doesn't work.

Regards,

Balwant Patel.

|||

Hi gnieboer,

Thank you very much for response...I also restarted my system after apply SQL Server 2005 SP2 but I can't get rid of the problem.

Regards,

Balwant Patel

|||

Hi Suessmeyer,

I check permission on Report Server Database...It is fine

Regards,

Balwant Patel

|||Which account has been granted access to the Report Server database, the ReportService Account (Windows Service) or the ASP_Net Worker process ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi suessmeyer,

I m using Windows Service Account

Regards,

Balwant Patel

|||Make sure that the the ASPNET account is a member of the SQLServer2005ReportingServicesWebServiceUser$<computername> $MSSQLSERVER Windows group and NT Authority\System is a member of the SQLServer2005ReportServerUser$<computername>$MSSQLSERVER group.|||

Hi Teo,

Yes, ASPNET Account & Authority\System are members of above groups...

I dont know what's the problem with it...

Regards,

Balwant Patel.