December 13, 2007 at 8:49 am
I am trying to send the results of a query via SQL Mail and get an invalid parameter result. Can anyone tell me what is the cause of this error?
The subject table is one created to recognize redirected rows from an SSIS process. The table currently contains one test row.
I can send a test message without the query parameters successfully.
Your help is appreciated.
Code:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DW_VISTA',
@recipients = 'Howard.Bachtel@va.gov',
@body = 'Test',
@query = 'Select
TableName,RowsCnt
from SSIS_Exceptions.dbo.tbl_Redirected' ,
@subject = 'DB Mail Test',
@attach_query_result_as_file = 1;
Result:
Msg 22050, Level 16, State 1, Line 0
Error executing extended stored procedure: Invalid Parameter
December 13, 2007 at 9:36 am
Try -
DECLARE @sql nvarchar(MAX)
SET @sql = 'select top 1* from sys.tables'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail',
@recipients = 'thomas_bollhofer@symantec.com',
@body = 'Test',
@query = @sql ,
@subject = 'DB Mail Test',
@attach_query_result_as_file = 1;
Tommy
Follow @sqlscribeDecember 13, 2007 at 11:26 am
I tried the suggested change (modified the recipient to myself and the profile to our installed profile) with the same results.
After that, I tried exactly as you sent it hoping you would get an e-mail but the same thing happened.
Thanks for your thoughts. If you have any more I would appreciate it.
December 13, 2007 at 11:46 am
I was comparing your call to sp_send_dbmail to one I have in a scheduled job and I see one thing missing from yours, this:
@execute_query_database = 'mydb',
Add this to your call (and if you add it to the end, be sure to remove the comma (,) at the end).
😎
December 13, 2007 at 1:12 pm
Interesting.....have you confirmed that database mail is working in your env? Can you send a test message?
Tommy
Follow @sqlscribeDecember 14, 2007 at 7:46 am
I have tried including the database reference with the same results:
"Msg 22050, Level 16, State 1, Line 0
Error executing extended stored procedure: Invalid Parameter"
I can send a test message successfully, but when I add a query to it this message appears.
I also tried to attach a file to see what happened:
The following is the query that gets the above result:
"
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DW_VISTA',
@recipients = 'Howard.Bachtel@va.gov',
@subject = 'DB Mail Test',
@body = 'Test',
@query = 'Select
TableName,RowsCnt
from SSIS_Exceptions.dbo.tbl_Redirected' ,
@attach_query_result_as_file = 0,
@execute_query_database = 'SSIS_Exceptions';"
Is there a way to determine what the invalid parameter is from what extended stored procedure?
December 14, 2007 at 8:09 am
What build are you running?
Tommy
Follow @sqlscribeDecember 14, 2007 at 8:15 am
I took your code, made a couple of changes for my environment, and was able to send myself an email. If you would like, I can make another small change and see if I can send the same test to you.
Here is the code as I ran it:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DWDevelopmentEmail',--'DW_VISTA',
@recipients = 'pettila@d11.org', --'Howard.Bachtel@va.gov',
@subject = 'DB Mail Test',
@body = 'Test',
@query = 'Select
TableName,RowCnt
from dbo.tbl_Redirected' ,
@attach_query_result_as_file = 0,
@execute_query_database = 'SandBox';
😎
December 14, 2007 at 1:19 pm
Thank you for your help. I tried the code you sent (made the change back to my environment) and got the same information. Some looking around and there is a bug reported (#858) with a hotfix available that appears to address this problem. From the details for this bug and hotfix it seems that some, but not all installations receive this. The documentation is relatively recent and I do not know if it was included in our last upgrade to SQL Server. I plan to research that next and suspect we may have not included it. I appreciate your help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply