We have it set so that any database that shows an error code during the server scan is set to write the database name into a table. What I want to do is then have an email generated that will send the database(s) that threw an error code.
I have been toying with this syntax; however, it will not execute, I keep getting:
Msg 422, Level 16, State 4, Line 9
Common table expression defined but not used.
This is my syntax -- what would be my best way to accomplish this?
- Scan the table
[dbo].[FailingDatabase]and get the values from the two fields
- Execute the Select Statement to produce a result set of the failed databases
- Generate an email (I can do this element, this part is no issue)
Here is DDL for my table FailingDatabase:
Create Table [dbo].[FailingDatabase] ( datefailed datetime ,[database] varchar(1000) ) Insert INTO [dbo].[FailingDatabase] VALUES ('2016-03-18 08:29:00.000', 'server.database.dbo.tablename') ,('2016-03-18 08:35:00.000', 'server.database.dbo.tablename')
I am trying to use a CTE to query the databases, as I set a flag in the email based off if I can query the database. If it queries succesfully, then I know the database is still up and running. If I am unable to query the database, then it is offline for whatever reason, and I need to immediately try to remedy.
Here is my un-working syntax...
declare @fullsyntax varchar(MAX) ;with faileddb as ( Select Top 2 [databasename] FROM [dbo].[FailingDatabase] ) select @fullsyntax += 'Select datefaield, database FROM ' + [databasename] + 'from faileddb; ' Print @fullsyntax
Could I also add a where clause like:
FROM '+[databasename]+' WHERE databasename <> ''Master'';' from faileddb;