• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1719
  • Last Modified:

sql mail error

I have created a profile called keir in management/database mail

however it is still giving the following error

Msg 15281, Level 16, State 1, Procedure xp_sendmail, Line 1
SQL Server blocked access to procedure 'sys.xp_sendmail' of component 'SQL Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'SQL Mail XPs' by using sp_configure. For more information about enabling 'SQL Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.

declare @myQuery varchar(8000), @eSubject varchar(254)
Set @myQuery='SELECT T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120) Date, sum(T0.[Quantity]) Total_fixes,
 sum(T0.LineTotal) Invoicing_total, sum(T3.[U_IIS_TIME]) TaskTime_Total, T0.[u_iis_INS2], T0.[u_IIS_INS3]
 FROM DLN1 T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
 INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry
 left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
 WHERE T2.[U_IIS_JTYPE] = ''6'' and T0.[U_iis_ddate] >= (CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) - 1)
 and T0.[U_iis_ddate] < CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))
 group by T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120), T0.[u_iis_INS2], T0.[u_IIS_INS3]'
 
Set @eSubject='REPORT: Completions per engineer'
 
            SET @myQuery = 'Select Body from TmpEmail'
            Create Table TmpEmail (Body varchar(254))
            Insert into TmpEmail Values('Hi All')
            Insert into TmpEmail Values('')
            Insert into TmpEmail Values('Please find attached the latest Invoice reports generated by the SQL Server.')
            Insert into TmpEmail Values('')
            Insert into TmpEmail Values('Thanks.')
            Insert into TmpEmail Values('')
            Insert into TmpEmail Values('MSSQL Database Server')
            exec master..xp_sendmail @recipients='mail.co.uk',
            @query=@myQuery, @no_header='TRUE',
            @copy_recipients='mySecondEmail?whoknows.com',
            @subject=@eSubject,
            @dbuse='advanced'
 
      Drop Table TmpEmail

Open in new window

0
KeirMcCann
Asked:
KeirMcCann
1 Solution
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
You didn't really mention what you have done to address the error message though. The ability to execute the mail sprocs is turned off. (thus the message)A system administrator can enable the use of 'SQL Mail XPs' by using sp_configure. For more information about enabling 'SQL Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.

Have you as an administrator logged into sql server and followed the instructions to enable the 'SQL Mail XPs'?  You won't get it to work until you do.

Setting up an account to run these would appear to be fine as well, make sure the account has all the priviledges necessary to do this but, the most important piece is to enable the 'SQL Mail XPs'.
Dan
0
 
KeirMcCannAuthor Commented:
How could i run this code using the Database Mail XPs  are than xp_sendmail which is the outdated version i think??? I am required to install microsoft outlook on the SAP server for this query to work properly however i would much rather it used the database mail xps which i can configure

hope this makes sense!!

Thanks



declare @myQuery varchar(8000), @eSubject varchar(254)
Set @myQuery='SELECT T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120) Date, sum(T0.[Quantity]) Total_fixes,
 sum(T0.LineTotal) Invoicing_total, sum(T3.[U_IIS_TIME]) TaskTime_Total, T0.[u_iis_INS2], T0.[u_IIS_INS3]
 FROM DLN1 T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
 INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry
 left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
 WHERE T2.[U_IIS_JTYPE] = ''6'' and T0.[U_iis_ddate] >= (CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) - 1)
 and T0.[U_iis_ddate] < CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))
 group by T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120), T0.[u_iis_INS2], T0.[u_IIS_INS3]'
 
Set @eSubject='REPORT: Completions per engineer'
 
            SET @myQuery = 'Select Body from TmpEmail'
            Create Table TmpEmail (Body varchar(254))
            Insert into TmpEmail Values('Hi All')
            Insert into TmpEmail Values('')
            Insert into TmpEmail Values('Please find attached the latest Invoice reports generated by the SQL Server.')
            Insert into TmpEmail Values('')
            Insert into TmpEmail Values('Thanks.')
            Insert into TmpEmail Values('')
            Insert into TmpEmail Values('MSSQL Database Server')
            exec master..xp_sendmail @recipients='mail.co.uk',
            @query=@myQuery, @no_header='TRUE',
            @copy_recipients='mySecondEmail?whoknows.com',
            @subject=@eSubject,
            @dbuse='advanced'
 
      Drop Table TmpEmail

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now