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;

closed as unclear what you're asking by Julien Vavasseur, mustaccio, James Anderson, Erik, Philᵀᴹ Mar 21 '16 at 16:32

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

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);

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