2

Please help me on below .

I am trying to insert values into a temp table using the sql query. But I am facing below errors .

Please help me understand the mistakes.

Errors I am facing :

Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.

Database name 'tempdb' ignored, referencing object in tempdb.

Msg 156, Level 15, State 1, Line 17 Incorrect syntax near the keyword 'use'.

Msg 3701, Level 11, State 5, Line 91 Cannot drop the table '#BackupStatus', because it does not exist or you do not have permission.

Query I have tried so far :

IF OBJECT_ID('tempdb..#BackupStatus') IS NOT NULL 
DROP Table tempdb..#BackupStatus

create table tempdb..#BackupStatus(

Server_name VARCHAR(50),
Full_Backup_Status_Weekly VARCHAR(50),
Diff_Backup_Status_Daily VARCHAR(50),
Transaction_log_backup_Hourly VARCHAR(50))

--select * from #BackupStatus

insert into tempdb..#BackupStatus
(Server_name , Full_Backup_Status_Weekly,Diff_Backup_Status_Daily,Transaction_log_backup_Hourly)
--select Server_name , Full_Backup_Status_Weekly,Diff_Backup_Status_Daily,Transaction_log_backup_Hourly from
values
use msdb
go

WITH [MostRecentBackupStatus_CTE]
AS
(
    SELECT  bsfull.[server_name] ,
            bsfull.[database_name] ,
            bsfull.[backup_finish_date] AS [last_full_backup] ,
            bsdiff.[backup_finish_date] AS [last_diff_backup] ,
            bstlog.[backup_finish_date] AS [last_tran_backup] ,
            DATEDIFF(dd, bsfull.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_full_backup] ,
            DATEDIFF(dd, bsdiff.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_diff_backup] ,
            DATEDIFF(hh, bstlog.[backup_finish_date], CURRENT_TIMESTAMP) AS [hours_since_tranlog_backup] 

    FROM    [msdb]..[backupset] AS bsfull
            LEFT JOIN [msdb]..[backupset] AS bstlog ON bstlog.[database_name] = bsfull.[database_name]
                                                       AND bstlog.[server_name] = bsfull.[server_name]
                                                       AND bstlog.[type] = 'L'
                                                       AND bstlog.[backup_finish_date] = ( (SELECT  MAX([backup_finish_date])
                                                                                            FROM    [msdb]..[backupset] b2
                                                                                            WHERE   b2.[database_name] = bsfull.[database_name]
                                                                                                    AND b2.[server_name] = bsfull.[server_name]
                                                                                                    AND b2.[type] = 'L') )
            LEFT JOIN [msdb]..[backupset] AS bsdiff ON bsdiff.[database_name] = bsfull.[database_name]
                                                       AND bsdiff.[server_name] = bsfull.[server_name]
                                                       AND bsdiff.[type] = 'I'
                                                       AND bsdiff.[backup_finish_date] = ( (SELECT  MAX([backup_finish_date])
                                                                                            FROM    [msdb]..[backupset] b2
                                                                                            WHERE   b2.[database_name] = bsfull.[database_name]
                                                                                                    AND b2.[server_name] = bsfull.[server_name]
                                                                                                    AND b2.[type] = N'I') )
    WHERE   bsfull.[type] = N'D'
            AND bsfull.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
                                                 FROM   [msdb]..[backupset] b2
                                                 WHERE  b2.[database_name] = bsfull.[database_name]
                                                        AND b2.[server_name] = bsfull.[server_name]
                                                        AND b2.[type] = N'D') )
            AND EXISTS ( SELECT [name]
                         FROM   [master].[sys].[databases]
                         WHERE  [name] = bsfull.[database_name] )
            AND bsfull.[database_name] NOT IN  (N'tempdb' , N'Master' ,N'Model' , N'MSDB')
)
SELECT  c.[server_name]  ,

       -- c.[database_name] ,
       -- d.[recovery_model_desc] ,
        --c.[last_full_backup] ,
        --c.[last_diff_backup] ,
        --c.[last_tran_backup] ,
        CASE 
            WHEN c.[days_since_full_backup] <= 7 THEN 'Success'
            WHEN c.[days_since_full_backup] >= 7 THEN 'Failed'
        ELSE NULL END AS Full_Backup_Status_Weekly,
            CASE 
            WHEN c.[days_since_diff_backup] <= 1 THEN 'Success'
            WHEN c.[days_since_diff_backup] >= 1 THEN 'Failed'
        ELSE NULL END AS Diff_Backup_Status_Daily,
        CASE 
            WHEN c.[hours_since_tranlog_backup] <= 1 THEN 'Success'
            WHEN c.[hours_since_tranlog_backup] >= 1 THEN 'Failed'
        ELSE NULL END AS Transaction_log_backup_Hourly


       --c.[days_since_full_backup] ,
       -- c.[days_since_diff_backup] ,
       -- c.[hours_since_tranlog_backup] 
        --c.[full_backup_location] ,
        --c.[diff_backup_location] ,
        --c.[tlog_backup_location]
