April 9, 2013 at 8:07 am
Hi All,
I have created an stored procedure which uses thebelow select statement :
select getdate(),'Old or No Backup At All For the database ' + s.dbname + ' no backup for' ,s.[Days since Backup]
from (SELECT t.name as [DBName],
(COALESCE(Convert(varchar(10),MAX(datediff(d, getdate(), u.backup_finish_date))),101)) as [Days since Backup]
FROM SYS.DATABASES t left outer JOIN msdb.dbo.BACKUPSET u
ON t.name = u.database_name
group by t.Name) as s
where s.dbname <> 'tempdb'
Now, I have a batch job which calls this procedure and the output of this select statement is sent to output.log file.
When I Open this file, I get the result :
as
"2013-04-09 18:55:00.293 Old or No Backup At All For the database AdventureWorks2008 no backup for"
And the expected result should be
"2013-04-09 18:55:00.293 Old or No Backup At All For the database AdventureWorks2008 no backup for 25(This is the Days difference between getdate and last backup of DB)"
So the value after for is missing in theoutput file. Although, the value is perfectly displayed in SSMS..
Can someone please suggest if there is something that I am missing in this script?
April 9, 2013 at 10:33 am
rollercoaster43 (4/9/2013)
Hi All,I have created an stored procedure which uses thebelow select statement :
select getdate(),'Old or No Backup At All For the database ' + s.dbname + ' no backup for' ,s.[Days since Backup]
from (SELECT t.name as [DBName],
(COALESCE(Convert(varchar(10),MAX(datediff(d, getdate(), u.backup_finish_date))),101)) as [Days since Backup]
FROM SYS.DATABASES t left outer JOIN msdb.dbo.BACKUPSET u
ON t.name = u.database_name
group by t.Name) as s
where s.dbname <> 'tempdb'
Now, I have a batch job which calls this procedure and the output of this select statement is sent to output.log file.
When I Open this file, I get the result :
as
"2013-04-09 18:55:00.293 Old or No Backup At All For the database AdventureWorks2008 no backup for"
And the expected result should be
"2013-04-09 18:55:00.293 Old or No Backup At All For the database AdventureWorks2008 no backup for 25(This is the Days difference between getdate and last backup of DB)"
So the value after for is missing in theoutput file. Although, the value is perfectly displayed in SSMS..
Can someone please suggest if there is something that I am missing in this script?
suggestion, check your code and mine for the changes:
select getdate(),'Old or No Backup At All For the database ' + s.dbname + ' no backup for ' + CONVERT(VARCHAR(10),s.[Days since Backup] ) + ' days'
from (SELECT t.name as [DBName],
(COALESCE(Convert(varchar(10),MAX(datediff(d, getdate(), u.backup_finish_date))),101)) as [Days since Backup]
FROM SYS.DATABASES t left outer JOIN msdb.dbo.BACKUPSET u
ON t.name = u.database_name
group by t.Name) as s
where s.dbname <> 'tempdb'
April 10, 2013 at 5:50 am
Hi
Thanks a Ton for pointing out the COnversion Miss..!! It helped a lot!!!!
April 10, 2013 at 6:06 am
you're welcome, good luck!
April 10, 2013 at 7:07 am
I need one small clarification if you can assist.
In the datediff function, the second date will always be an earlier date than the first in my case (i.e getdate()). This will always result in a negative value.
(COALESCE(Convert(varchar(10),MAX(datediff(dd, getdate(),u.backup_finish_date))),101))
So this negative value gives me the correct no of days, but it prevents my output to a flat file since it considers '-' as a special character and does not include it in the o/p. Any String after '-' is truncated.
My batch file goes this way
sqlcmd -E -S <Servername> -i bkupChks.sql >> result.log.
Now, the result log truncates the values after the '-' which is needed to be displayed.
Moreover, I am unable to compare the value of convert(int,s.[Days since Backup])> (Some number) since the value returned is negative 🙁
Any suggestions?
April 10, 2013 at 7:20 am
Perfect case for a CASE!
btw nice post subject, I'm going to use your code myself!
edit: you could probably move the case clause elsewhere to take care of the comparison, for example, do the case on the results of your coalesc, produce another subquery, etc, but hopefully you can tweak the below and get what you want.
select getdate(),'Old or No Backup At All For the database ' + s.dbname + ' no backup for ' +
CASE WHEN s.[Days since Backup] < 0
THEN CONVERT(VARCHAR(10),-1 * s.[Days since Backup] ) + ' days'
ELSE CONVERT(VARCHAR(10), s.[Days since Backup] ) + ' days'
END
from (SELECT t.name as [DBName],
(COALESCE(Convert(varchar(10),MAX(datediff(d, getdate(), u.backup_finish_date))),101)) as [Days since Backup]
FROM SYS.DATABASES t left outer JOIN msdb.dbo.BACKUPSET u
ON t.name = u.database_name
group by t.Name) as s
where s.dbname <> 'tempdb'
[/code]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply