2

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 = 'randomemail@address.com',
    @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 = 'randomemail@address.com', 
            @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

  • Shouldn't you be using single-quotes instead of double quotes to enclose your strings in the where clause? – Philᵀᴹ Sep 4 '17 at 14:54
0

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.

  • Thank you so much for your reply and help! Did what you suggested and it works now! However I want the email to be sent only if the query returns (any) values but now it always sends the email with the body: "0 rows affected". I think that my code now looks correct but I really don't know anymore shakes head Please see my paste / code: link – TheSwede86 Sep 5 '17 at 11:08
  • @TheSwede86 - You may want to ask this as a new question, as the problem is different. You can post the code (working as it does now) without allowing confusion from the previous issue. Feel free to include a link to this question if it seems relevant. You may at least want to note that this is a follow-up (so if parts of the question seem like something people have seen before, they'll understand why). – RDFozz Sep 5 '17 at 20:03
  • @TheSwede86 If the code you posted in the exact code you are using (minus changing the "..." into [...]), then that is simply due to you creating a variable, @alerttriggered, setting it to a value, and then checking it via: IF (@alerttriggered IS NOT NULL). That means your IF condition is effectively if (true). If you want the variable to indicate any rows returned, change the variable to be INT and then do: SELECT @alerttriggered = COUNT(*) FROM... and then the rest of that query, but not in single quotes. Then check using: IF (@alerttriggered > 0) .... – Solomon Rutzky Sep 5 '17 at 20:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.