CBTr.Net Tech Dump

So we have an audit that tells us if our have the latest backup files or not. Our backup job runs Sunday – Wednesday. Our alert get kicked off Thursday – Saturday complainting that we miss some backups. I had to modify the job to stop the annoyance.

The SP was created by someone a long time ago using CURSOR. I don’t use CURSOR, in fact, I’ve never written CURSOR before. The CURSOR looks like this:

DECLARE SrvEnv CURSOR FOR
SELECT name from serverInfo..srvName
Open SrvEnv

So in order to avoid having to mess w/ the CURSORS, I used a temp table, an IF statement, and modify the cursor for a bit


DECLARE @weekday varchar(20), @msg varchar(300)
SET @weekday = datename(dw,getdate()) --return the day of the week

-- - - - - - - - - - - - - - - - - - - - -
-- new tmp table for the cursor to use: #tmpsrv01
-- - - - - - - - - - - - - - - - - - - - -
if object_id('temptdb..#tmpsrv01') is not null
drop table @tmpsrv01

create table #tmpsrv01 (name varchar(100))

-- - - - - - - - - - - - - - - - - - - - -
-- new: get servers listing based on the day
-- - - - - - - - - - - - - - - - - - - - -

if (@weekday) IN ('Thursday','Friday', 'Saturday')
begin
set @msg = 'selecting all servers except for SERVER_A & SERVER_B'
raiserror (@msg,0,1) with nowait

--insert into #tmpsrv01
select name
from serverInfo..srvName
where name NOT IN ('SERVER_A','SERVER_B')
order by name
end
else
set @msg = 'Selecting all servers'
raiserror (@msg,0,1) with nowait

--insert into #tmpsrv01
select name
from serverInfo..srvName
order by name

DECLARE SrvEnv CURSOR FOR
select name from #tmpsrv01

open SrvEnv


I had a feeling all this could be simplified even more but I didn’t really know how. So I asked my co-worker and he came up with:


DECLARE @weekday varchar(20)
SET @weekday = datename(dw,getdate()) --return the day of the week

DECLARE SrvEnv CURSOR FOR

SELECT 'Name' =
CASE
WHEN Name IN ('SERVER_A','SERVER_B') AND @weekday IN ('Thursday', 'Friday', 'Saturday') THEN NULL
ELSE ServerName
END
FROM ServerInfo..srvName
WHERE Name IS NOT NULL
ORDER BY Name

OPEN SrvEnv