0

so, i have this code, for a procedure. the code is below:

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'SQL-PHC',  
    @recipients = 'filipeferreira@mass.pt',  
    @query = 'SELECT no, nome, obrano, convert(varchar,datafinal)  
    FROM bo
    WHERE nmdos LIKE "Preços por Cliente"
    AND datafinal = DATEADD(day, -1, CONVERT(date, GETDATE()))' ,  
    @subject = 'Work Order Count',  
    @attach_query_result_as_file = 1 ;

when i parse the code, everything is ok, but when i execute it i get the following error:

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

i really don't get whats wrong or how to solve this

questionador
  • 111
  • 1
  • 3
  • 12
  • 1
    `sp_send_dbmail` runs the query from the msdb database context. You'll have to fully qualify your `bo` table reference with its database name and schema name. – AlwaysLearning Nov 07 '19 at 12:37
  • Is `Preços por Cliente` *meant* to be wrapped in double quotes? That would mean that `Preços por Cliente` is a column name. – Thom A Nov 07 '19 at 12:41
  • [Possible duplicate](https://stackoverflow.com/questions/50858900/sp-send-dbmail-fails-with-attachment)... – Peter Schneider Nov 07 '19 at 13:01
  • Preços por Cliente is in fact a column name. Going to try to reference the correct database, missed that. – questionador Nov 07 '19 at 14:06
  • @AlwaysLearning just changing the "msdb" for my database in "msdb.dbo.sp_send_dbmail", would be enough? – questionador Nov 07 '19 at 14:13

1 Answers1

0

well, finally got it right. after using profiler i discovered the error was on the query, so just adding the database name to the "from" solved it. thanks anyway guys

questionador
  • 111
  • 1
  • 3
  • 12