Error Message - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Error Message

Author

Topic

RajJol
Starting Member

17 Posts

Posted - 2010-10-20 : 11:28:56

Hi,

I am in the process of completing a task and everything was working fine until now. I set up a schedule job within my SQL server agent to run daily and ping me an email with results, however, I received the following error message today and I have no idea what the problem is: -

Message
Executed as user: NT AUTHORITY\NETWORK SERVICE. Incorrect syntax near the keyword 'Else'. [SQLSTATE 42000] (Error 156). The step failed.

Can anyone help me with this. I have added my code below but im pretty sure it's all spot on as it has worked for days without any errors.

Thank You.

STATEMENT

declare @MyErrorCount int

select @MyErrorCount = count(*) from Update y
left join update t
on y.tablename = t.TableName
where CONVERT (char (10), t.Date, 120)= convert(char(10), getdate(),120) and CONVERT (char(10), y.Date, 120)= convert(char(10), getdate() -1, 120)
and convert(decimal (19,1), convert(decimal(19,5),(t.Count- y.Count)) / y.Count * 100) > 5

if @MyErrorCount > 0

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Db eMail',
@recipients = 'abc@abc.com',
@subject = 'Warning ',
@body = 'Table(s) have more then 5% difference compared to yesterday''s count',

@query = 'select left(y.TableName + ''..............................'',30) as ''Table Name '', y.Count ''Yesterday'', t.count ''Today'', t.Count - y.Count ''Difference'', convert(decimal (19,1), convert(decimal(19,5),(t.Count- y.Count)) / y.Count * 100) ''Percentage Difference'' from abc.dbo.Update y left join abc.dbo.update t on y.tablename = t.TableName where CONVERT (char (10), t.Date, 120)= convert(char(10), getdate(),120) and CONVERT (char(10), y.InsertDate, 120)= convert(char(10), getdate() -1, 120) and convert(decimal (19,1), convert(decimal(19,5),(t.Count- y.Count)) / y.Count * 100) > 5 order by y.TableName'

Else
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Db eMail',
@recipients = 'abc@abc.com',
@subject = 'All tables have been updated',
@body = 'All tables have been updated',
@query_result_no_padding = 1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 11:53:45

First I always would use if begin .. end else begin ... end
IF @MyErrorCount > 0
BEGIN

END
ELSE
BEGIN

END

Second: The error would come up in your posted script if there were more than one statement in the if or else block.
So you are sure you are showing the code that is causing the error?

Third:
If you have a table named Update then you should use [Update] to get no error for that.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources