Case within a Cursor (Detached DB Utility)
-
Howdy all,
I have been writing a utility to fire of an email when a db becomes detatched as this happened twice last week and I can not trace when or why. Anyway, I was attempting to do a CASE on a cursor item and started getting erorrs. So here is my snippet, can somebody show me where I went wrong? I know there are possibly more elegant methods of accomplishing this, but this is where I am.
******************************
/* This script returns a list of the current databases
and the current status via the 'DATABASEPROPERTY(dbName,'IsDetached')'
Metadata function.
I found some unrelated snippets of code and pieced them together
to get the desired results. I plan on sending an email when a db
becomes mysteriously 'Detached'. There are gremlins in my db and they
have been busy detaching db's unceriomounisly
*/
DECLARE @command VarChar(500),
@path VarChar(255)
Create Table #FileTable
(#FileName VarChar(100) NULL)
--SET @path = 'C:\DataFiles\Data\' -- Development SQL
SET @path = 'E:\Databases\' -- Production SQL
SELECT @command = 'master..xp_cmdshell ' + '"' + 'dir ' + @path + '/b /A:-D' + '"'
INSERT #FileTable
EXEC (@command)
GO
DECLARE @dbName varchar(30), -- Name holder
@dbPropTxt varchar(150), -- db property
@OutMsg varchar(500), -- List of db's for email
@Err int -- If I have anything to email
SET @OutMsg = ''
SET @Err = 0
-- Do the cursor thing.
DECLARE tCursor CURSOR FOR SELECT * FROM #FileTable
OPEN tCursor
FETCH NEXT FROM tCursor
INTO @dbName
-- Stripping the _Data.MDF from each returned value from
-- the temp table.
--SELECT @dbname
Case @dbname
WHEN Right(@dbname,9) = '_Data.MDF' THEN SET @dbName = LEFT(@dbName,Len(@dbName)- 9)
WHEN Right(@dbname,9) <> '_Data.MDF' THEN SET @dbName = LEFT(@dbName,Len(@dbName)- 4)
END
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbPropTxt = (SELECT DATABASEPROPERTY(@dbName,'IsDetached'))
If @dbPropTxt <> 0
-- BEGIN
-- -- Print @dbName + ' : ' + @dbPropTxt
-- END
--ELSE
BEGIN
SET @OutMsg = @OutMsg + ' : ' + @dbName
SET @Err = 1
print 'Some thing'
END
FETCH NEXT FROM tCursor
INTO @dbName
Case @dbname
WHEN Right(@dbname,9) = '_Data.MDF' THEN SET @dbName = LEFT(@dbName,Len(@dbName)- 9)
WHEN Right(@dbname,9) <> '_Data.MDF' THEN SET @dbName = LEFT(@dbName,Len(@dbName)- 4)
END
END
CLOSE tCursor
DEALLOCATE tCursor
DROP Table #FileTable
IF @Err = 1
BEGIN
EXEC xp_sendmail @recipients = 'bwillyerd',
@subject = 'Possible Detached Databases',
@message = @OutMsg
END
******************************
Oh ya, errors
Server; Msg 156, Level 15, Stage 1, Line 14
Incorrect syntax near the keyword 'Case'.
Server; Msg 156, Level 15, Stage 1, Line 16
Incorrect syntax near the keyword 'When'.
Server; Msg 156, Level 15, Stage 1, Line 17
Incorrect syntax near the keyword 'End'.
Server; Msg 156, Level 15, Stage 1, Line 34
Incorrect syntax near the keyword 'Case'.
Server; Msg 156, Level 15, Stage 1, Line 36
Incorrect syntax near the keyword 'When'.
Server; Msg 156, Level 15, Stage 1, Line 38
Incorrect syntax near the keyword 'End'.
TIA
Bill
-
I haven't read all the code but maybe this seems to be reapeating :
OPEN tCursor
FETCH NEXT FROM tCursor
INTO @dbName
-- Stripping the _Data.MDF from each returned value from
-- the temp table.
--SELECT @dbname
Case @dbname
WHEN Right(@dbname,9) = '_Data.MDF' THEN SET @dbName = LEFT(@dbName,Len(@dbName)- 9)
WHEN Right(@dbname,9) '_Data.MDF' THEN SET @dbName = LEFT(@dbName,Len(@dbName)- 4)
END
Change the case to an if :
if Right(@dbname,9) = '_Data.MDF' then
set...
else--no need to recheck the condition here
set...
It think that if you convert both cases to ifs then you'll have cleared all the errors because they seem to be connected to each other.
-
Thx Old Hand,
I guess I just wanted to try the CASE n it backfired. The IF worked fine.
Bill
-
Case is the version of an if in a ddl statement (select, insert, update...), but that was a pure workflow statement. That's why it didn't work.
-
Thanks again Remi,
BTW are you related to our Governor? Christine Gregoire
-
Not that I'm aware of.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply