Job is empty SQL

Question

Job is empty SQL

Barendrecht82

Old Hand

Points: 337
More actions
December 6, 2021 at 1:20 pm

#3960809
Hi guys,

I really do not understand why I still receive empty emails. Please check my code:
```
EXEC msdb.dbo.sp_send_dbmail
    @profile_name =  '#####'
    , @recipients = 'a###'
    , @subject = 'queryresultset'
, @body= 'test tes tesl'

, @execute_query_database = 'dbo.rates'
```
The mail I get is:
- This topic was modified 3 years, 9 months ago by Barendrecht82.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

You need to read the documentation a little more closely. You have not defined @query and your database name looks more like a table name.

Barendrecht82 Old Hand Points: 337 More actions · Answer 2

I have changed the code:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name =  'sql***'
    , @recipients = '*******'
    , @subject = 'queryresultset'
, @body= 'test tes tesl'

, @execute_query_database = '****sql01'

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name =  '*****'
    , @recipients = 'a******'
   , @query = 'SELECT (*) FROM dbo.View_rates'
    , @subject = 'Work Order Count'  
    , @attach_query_result_as_file = 1

The job failed

Date 6-12-2021 15:35:09

Log Job History (PV controle)

Step ID 1

Server ***SQL01

Job Name PV controle

Step Name controle

Duration 00:00:00

Sql Severity 16

Sql Message ID 22050

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted 0

Message

Executed as user: ****administrator. Mail (Id: 158) queued. [SQLSTATE 01000] (Message 0) Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050). The step failed.

What should I do?

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

Try again after adding this to the command:

@query_result_header = 1,

Barendrecht82 Old Hand Points: 337 More actions · Answer 4

I tried this, get the same issue:

Message

Executed as user: ***\administrator. Mail (Id: 159) queued. [SQLSTATE 01000] (Message 0) Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050). The step failed.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name =  '***'
    , @recipients = '****'
    , @subject = 'queryresultset'
, @body= 'test tes tesl'

, @execute_query_database = '***'

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name =  's**'
    , @recipients = '***'
   , @query = 'SELECT (*) FROM dbo.View_rates'
    , @subject = 'Work Order Count'  
    , @attach_query_result_as_file = 1
, @query_result_header = 1

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

Your query syntax is invalid.

SELECT * FROM dbo.View_rates

might work, but you should really

a) Test your query first, and

b) Explicitly name the columns that you wish to return

Barendrecht82 Old Hand Points: 337 More actions · Answer 6

I thank you for the advice's but still errors...

Message

Executed as user: ***\administrator. Mail (Id: 160) queued. [SQLSTATE 01000] (Message 0) Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050). The step failed.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name =  '***'
    , @recipients = '***'
    , @subject = 'queryresultset'
, @body= 'test tes tesl'

, @execute_query_database = '***'

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name =  '***'
    , @recipients = '***'
   , @query = 'SELECT        res_id
FROM            dbo.humres'
    , @subject = 'Work Order Count'  
    , @attach_query_result_as_file = 1
, @query_result_header = 1

Phil Parkin SSC Guru Points: 247180 More actions · Answer 7

Just to be clear, you executed only this part, is that correct?

EXEC msdb.dbo.sp_send_dbmail @profile_name = '***'
                            ,@recipients = '***'
                            ,@query = 'SELECT res_id FROM dbo.humres'
                            ,@subject = 'Work Order Count'
                            ,@attach_query_result_as_file = 1
                            ,@query_result_header = 1;

And substituted the 'real' values in place of '***'?

Barendrecht82 Old Hand Points: 337 More actions · Answer 8

Barendrecht82

Old Hand

Points: 337

December 6, 2021 at 4:11 pm

#3960905

Correct

Phil Parkin SSC Guru Points: 247180 More actions · Answer 9

Phil Parkin

SSC Guru

Points: 247180

December 6, 2021 at 4:28 pm

#3960910

Then I am out of ideas. Anyone else care to help?

Barendrecht82 Old Hand Points: 337 More actions · Answer 10

Hi Phil,

I changed the login details:

Role

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name =  '***'
    , @recipients = '***'
, @body= 'Some text for body'
,@execute_query_database = '***'
   , @query = 'SELECT        res_id
FROM            dbo.humres'
    , @subject = 'Work Order Count'  
    , @attach_query_result_as_file = 1

Errorcode:

Message

Executed as user: ****. The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 11

You need to change the role memberships within MSDB. Untick everything except db_owner and try again.