1

I am trying to send an email containing the results of a stored procedure call inside SQL Server.

declare @querytext nvarchar(100)
set @querytext = 'sp_get_SPresults @item_name = ''Name of Group'''';
EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'My Email',  
    @recipients = 'anaddress@adomain.com',  
    @body = 'Email',  
    @subject = 'Suitable Subject' ,     
    @query = @querytext,
    @attach_query_result_as_file = 1,
@query_result_header = 1,
@query_no_truncate = 1;

But when I run this, I get a

Msg 22050, Level 16, State 1, Line 23 Failed to initialize sqlcmd library with error number -2147467259 error.

When I send a test email in SQL Server it sends it fine. Even just setting @querytext as "select * from [Table]" doesn't work. I've seen elsewhere that including @query_result_header=1 can clear this kind of issue, but it doesn't appear to make a difference here.

Any help would be appreciated.

Thanks

Igor
  • 60,821
  • 10
  • 100
  • 175
Dan
  • 57
  • 6
  • I suspect there are 2 problem here. Firstly, on what you said you attempted, my reading suggests that `@query_result_header` should be set to `0` not `1`. I suspect the reason to do this is when the dataset you are trying to send it too large, and thus you need to have the result truncate; having the header spills it over still though as it ensures the dataset fits, not the header + dataset. Ideally, however, move the logic to something else if you want to email out such large data sets. – Thom A Aug 30 '21 at 11:54
  • Try calling the stored procedure by qualifying the database and schema names, e.g. `'exec MyDatabase.dbo.sp_get_SPresults @item_name=''Name of Group'''';` – AlwaysLearning Aug 30 '21 at 11:55
  • Also... _Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server to designate system procedures. Using the prefix can cause application code to break if there is a system procedure with the same name._ REF: [CREATE PROCEDURE](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql). – AlwaysLearning Aug 30 '21 at 11:55
  • 1
    I suspect, however, the real reason is your lack of the parameter `execute_query_database` (see [Arguments](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-ver15#arguments)) and so you attempt to run the **S**pecial **P**rocedure `sp_get_SPresults` in the context of `msdb`; the database the procedure `sp_send_dbmail` exists in. Though I don't recognise `sp_get_SPresults`; it's not a documented **S**pecial **P**rocedure. – Thom A Aug 30 '21 at 11:55
  • Have you set up DB Mail properly, is the profile `My Email` fully setup? – Charlieface Aug 30 '21 at 12:13
  • @Charlieface as far as I can determine, and surely the test email wouldn't come through if it was incorrectly set up? – Dan Aug 30 '21 at 12:40
  • @Larnu - the results that should come back return about 5 or 6 columns, and with typically no more than 10 rows. – Dan Aug 30 '21 at 12:41
  • Which is why my real suspicions is the lack of the parameter for the database, @Dan , as I note in my second comment. – Thom A Aug 30 '21 at 12:43
  • @AlwaysLearning Noted re the SP prefix. I've tried naming the database name.dbo.sp.... and it subsequently gives me a -2147024809 error instead. – Dan Aug 30 '21 at 12:44
  • @Larnu, could you give me a bit more guidance on what you mean about the execute_query_database, I don't quite follow and this isn't my normal job. Is it a case of stating that this database is the name of my database, ie not master, etc? Thank you. – Dan Aug 30 '21 at 12:45
  • I link to the documentation @Dan ; that explains everything about said parameter. – Thom A Aug 30 '21 at 12:46

1 Answers1

0

In the end, for whatever reason, I tried using just the following parameters:

  • @profile_name
  • @recipients
  • @body
  • @subject
  • @body_format
  • @execute_query_database

This now works. Thanks everyone for your help. :)

Dan
  • 57
  • 6