October 1, 2011 at 8:43 am
I have a SQL Agent job that runs a stored-procedure to check the status of our end of day processes. It's rather straightforward TSQL and 8/10 it runs successfully, the other 20% of the time we receive this error (love this one):
Message
Executed as user: MyCompany\F1Onsitelog. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.
"Probably" invalid parameters...
I don't understand how it can run successfully one day, but fail the next. Am I missing something below that's so blatently obvious I keep missing it?
USE [loadprogress]
GO
/****** Object: StoredProcedure [dbo].[utl_CheckEODComplete] Script Date: 10/01/2011 09:36:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[utl_CheckEODComplete] AS
DECLARE @EODDate varchar(10)
DECLARE @CAEOD varchar(10)
DECLARE @CIEOD varchar(10)
DECLARE @Job varchar(100)
DECLARE @Subject varchar(150)
DECLARE @Body varchar(750)
DECLARE @Query varchar(500)
DECLARE @ERROR varchar(500)
SET @Query = '
SET NOCOUNT ON
SELECT
(SELECT CONVERT(varchar(10),procdayend,101) FROM [NAS2-DBR].COREISSUE.dbo.arsystemaccounts) [Current CI Day:],
(SELECT CONVERT(varchar(10),procdayend,101) FROM [NAS2-DBR].COREACQUIRE.dbo.arsystemaccounts) [Current CA Day:]
SET NOCOUNT OFF'
SET @EODDate = CONVERT(varchar(10), GETDATE(),101)
BEGIN TRY
SELECT @CIEOD = CONVERT(varchar(10),procdayend,101) FROM [NAS2-DBR].COREISSUE.dbo.arsystemaccounts
SELECT @CAEOD = CONVERT(varchar(10),procdayend,101) FROM [NAS2-DBR].COREACQUIRE.dbo.arsystemaccounts
WHILE (@CIEOD < @EODDate) OR (@CAEOD < @EODDate)
BEGIN
SET @Subject = 'Business Support Reports are not starting :: CI/CA EOD has not yet completed!'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'me@mycompany.com',
@subject = @Subject,
@body = '',
@importance = 'High',
@query = @Query,
@execute_query_database = 'COREACQUIRE'
WAITFOR DELAY '00:15:00'
SELECT @CIEOD = CONVERT(varchar(10),procdayend,101) FROM [NAS2-DBR].COREISSUE.dbo.arsystemaccounts
SELECT @CAEOD = CONVERT(varchar(10),procdayend,101) FROM [NAS2-DBR].COREACQUIRE.dbo.arsystemaccounts
END
IF (@CIEOD = @EODDate) AND (@CAEOD = @EODDate)
BEGIN
EXEC msdb..sp_send_dbmail
@recipients = 'me@mycompany.com'
,@subject = 'Daily - Load Progress Job - Complete'
,@sensitivity = 'private'
,@Body = 'All tables (and dependent tables for other jobs) have been successfully loaded from Jerry. Reporting checks are now taking place - once completed, reporting will begin!'
END
END TRY
BEGIN CATCH
SET @Error = ERROR_MESSAGE()
PRINT 'ERROR' + @Error
EXEC msdb..sp_send_dbmail
@recipients = 'me@mycompany.com'
,@subject = 'EOD Check Failed'
,@sensitivity = 'private'
,@Body = @Error
END CATCH
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 1, 2011 at 1:35 pm
As per this link the issue might be related to an expired password
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply