December 22, 2014 at 2:34 pm
I have a query I am trying to work with to try and create a job, I am using it to get a row count and also to create a message for the job failure.
but I keep getting the message about:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
and I am a little confused since I don't have what I would consider a subquery. Could someone enlighted me?
SELECT Process_ID, Process_Name, Last_Message_Time
FROM [Process]
WHERE Process_ID IN ( 1,2,3,4)
and CONVERT(date, Last_Message_Time)> CONVERT(date, getdate() - 7)
Thanks,
Nancy
December 22, 2014 at 4:13 pm
Hi Nancy,
obviously you presented just a piece of the puzzle.
It seems like somewhere in your code you're trying to assign a value to a variable or to use it as a parameter for a stored procedure but the query in question returns more than one value.
Could you post the complete code you're struggling with?
December 22, 2014 at 5:17 pm
Also, is [Process] a table or a view? If it's a view, please provide the definition.
December 22, 2014 at 8:55 pm
This is the whole code:
Set NoCount On
Declare @LastRun varchar (50)
Declare @CreateFail varchar(50)
Declare @FailureCount int
Declare @Fail int
Set @LastRun =
(SELECT Process_ID, Process_Name, Last_Message_Time
FROM [ITF3].[dbo].[Process]
WHERE Process_ID IN (1,2,3,4)
and CONVERT(date, Last_Message_Time)> CONVERT(date, getdate() - 7)
)
SELECT @@ROWCOUNT
SET @Fail = @@ROWCOUNT
If @Fail >= 1
Begin
Print 'There was a failure for the following processes: ' + @LastRun --
Set @Fail = 'Fail'
End
Else
Begin
Set @CreateFail = 'Success'
End
I want the code to fail so it will send out an email using another step and cmdexec writed by someone else, as I am new to this type of coding and couldn't get it to work where if @@RowCount was more than 1 an email would be sent out, since I have never worked with email in this way before.
December 22, 2014 at 8:56 pm
Process is a table.
December 23, 2014 at 12:20 am
Declare @LastRun varchar (50)
...
Set @LastRun =
(SELECT Process_ID, Process_Name, Last_Message_Time
FROM [ITF3].[dbo].[Process]
WHERE Process_ID IN (1,2,3,4)
and CONVERT(date, Last_Message_Time)> CONVERT(date, getdate() - 7)
)
You try to store several records into a string variable ;-).
December 23, 2014 at 1:21 am
nancy.lytle (12/22/2014)
This is the whole code:Set NoCount On
Declare @LastRun varchar (50)
Declare @CreateFail varchar(50)
Declare @FailureCount int
Declare @Fail int
Set @LastRun =
(SELECT Process_ID, Process_Name, Last_Message_Time
FROM [ITF3].[dbo].[Process]
WHERE Process_ID IN (1,2,3,4)
and CONVERT(date, Last_Message_Time)> CONVERT(date, getdate() - 7)
)
SELECT @@ROWCOUNT
SET @Fail = @@ROWCOUNT
If @Fail >= 1
Begin
Print 'There was a failure for the following processes: ' + @LastRun --
Set @Fail = 'Fail'
End
Else
Begin
Set @CreateFail = 'Success'
End
I want the code to fail so it will send out an email using another step and cmdexec writed by someone else, as I am new to this type of coding and couldn't get it to work where if @@RowCount was more than 1 an email would be sent out, since I have never worked with email in this way before.
Quick though, in this case the @@ROWCOUNT will ALWAYS return 1, consider this example
😎
/* Declare and initialize a "count" variable */
DECLARE @FAIL INT = 0;
/* Execute a query that returns a @@ROWCOUNT of Zero */
SELECT * FROM sys.objects WHERE name = N'DOES NOT EXIST';
/* Get the @@ROWCOUNT, this returns Zero but the operation
sets the @@ROWCOUNT to One
*/
SELECT @@ROWCOUNT;
/* Assign the latest @@ROWCOUNT to the "count" variable, the value is now
set to One, not the initial Zero
*/
SELECT @FAIL = @@RowCount;
/* Display the current variable value */
SELECT @FAIL;
Quick suggestion for alternative and better coding the logic
SET NOCOUNT ON;
DECLARE @LastRun VARCHAR(250) = '';
DECLARE @CreateFail VARCHAR(50) = '';
DECLARE @Fail INT = 0;
DECLARE @NOW DATETIME = GETDATE();
SELECT @FAIL = COUNT(*)
FROM [ITF3].[dbo].[Process] P
WHERE P.Process_ID IN (1,2,3,4)
AND DATEDIFF(HOUR,Last_Message_Time,@NOW) > 168; -- 7 x 24
IF @FAIL > 0
BEGIN
SELECT @LastRun = 'There was a failure for the following processes: ' +
(
SELECT TOP (1)
CONVERT(VARCHAR(25),Process_ID ,0) + CHAR(58)
+ CONVERT(VARCHAR(25),Process_Name ,0) + CHAR(58)
+ CONVERT(VARCHAR(25),Last_Message_Time,0)
FROM [ITF3].[dbo].[Process]
WHERE Process_ID IN (1,2,3,4)
);
END
ELSE
BEGIN
SET @CreateFail = 'Success';
END
;
December 23, 2014 at 3:27 am
Based on Eirikurs solution here's a slightly modified version.
Differences: The code below does only one SELECT against the Process table and the result of the SELECT ist stored in a single variable instead of one variable per "return type" (success or fail). Furthermore, it returns the lates Last_Message_Time that failed instead of the earliest one (the missing ORDER BY together with TOP(x) was obviously just because Eirikur posted the code in in a hurry 😉 ).
Edit: And finally, I changed the DATEDIFF to eliminate the uasge of a column inside a function to improve performance (if there's an index on Last_Message_Time).
SET NOCOUNT ON;
DECLARE @Result VARCHAR(250) = '';
DECLARE @NOW DATETIME = GETDATE();
SELECT TOP (1)
@Result = 'There was a failure for the following processes: '+
CONVERT(VARCHAR(25),Process_ID ,0) + CHAR(58)
+ CONVERT(VARCHAR(25),Process_Name ,0) + CHAR(58)
+ CONVERT(VARCHAR(25),Last_Message_Time,0)
FROM [ITF3].[dbo].[Process]
WHERE Process_ID IN (1,2,3,4)
AND Last_Message_Time >DATEADD(HOUR,-168,@NOW) -- 7 x 24
ORDER BY Last_Message_Time DESC; -- show the latest message that failed
IF @Result = ''
BEGIN
SET @Result = 'Success';
END;
SELECT @Result;
December 23, 2014 at 4:33 am
LutzM (12/23/2014)
Based on Eirikurs solution here's a slightly modified version.Differences: The code below does only one SELECT against the Process table and the result of the SELECT ist stored in a single variable instead of one variable per "return type" (success or fail). Furthermore, it returns the lates Last_Message_Time that failed instead of the earliest one (the missing ORDER BY together with TOP(x) was obviously just because Eirikur posted the code in in a hurry 😉 ).
Edit: And finally, I changed the DATEDIFF to eliminate the uasge of a column inside a function to improve performance (if there's an index on Last_Message_Time).
SET NOCOUNT ON;DECLARE @Result VARCHAR(250) = '';
DECLARE @NOW DATETIME = GETDATE();
SELECT TOP (1)
@Result = 'There was a failure for the following processes: '+
CONVERT(VARCHAR(25),Process_ID ,0) + CHAR(58)
+ CONVERT(VARCHAR(25),Process_Name ,0) + CHAR(58)
+ CONVERT(VARCHAR(25),Last_Message_Time,0)
FROM [ITF3].[dbo].[Process]
WHERE Process_ID IN (1,2,3,4)
AND Last_Message_Time >DATEADD(HOUR,-168,@NOW); -- 7 x 24
ORDER BY Last_Message_Time DESC; -- show the latest message that failed
IF @Result = ''
BEGIN
SET @Result = 'Success';
END;
SELECT @Result;
Thanks for the correction Lutz!
😎
December 23, 2014 at 6:37 am
All of these are great solutions that work but I need to return all processes that fail not just one. I will try doing a select top (4) and see if that works but I haven't done much work with top either.
And thanks y'all
December 23, 2014 at 6:48 am
The only "useful" way to assign more than one row to a variable would be an xml data type (or varchar).
Obviously, we're still missing the whole picture. In your first post you wrote "to create a message for the job failure". What should this message look like?
Are you looking for a plain table embedded in an email, an attached xls file or a formatted HTML file embedded in the mail?
We need to differentiate two steps:
Step 1: identify, if there's a need to send an email (for example shown in eithe Eirikurs or my post) and
Step 2: Format and send the mail.
In your current code there's nothing that describe the message process. the only "output" is the PRINT statement...
December 23, 2014 at 7:25 am
Sorry I just got the email requirement last night after finding out the boss didn't like the attachment format. basically he wants an email that gives him the following:
Process Name: Dog Friendly Order Server Last Message Time: Dec 22 2014 7:06PM Time Interval: 849
Thanks,
Nancy
December 23, 2014 at 7:27 am
And obviously if there is more than one process that fails it should look like:
Process Name: Dog Friendly Order Server Last Message Time: Dec 22 2014 7:06PM Time Interval: 849
Process Name: Cat Friendly Order Server Last Message Time: Dec 22 2014 7:12PM Time Interval: 843
etc
Thanks,
Nancy
December 23, 2014 at 7:44 am
Based on the logic provided so far, send the mail if @Result <> ''
You could use the following parameter in your email:
@query =
'SELECT ''Process Name: '' + CONVERT(VARCHAR(25),Process_Name ,0)
+''Last Message Time: ''+ CONVERT(VARCHAR(25),Last_Message_Time,0)
FROM [ITF3].[dbo].[Process]
WHERE Process_ID IN (1,2,3,4)
AND Last_Message_Time >DATEADD(HOUR,-168,GETDATE())
ORDER BY Last_Message_Time DESC'
Don't set @attach_query_result_as_file to 1.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply