Database Overwritten

Saturday, February 25, 2012

Database Mail question?

Hello All!

So I learned this cool thing today called Database Mail. I have a statement that will allow me to perform a query, and send a email with the results in a table form in a HTML format. What I would like to do is place more than one table. I have several tables to send, I don't want to send 5 emails. I want to send 1 email with 5 tables in it. Here some code!

TIA!

Rudy

DECLARE @.xml NVARCHAR(MAX)DECLARE @.body NVARCHAR(MAX)

BEGIN

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Active_Orders WHERE (Service_Date_Time <= dateadd( Month, -9, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired.</H1><body bgcolor=White><table border = 2><tr><th>I-9</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Archive_Orders WHERE (Service_Date_Time <= dateadd( Month, -2, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired1.</H1><body bgcolor=White><table border = 2><tr><th>Workers Comp</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@.recipients=N'jrudolf@.ikon.com',

@.body= @.body,

@.body_format = 'HTML',

@.Subject='HR Records to delete',

@.profile_name = 'Ikon'

end

YOu can just combine them. You can also use UNION/All to combine them into a single query.

DECLARE @.xml NVARCHAR(MAX)DECLARE @.body NVARCHAR(MAX)

BEGIN

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Active_Orders WHERE (Service_Date_Time <= dateadd( Month, -9, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

+CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Archive_Orders WHERE (Service_Date_Time <= dateadd( Month, -2, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired1.</H1><body bgcolor=White><table border = 2><tr><th>Workers Comp</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@.recipients=N'jrudolf@.ikon.com',

@.body= @.body,

@.body_format = 'HTML',

@.Subject='HR Records to delete',

@.profile_name = 'Ikon'

end

|||

Thanks Oj! It worked great!

Rudy

Database Mail question?

Hello All!

TIA!

Rudy

DECLARE @.xml NVARCHAR(MAX)DECLARE @.body NVARCHAR(MAX)

BEGIN

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Active_Orders WHERE (Service_Date_Time <= dateadd( Month, -9, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired.</H1><body bgcolor=White><table border = 2><tr><th>I-9</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Archive_Orders WHERE (Service_Date_Time <= dateadd( Month, -2, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired1.</H1><body bgcolor=White><table border = 2><tr><th>Workers Comp</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@.recipients=N'jrudolf@.ikon.com',

@.body= @.body,

@.body_format = 'HTML',

@.Subject='HR Records to delete',

@.profile_name = 'Ikon'

end

YOu can just combine them. You can also use UNION/All to combine them into a single query.

DECLARE @.xml NVARCHAR(MAX)DECLARE @.body NVARCHAR(MAX)

BEGIN

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Active_Orders WHERE (Service_Date_Time <= dateadd( Month, -9, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

+CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Archive_Orders WHERE (Service_Date_Time <= dateadd( Month, -2, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired1.</H1><body bgcolor=White><table border = 2><tr><th>Workers Comp</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@.recipients=N'jrudolf@.ikon.com',

@.body= @.body,

@.body_format = 'HTML',

@.Subject='HR Records to delete',

@.profile_name = 'Ikon'

end

|||

Thanks Oj! It worked great!

Rudy

Database Mail Problems / Job Notifications...

I've read an extensive bit about this problem in several forums and so far none of the information has helped me to determine if there is a bug in SQL 2005 x64 regardless of version or SP or if I just don't know how to do this correctly.

I can send a test email from db mail but when a job fails it does not send an email to an operator. I have monitored the IIS logs to see if it is trying to send and it is not. Database mail is enabled in Surface AC and in SQL Agent [one odd thing is that the 'test' button is only available on the SQL Agent Alerts page if I use SQL Mail]. No specific error happens (in the mail log, sql agent log, or application event log) when I try to send mail from a failed job. Mail accounts and credentials are valid. Please note that in the steps listed below, I have restarted the SQL Agent Service each time I make a change.

I've tried deleting and recreating the operators (they are enabled). I've tried using a mail server that uses anonymous authentication as well (I changed this back to one that uses basic authentication). The profiles were made default and public. (Though this shouldn't ever have to be done, as the account is a sysadmin) the SQLServer2005SQLAgentUser was explicitly added to the msdb DatabaseMail user role.

This query works, so I know that the job is not trying to execute the sp:

USE msdb ;

EXEC dbo.sp_notify_operator

@.profile_name = N'staging monitor',

@.name = N'Charles Evans',

@.subject = N'Test Notification',

@.body = N'Successfully emailed profile.' ;

This is happening in SQL 2005 x64 EE and x64 DE SP1 and SP2 respectively. Any thoughts on what else this could be? The same setup works fine in SQL 2005 SE x86 SP1.

Check the following article...

How to configure SQL Server Agent to send job status notifications and alert notifications in SQL Server 2005 64-bit editions

http://support.microsoft.com/kb/908360

|||So the only way is to use SQL Mail, instead of database mail?|||

SQL Server Agent is not integrated with Database Mail or with SQL Mail in Microsoft SQL Server 2005 64-bit editions.

Please read the article... you can setup mail using SMTP where SQL mail uses MAPI ...

|||

In SP0 this is true - according to the article:

"In SQL Server 2005 Service Pack 1 (SP1), you can use SQL Server Agent to interact with Database Mail as the mailing system on 64-bit operating systems."

I've made this work in our production system now with SP-1 EE x64, there was a permissions issue. However, things are not working in our staging environment SP-2 DE x64 with the same setup and account settings/permissions.

I'll keep posted if I figure out why not in staging.

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

Message Results...

Investigating the sproc itself shows...

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

Message Results...

Investigating the sproc itself shows...

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 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

Saturday, February 25, 2012

Database Mail question?

Database Mail question?

Database Mail Problems / Job Notifications...

database mail problem...Query execution failed: Error initializing COM

database mail problem...Query execution failed: Error initializing COM

database mail problem...Query execution failed: Error initializing COM

Database Mail Operator Question

Database Overwritten

Blog Archive

About Me