FROM    [MostRecentBackupStatus_CTE] c
        INNER JOIN [master].[sys].[databases] d ON c.[database_name] = d.[name];


        DROP Table tempdb..#BackupStatus
Improve this question
0

2 Answers 2

Reset to default
4

Database name 'tempdb' ignored, referencing object in tempdb.

This is not an error, it's only information message. It's caused by your code insert into tempdb..#BackupStatus.

You should use just insert into #BackupStatus (without specifying tempdb) because # already points to tempdb.

The error

Incorrect syntax near the keyword 'use'

is caused by 

insert into tempdb..#BackupStatus
(Server_name , Full_Backup_Status_Weekly,Diff_Backup_Status_Daily,Transaction_log_backup_Hourly)
--select Server_name , Full_Backup_Status_Weekly,Diff_Backup_Status_Daily,Transaction_log_backup_Hourly from
values
use msdb
go

There are no values specified in values clause of your insert but instead there is use.

Just specify your values.

UPDATE

I reviewed your code and understood that you don't want to insert values but you want to insert the result of select from msdb using CTE, so I rewrote your code, it should look like this:

use msdb
go


IF OBJECT_ID('tempdb..#BackupStatus') IS NOT NULL 
DROP Table #BackupStatus

create table #BackupStatus(

Server_name VARCHAR(50),
Full_Backup_Status_Weekly VARCHAR(50),
Diff_Backup_Status_Daily VARCHAR(50),
Transaction_log_backup_Hourly VARCHAR(50));

--select * from #BackupStatus

WITH [MostRecentBackupStatus_CTE]
AS
(
    SELECT  bsfull.[server_name] ,
            bsfull.[database_name] ,
            bsfull.[backup_finish_date] AS [last_full_backup] ,
            bsdiff.[backup_finish_date] AS [last_diff_backup] ,
            bstlog.[backup_finish_date] AS [last_tran_backup] ,
            DATEDIFF(dd, bsfull.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_full_backup] ,
            DATEDIFF(dd, bsdiff.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_diff_backup] ,
            DATEDIFF(hh, bstlog.[backup_finish_date], CURRENT_TIMESTAMP) AS [hours_since_tranlog_backup] 

    FROM    [msdb]..[backupset] AS bsfull
            LEFT JOIN [msdb]..[backupset] AS bstlog ON bstlog.[database_name] = bsfull.[database_name]
                                                       AND bstlog.[server_name] = bsfull.[server_name]
                                                       AND bstlog.[type] = 'L'
                                                       AND bstlog.[backup_finish_date] = ( (SELECT  MAX([backup_finish_date])
                                                                                            FROM    [msdb]..[backupset] b2
                                                                                            WHERE   b2.[database_name] = bsfull.[database_name]
                                                                                                    AND b2.[server_name] = bsfull.[server_name]
                                                                                                    AND b2.[type] = 'L') )
            LEFT JOIN [msdb]..[backupset] AS bsdiff ON bsdiff.[database_name] = bsfull.[database_name]
                                                       AND bsdiff.[server_name] = bsfull.[server_name]
                                                       AND bsdiff.[type] = 'I'
                                                       AND bsdiff.[backup_finish_date] = ( (SELECT  MAX([backup_finish_date])
                                                                                            FROM    [msdb]..[backupset] b2
                                                                                            WHERE   b2.[database_name] = bsfull.[database_name]
                                                                                                    AND b2.[server_name] = bsfull.[server_name]
                                                                                                    AND b2.[type] = N'I') )
    WHERE   bsfull.[type] = N'D'
            AND bsfull.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
                                                 FROM   [msdb]..[backupset] b2
                                                 WHERE  b2.[database_name] = bsfull.[database_name]
                                                        AND b2.[server_name] = bsfull.[server_name]
                                                        AND b2.[type] = N'D') )
            AND EXISTS ( SELECT [name]
                         FROM   [master].[sys].[databases]
                         WHERE  [name] = bsfull.[database_name] )
            AND bsfull.[database_name] NOT IN  (N'tempdb' , N'Master' ,N'Model' , N'MSDB')
)


insert into #BackupStatus
(Server_name , Full_Backup_Status_Weekly,Diff_Backup_Status_Daily,Transaction_log_backup_Hourly)
SELECT  c.[server_name]  ,

       -- c.[database_name] ,
       -- d.[recovery_model_desc] ,
        --c.[last_full_backup] ,
        --c.[last_diff_backup] ,
        --c.[last_tran_backup] ,
        CASE 
            WHEN c.[days_since_full_backup] <= 7 THEN 'Success'
            WHEN c.[days_since_full_backup] >= 7 THEN 'Failed'
        ELSE NULL END AS Full_Backup_Status_Weekly,
            CASE 
            WHEN c.[days_since_diff_backup] <= 1 THEN 'Success'
            WHEN c.[days_since_diff_backup] >= 1 THEN 'Failed'
        ELSE NULL END AS Diff_Backup_Status_Daily,
        CASE 
            WHEN c.[hours_since_tranlog_backup] <= 1 THEN 'Success'
            WHEN c.[hours_since_tranlog_backup] >= 1 THEN 'Failed'
        ELSE NULL END AS Transaction_log_backup_Hourly


       --c.[days_since_full_backup] ,
       -- c.[days_since_diff_backup] ,
       -- c.[hours_since_tranlog_backup] 
        --c.[full_backup_location] ,
        --c.[diff_backup_location] ,
        --c.[tlog_backup_location]
