SQL Server 2016: sp_send_dbmail, Failed to initialize sqlcmd library with error number -2147467259

Question

I have a quite frustrating problem and I am not sure I have done this correctly. I don't have a lot of experience with T-SQL but here is what I am trying to do:

IF a query returns values THEN send an email, otherwise do nothing. I am going to schedule the query to be run as a job every 5 minutes or so to check if the query has returned results (and if so send an email as per the script).

Below is the script which is work in progress and taken from here:

https://serverfault.com/questions/165597/send-sql-server-job-alert-only-when-a-query-has-rows-to-return

So I understand that the script won't do exactly what I want but since I get stuck when trying to run it I want to solve that before modifying it further.

create procedure [dbo].[sp_alert_nav_jobqueue_test] 
as

Begin

declare @alerttriggered AS nvarchar(max);

set @alerttriggered = 'SELECT "ID", "Description", "Start Date_Time", "Status", "E-Mail Sent", "Job Queue Category Code"
FROM [MyDatabaseTEST].[dbo].[TEST My Company $Job Queue Log Entry]
WHERE cast("Start Date_Time" as date) = cast(getdate() as date) AND "Status" != 0 AND "E-Mail Sent" >0'

IF (@alerttriggered IS NOT NULL)
begin

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Primary SMTP Profile',
    @recipients = '[email protected]',
    @query = 'SELECT "ID", "Description", "Start Date_Time", "Status", "E-Mail Sent", "Job Queue Category Code"
FROM [MyDatabaseTEST].[dbo].[TEST My Company $Job Queue Log Entry]
WHERE cast("Start Date_Time" as date) = cast(getdate() as date) AND "Status" != 0 AND "E-Mail Sent" >0' ,
      @subject = 'Alert from TEST NAV Job Queue Entry ',
       @Body = 'Asdf' ,
    @attach_query_result_as_file = 0 ;

End
else
begin

      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Primary SMTP Profile',
       @recipients = '[email protected]', 
            @BODY = 'No data returned ', 
            @subject = 'Merchant Email'

End
End;

Now if I try and execute it via:

EXEC [dbo].[sp_alert_nav_jobqueue_test]
GO

I get this error:

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

Now I am trying to execute it via my user and I've created an AD-group which I am a member of under the "msdb"-datbase and "Security" > "Users" which have "DatabaseMailUserRole" and "db_owner" under "Membership" and also have "Alter","Control" and "Execute" for the stored procedure I created.

When I am running SQL Profiler to further troubleshoot I see this:

exec sp_executesql N'INSERT sysmail_query_transfer(uid, text_data) VALUES((@P1), (@P2))',N'@P1 nchar(72),@P2 ntext',N'29E86B54-AC35-4944-B700-EBD9BED57A22                                    ',N'Msg 241, Level 16, State 1, Server MyServer, Line 1
Conversion failed when converting date and/or time from character string.
'

If I comment out the "WHERE"-clause, modify the "SELECT"-statement to "TOP 1" and set "@attach_query_result_as_file = 0" to "1" I can successfully send an email. This leads me to believe that there is something wrong with my "WHERE"-clause but what?

Guessing it is that the value that gets returned from the "WHERE"-clause is not of a "varchar" type?

Really appreciate any and all help

Answer 1

The problem is that you are a) using double-quotes instead square-brackets when specifying column names, and b) somehow the session setting of QUOTED_IDENTIFIER is OFF.

You can alter the stored procedure, BUT only after specifying the proper setting first. The setting is stored along with the Stored Procedure definition. You can see the setting (the uses_quoted_identifier column) by running the following:

SELECT * FROM sys.sql_modules WHERE [object_id] = OBJECT_ID(N'sp_alert_nav_jobqueue_test');

Hence, do the following and then re-run the query above to see that the setting did change:

SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[sp_alert_nav_jobqueue_test] 
AS
...

You should also change the double-quotes to be square-brackets. Even if it starts working with the correct session setting, square brackets always work and so are by far the better choice. You can see the difference in behavior below.

SET QUOTED_IDENTIFIER ON;
SELECT cast("Start Date_Time" as date) FROM (SELECT GETDATE() AS [Start Date_Time]) tmp;
-- works 

SET QUOTED_IDENTIFIER OFF;
SELECT cast("Start Date_Time" as date) FROM (SELECT GETDATE() AS [Start Date_Time]) tmp;
-- error:
/*
Msg 241, Level 16, State 1, Line 210
Conversion failed when converting date and/or time from character string.
*/

SET QUOTED_IDENTIFIER ON;
SELECT cast([Start Date_Time] as date) FROM (SELECT GETDATE() AS [Start Date_Time]) tmp;
-- works

SET QUOTED_IDENTIFIER OFF;
SELECT cast([Start Date_Time] as date) FROM (SELECT GETDATE() AS [Start Date_Time]) tmp;
-- works

---

Side note: It is also best to not name stored procedures starting with sp_ as that has special meaning in SQL Server. It causes SQL Server to first look in [master] for the stored procedure and then, if not found, it will check the current DB. Meaning: it is a performance hit unless it is in [master] and intended to execute this way.