sp_Send_dbmail “Failed to initialize sqlcmd library with error number -2147467259” error

Question

sp_Send_dbmail “Failed to initialize sqlcmd library with error number -2147467259” error

jwilder

SSC Veteran

Points: 206
More actions
June 2, 2017 at 6:07 am

#323835
When I run the query below I get the following error message:
Msg 22050, Level 16, State 1, Line 0 Failed to initialize sqlcmd library with error number -2147467259.
```
Declare @CurrentDateTime as datetimeSet 
@CurrentDateTime = DateAdd(HH,5,GETDATE())  
EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'Alerts',
  @recipients = 'xxx@myemailaddress.com',
  @subject = 'Job Queue not running',
  @query = 'Select CodeFrom [MyDatabase$Job Queue] Where [Last Heartbeat] < @CurrentDateTime',
  @execute_query_database = MyDatabase,
  @attach_query_result_as_file = 0
```
I can execute the query (Select CodeFrom [MyDatabase$Job Queue] Where [Last Heartbeat] < @CurrentDateTime) fine by itself in SSMS. But the same query does not work when I put it into @query. I am running this in the query editor so SQLServer Agent not in play yet though I did make it a sysadmin anyway. If I remove the Where Clause it works and I get the email so I assume it has something to do with the @CurrentDateTime variable.
I have looked at all the posts for this error and usually the issue is missing the @execute_query_database parameter or a permission issue. Any thoughts?

Viewing 3 posts - 1 through 2 (of 2 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

I think your first thought with the variable is correct.
Since the query will execute in a separate session, the variable wouldn't be available to sp_send_dbmail.
Check the info about @query in the help topic: sp_send_dbmail

Sue

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

You could try rewriting the query as:


Select Code From [MyDatabase$Job Queue] 
Where [Last Heartbeat] < DateAdd(hour, 5, GETDATE())DateAdd(hour, 5, GETDATE())

You do realize that your calculation is checking for [Last Heartbeat] that's before 5 hours from now, not before 5 hours ago, right?

Also, please notice that I put a space between the Code and From in the query. It may be a typo or copy/paste issue with the site, but you don't have one in your original post.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions