2

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?

  1. Scan the table [dbo].[FailingDatabase] and get the values from the two fields datefaield, database
  2. Execute the Select Statement to produce a result set of the failed databases
  3. 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;
4

Your dynamic SQL has an error. Try this:

IF COALESCE(OBJECT_ID('dbo.FailingDatabase'), 0) = 0
Create Table [dbo].[FailingDatabase]
(
    DateFailed datetime
    , DatabaseName varchar(1000)
);
TRUNCATE TABLE dbo.FailingDatabase;
Insert INTO [dbo].[FailingDatabase] (DateFailed, DatabaseName)
VALUES ('2016-03-18 08:29:00.000', 'server.database1.dbo.tablename')
    , ('2016-03-18 08:35:00.000', 'server.database2.dbo.tablename');


DECLARE @fullsyntax VARCHAR(MAX);
SET @fullsyntax = '';
;WITH faileddb
AS
(
    SELECT TOP (2) databasename --why you want TOP(2) is unclear
    FROM dbo.FailingDatabase
)
SELECT @fullsyntax = @fullsyntax  
    + CASE WHEN @fullsyntax = '' 
        THEN '' 
        ELSE CHAR(13) + CHAR(10) 
        END
    + 'SELECT datefaield, database FROM ' + [databasename] + ';' 
FROM faileddb
WHERE faileddb.DatabaseName NOT IN (
    'master'
    , 'tempdb'
    );
PRINT (@fullsyntax);
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.