Question :
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
Answer :
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;
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