January 25, 2016 at 6:00 pm
Hi Experts,
Can't send dbmail with attachment as per below script.
if exists (select distinct 1 from xxxx.dbo.owtr where U_STN_Ref = 'POSNegINVCorr' and convert(varchar(10),docdate,112) = convert(varchar(10),getdate() - 1,112))
begin
declare @EmailList nvarchar(max)
declare @html nvarchar(MAX)
declare @Tab char(1) = CHAR(9)
declare @Filename nvarchar(50)
declare @EmailSubject varchar(254)
set @EmailSubject = (select 'SAP-Barter Integration POS Inventory Adjustment Posted for: ' + convert(varchar(10),min(DocDate),103)+ ' - ' +convert(varchar(10),max(DocDate),103) from vwPOSInventoryAdjustment where DocDate is not null)
set @Filename = 'InventoryAutoCorrection_' + convert(varchar(10),getdate()-1,112) + '.xls'
select @EmailList = coalesce(@EmailList + ';', '') + wEmail from vwEmailAddList where wUserStatus = 1 and wRptNum = 7 and wRptValue = 1
exec UTL_QueryToHtmlTable @html = @html OUTPUT,
@query = N'select * from [vwPOSInventoryAdjustment] '
,@orderBy = N'ORDER BY TransType desc, DocDate asc, Warehouse asc';
exec msdb.dbo.sp_send_dbmail
@profile_name = 'Gmail',
@recipients = @EmailList,
@subject = @EmailSubject,
@body = @html,
@body_format = 'HTML',
@query = N'select * from xxxx.dbo.vwPOSInventoryAdjustment,
@attach_query_result_as_file = 1,
@query_attachment_filename = @Filename,
@query_result_separator = @Tab,
@query_result_width = 32767,
@query_result_no_padding = 1;
end
Getting this error in Agent History Log:
executed as user: xxxx\SQLServerAGENT. Error formatting query. probably invalid parameters [SQLSTATE 42000] (Error 22050) Query execution failed: Msg 916 Level 14 State 1 Server xxxx Line 1 The server principal "xxxx\SQLServerAGENT" is not able to access the database "SAPB1_Local" under the current security context. [SQLSTATE 42000] (Error 14661). The step failed.,00:00:03,16,14661,,,,0
Apparently, the script works when running the stored proc, but when it comes to run it as agent it fails.
January 25, 2016 at 6:39 pm
genuiforex (1/25/2016)
Hi Experts,Can't send dbmail with attachment as per below script.
if exists (select distinct 1 from xxxx.dbo.owtr where U_STN_Ref = 'POSNegINVCorr' and convert(varchar(10),docdate,112) = convert(varchar(10),getdate() - 1,112))
begin
declare @EmailList nvarchar(max)
declare @html nvarchar(MAX)
declare @Tab char(1) = CHAR(9)
declare @Filename nvarchar(50)
declare @EmailSubject varchar(254)
set @EmailSubject = (select 'SAP-Barter Integration POS Inventory Adjustment Posted for: ' + convert(varchar(10),min(DocDate),103)+ ' - ' +convert(varchar(10),max(DocDate),103) from vwPOSInventoryAdjustment where DocDate is not null)
set @Filename = 'InventoryAutoCorrection_' + convert(varchar(10),getdate()-1,112) + '.xls'
select @EmailList = coalesce(@EmailList + ';', '') + wEmail from vwEmailAddList where wUserStatus = 1 and wRptNum = 7 and wRptValue = 1
exec UTL_QueryToHtmlTable @html = @html OUTPUT,
@query = N'select * from [vwPOSInventoryAdjustment] '
,@orderBy = N'ORDER BY TransType desc, DocDate asc, Warehouse asc';
exec msdb.dbo.sp_send_dbmail
@profile_name = 'Gmail',
@recipients = @EmailList,
@subject = @EmailSubject,
@body = @html,
@body_format = 'HTML',
@query = N'select * from xxxx.dbo.vwPOSInventoryAdjustment,
@attach_query_result_as_file = 1,
@query_attachment_filename = @Filename,
@query_result_separator = @Tab,
@query_result_width = 32767,
@query_result_no_padding = 1;
end
Getting this error in Agent History Log:
executed as user: xxxx\SQLServerAGENT. Error formatting query. probably invalid parameters [SQLSTATE 42000] (Error 22050) Query execution failed: ?Msg 916 Level 14 State 1 Server xxxx Line 1 The server principal "xxxx\SQLServerAGENT" is not able to access the database "SAPB1_Local" under the current security context. [SQLSTATE 42000] (Error 14661). The step failed.,00:00:03,16,14661,,,,0
Apparently, the script works when running the stored proc, but when it comes to run it as agent it fails.
Looks like you are missing a single-quote at the end of the @query parameter
@query = N'select * from xxxx.dbo.vwPOSInventoryAdjustment,
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 25, 2016 at 6:46 pm
Hi Orlando, thanks for your reply. Change to
N'select * from xxxx.dbo.vwPOSInventoryAdjustment'
Still doesn't work. Any further suggestions?
January 25, 2016 at 6:57 pm
genuiforex (1/25/2016)
executed as user: xxxx\SQLServerAGENT. Error formatting query. probably invalid parameters [SQLSTATE 42000] (Error 22050) Query execution failed: ?Msg 916 Level 14 State 1 Server xxxx Line 1 The server principal "xxxx\SQLServerAGENT" is not able to access the database "SAPB1_Local" under the current security context. [SQLSTATE 42000] (Error 14661). The step failed.,00:00:03,16,14661,,,,0Apparently, the script works when running the stored proc, but when it comes to run it as agent it fails.
Focusing on the error, it looks like permissions to me. The service account for the SQL Agent is running as the xxxx\SQLServerAGENT login. If it works when you execute the procedure from SSMS, are you able to execute the procedure as that user in SSMS? It would look something like this:
EXECUTE AS login = 'xxxx\SQLServerAGENT';
EXECUTE your_procedure_name;
REVERT;
If that fails, can you run this in SSMS:
EXECUTE AS login = 'xxxx\SQLServerAGENT';
select * from xxxx.dbo.vwPOSInventoryAdjustment;
REVERT;
Also, could you post the source code for the UTL_QueryToHtmlTable procedure?
I'm asking about this procedure because I'm not sure which one's throwing the error.
January 25, 2016 at 7:03 pm
Hi Ed,
After inserting Execute as login getting this error
The server principal "xxxx\SQLServerAGENT" is not able to access the database "xxxx" under the current security context.
stored proc UTL_QueryToHtmlTable script (this is the html body in the email) :
BEGIN
SET NOCOUNT ON;
IF @orderBy IS NULL BEGIN
SET @orderBy = ''
END
SET @orderBy = REPLACE(@orderBy, '''', '''''');
DECLARE @strsql nvarchar(MAX) = '
DECLARE @TableHead nvarchar(MAX);
DECLARE @headerRow nvarchar(MAX);
DECLARE @cols nvarchar(MAX);
SELECT * INTO #@strSQLEmail FROM (' + @query + ') sub;
SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
FROM tempdb.sys.columns
WHERE object_id = object_id(''tempdb..#@strSQLEmail'')
ORDER BY column_id;
SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #@strSQLEmail ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''
EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT
SELECT @headerRow = COALESCE(@headerRow + '''', '''') + ''<th>'' + name + ''</th>''
FROM tempdb.sys.columns
WHERE object_id = object_id(''tempdb..#@strSQLEmail'')
ORDER BY column_id;
SET @TableHead = ''<html><head>'' + ''<style>''
+ ''td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 11px arial} ''
+ ''</style>'' + ''</head>'' + ''<body>'' + ''DO NOT REPLY. This is a system generated report exported on '' + CONVERT(VARCHAR(50), GETDATE(), 100) + ''
</body>'';
SET @headerRow = ''<tr>''+ @headerRow + ''</tr>'';
SET @html = @TableHead + ''<table cellpadding="5" cellspacing="0" border="1" style="border-collapse:collapse; width: 100%" >'' + @headerRow + @html + ''</table>'';
';
EXEC sys.sp_executesql @strsql, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
END
January 25, 2016 at 8:03 pm
Okay, that's the problem. The login xxxx\SQLServerAGENT can't execute the procedure because it doesn't have permission to database xxxx.
To fix it, you'll need to create a user named xxxx\SQLServerAGENT in the databaes that contains your procedure. Grant it execute permissions on the stored procedure and you should be off to the races. It'll be something similar to this:
CREATE USER [xxxx\SQLServerAGENT] for login [xxxx\SQLServerAGENT] with default_schema = dbo;
GRANT EXECUTE ON your_procedure_name to [xxxx\SQLServerAGENT];
You should then be able to execute the procedure as the login xxxx\SQLServerAGENT and have it succeed. If not, see what permissions problem comes next.
January 25, 2016 at 9:25 pm
Thank you Ed, your solution works. BTW, i have added the sqlserveragent with role proc_executor membership and everything went well.
January 26, 2016 at 5:15 am
genuiforex (1/25/2016)
Thank you Ed, your solution works. BTW, i have added the sqlserveragent with role proc_executor membership and everything went well.
I'm glad you got it working. Thanks for the feedback.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply