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 2 years, 11 months ago by Barendrecht82.

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

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

Phil Parkin SSC Guru Points: 246960 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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: 246960 More actions · Answer 3

Try again after adding this to the command:

@query_result_header = 1,

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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: 246960 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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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: 246960 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 '***'?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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: 246960 More actions · Answer 9

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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: 246960 More actions · Answer 11

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin