Failed to initialize sqlcmd library with error number -2147467259

Question

This is my code:

SET @msg = 'Customer Voice replies.';  
SET @query= 
    'SELECT [column1],[column2] FROM [StagingMX].[dbo].[_CVA] 
    WHERE DATEDIFF(day, [Completion time] ,GETDATE()) = 1 
    AND [column1] = IQS 
    AND [column1] = MED;'
    ;
    
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'cvApp'
    , @recipients = '[email protected]'
    , @subject = 'queryresultset'
    , @body= @msg
    , @body_format = 'TEXT'
    , @query = @query
    , @execute_query_database = 'StagingMX'  
    , @attach_query_result_as_file = 1
    , @query_attachment_filename='CustomerVoice.csv'
    , @query_result_header = 1
    , @query_result_width = 100
    , @query_result_separator = ' '
    , @exclude_query_output = 0
    , @append_query_error = 1
    , @query_no_truncate = 0
    , @query_result_no_padding = 0;

And the result I'm being given is:

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

My query works outside of sp_send_dbmail, but would not work inside it. And if I change my query for the following code, it will send the email and work as expected:

@query = 'SELECT TOP(1) * FROM [StagingMX].[dbo].[_CVA] ORDER  BY [ID] DESC;',

Also, I have already executed:

GRANT EXECUTE ON sp_send_dbmail TO PUBLIC

So far, I've concluded that:

• I have the permissions to send emails.
• I am able to send emails with queries (both as attachment and variable).

I don't know for sure, but I suspect that the problem has to do something with the limitations of the clause WHERE inside sp_send_dbmail ? Anyway, I read the following statement in a forum, and I believe he went through the same error I am getting:

After hunting around a lot more, I found a few articles discussing security and granting permissions that sent me on a wild goose chase and ultimately made no difference to the outcome. The script continued to work outside of sp_send_dbmail, but would not work with it. I don't recall how, but I have subsequently discovered that stored procedures the indexing script allegedly uses had gone AWOL (ones created by the file CommandExecute.sql), even though the indexing script worked fine outside of sp_send_dbmail. Once I created those stored procedures, everything seems to work as it should!

I am recently new to SQL, and I've tried different ways to do this but to no avail. Could someone please guide me with code through what the former statement is referring to ? Or perhaps, point me into the right direction ? At this point anything helps.

Answer 1

It looks like a mistake in your query. Shouldn't the IQS and MED not be between quotes? So something like this?

SET @query= 
    'SELECT [column1],[column2] FROM [StagingMX].[dbo].[_CVA] 
    WHERE DATEDIFF(day, [Completion time] ,GETDATE()) = 1 
    AND [column1] = ''IQS''
    AND [column1] = ''MED'';'