FROM    [MostRecentBackupStatus_CTE] c
        INNER JOIN [master].[sys].[databases] d ON c.[database_name] = d.[name];

select * from #BackupStatus;

DROP Table #BackupStatus;
Improve this answer
0
1

You can also simply insert straight into the # table, can't you?

SELECT *
INTO #TempTable
FROM dbo.table1

Then you don't need to worry about creating the able first (and having to modify its definition if you modify your subsequent SELECT statement)

Here's a re-write of your code

IF OBJECT_ID('#BackupStatus') IS NOT NULL 
DROP Table #BackupStatus

use msdb
go

WITH [MostRecentBackupStatus_CTE]
AS
(
    SELECT  bsfull.[server_name] ,
            bsfull.[database_name] ,
            bsfull.[backup_finish_date] AS [last_full_backup] ,
            bsdiff.[backup_finish_date] AS [last_diff_backup] ,
            bstlog.[backup_finish_date] AS [last_tran_backup] ,
            DATEDIFF(dd, bsfull.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_full_backup] ,
            DATEDIFF(dd, bsdiff.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_diff_backup] ,
            DATEDIFF(hh, bstlog.[backup_finish_date], CURRENT_TIMESTAMP) AS [hours_since_tranlog_backup] 

    FROM    [msdb]..[backupset] AS bsfull
            LEFT JOIN [msdb]..[backupset] AS bstlog ON bstlog.[database_name] = bsfull.[database_name]
                                                       AND bstlog.[server_name] = bsfull.[server_name]
                                                       AND bstlog.[type] = 'L'
                                                       AND bstlog.[backup_finish_date] = ( (SELECT  MAX([backup_finish_date])
                                                                                            FROM    [msdb]..[backupset] b2
                                                                                            WHERE   b2.[database_name] = bsfull.[database_name]
                                                                                                    AND b2.[server_name] = bsfull.[server_name]
                                                                                                    AND b2.[type] = 'L') )
            LEFT JOIN [msdb]..[backupset] AS bsdiff ON bsdiff.[database_name] = bsfull.[database_name]
                                                       AND bsdiff.[server_name] = bsfull.[server_name]
                                                       AND bsdiff.[type] = 'I'
                                                       AND bsdiff.[backup_finish_date] = ( (SELECT  MAX([backup_finish_date])
                                                                                            FROM    [msdb]..[backupset] b2
                                                                                            WHERE   b2.[database_name] = bsfull.[database_name]
                                                                                                    AND b2.[server_name] = bsfull.[server_name]
                                                                                                    AND b2.[type] = N'I') )
    WHERE   bsfull.[type] = N'D'
            AND bsfull.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
                                                 FROM   [msdb]..[backupset] b2
                                                 WHERE  b2.[database_name] = bsfull.[database_name]
                                                        AND b2.[server_name] = bsfull.[server_name]
                                                        AND b2.[type] = N'D') )
            AND EXISTS ( SELECT [name]
                         FROM   [master].[sys].[databases]
                         WHERE  [name] = bsfull.[database_name] )
            AND bsfull.[database_name] NOT IN  (N'tempdb' , N'Master' ,N'Model' , N'MSDB')
)
SELECT  c.[server_name]  ,

       -- c.[database_name] ,
       -- d.[recovery_model_desc] ,
        --c.[last_full_backup] ,
        --c.[last_diff_backup] ,
        --c.[last_tran_backup] ,
        CASE 
            WHEN c.[days_since_full_backup] <= 7 THEN 'Success'
            WHEN c.[days_since_full_backup] >= 7 THEN 'Failed'
        ELSE NULL END AS Full_Backup_Status_Weekly,
            CASE 
            WHEN c.[days_since_diff_backup] <= 1 THEN 'Success'
            WHEN c.[days_since_diff_backup] >= 1 THEN 'Failed'
        ELSE NULL END AS Diff_Backup_Status_Daily,
        CASE 
            WHEN c.[hours_since_tranlog_backup] <= 1 THEN 'Success'
            WHEN c.[hours_since_tranlog_backup] >= 1 THEN 'Failed'
        ELSE NULL END AS Transaction_log_backup_Hourly


       --c.[days_since_full_backup] ,
       -- c.[days_since_diff_backup] ,
       -- c.[hours_since_tranlog_backup] 
        --c.[full_backup_location] ,
        --c.[diff_backup_location] ,
        --c.[tlog_backup_location]
into #BackupStatus
FROM    [MostRecentBackupStatus_CTE] c
        INNER JOIN [master].[sys].[databases] d ON c.[database_name] = d.[name];

        SELECT * FROM #BackupStatus
        DROP Table #BackupStatus

In SQL Server 2016, you can also use DROP TABLE IF EXISTS #BackupStatus https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/

I hope this helps,

B

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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