October 29, 2018 at 11:49 am
Msg 141, Level 15, State 1, Procedure sp_getbackuphistory, Line 11
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Msg 102, Level 15, State 1, Procedure sp_getbackuphistory, Line 11
Incorrect syntax near 'database_name'.
October 29, 2018 at 11:55 am
MrXspektra - Monday, October 29, 2018 11:49 AMHi Please help with the following stored procedure, what am I doing wrong?
USE msdb
GO
CREATE PROCEDURE dbo.sp_getbackuphistory@DATABASE VARCHAR(50),
@TimeDiff INT OUTPUTAS
BEGINSELECT
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date),
@TimeDiff = DATEDIFF(MI, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE())
FROM msdb.dbo.backupset
WHERE msdb.dbo.backupset.type = 'L' AND msdb.dbo.backupset.database_name = @Database
GROUP BY msdb.dbo.backupset.database_nameMsg 141, Level 15, State 1, Procedure sp_getbackuphistory, Line 11
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Msg 102, Level 15, State 1, Procedure sp_getbackuphistory, Line 11
Incorrect syntax near 'database_name'.
Try this:
USE msdb
GO
CREATE PROCEDURE dbo.sp_getbackuphistory
@DATABASE VARCHAR(50)
AS
begin
select [msdb].[dbo].[backupset].[database_name]
, max([msdb].[dbo].[backupset].[backup_finish_date])
, [TimeDiff] = datediff(MI, max([msdb].[dbo].[backupset].[backup_finish_date]), getdate())
from [msdb].[dbo].[backupset]
where [msdb].[dbo].[backupset].[type] = 'L'
and [msdb].[dbo].[backupset].[database_name] = @DATABASE
group by [msdb].[dbo].[backupset].[database_name];
end
go
October 29, 2018 at 12:03 pm
Thank you!, however I want the TimeDiff assigned to an output parameter.
October 29, 2018 at 12:09 pm
Lynn Pettis - Monday, October 29, 2018 11:55 AMMrXspektra - Monday, October 29, 2018 11:49 AMHi Please help with the following stored procedure, what am I doing wrong?
USE msdb
GO
CREATE PROCEDURE dbo.sp_getbackuphistory@DATABASE VARCHAR(50),
@TimeDiff INT OUTPUTAS
BEGINSELECT
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date),
@TimeDiff = DATEDIFF(MI, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE())
FROM msdb.dbo.backupset
WHERE msdb.dbo.backupset.type = 'L' AND msdb.dbo.backupset.database_name = @Database
GROUP BY msdb.dbo.backupset.database_nameMsg 141, Level 15, State 1, Procedure sp_getbackuphistory, Line 11
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Msg 102, Level 15, State 1, Procedure sp_getbackuphistory, Line 11
Incorrect syntax near 'database_name'.Try this:
USE msdb
GO
CREATE PROCEDURE dbo.sp_getbackuphistory
@DATABASE VARCHAR(50)
AS
begin
select [msdb].[dbo].[backupset].[database_name]
, max([msdb].[dbo].[backupset].[backup_finish_date])
, [TimeDiff] = datediff(MI, max([msdb].[dbo].[backupset].[backup_finish_date]), getdate())
from [msdb].[dbo].[backupset]
where [msdb].[dbo].[backupset].[type] = 'L'
and [msdb].[dbo].[backupset].[database_name] = @DATABASE
group by [msdb].[dbo].[backupset].[database_name];
end
go
MrXspektra - Monday, October 29, 2018 12:03 PMThank you!, however I want the TimeDiff assigned to an output parameter.
What about the other data in the query? You can't return the TimeDiff as a variable and the other data in the select statement in one query. That is what the error message you got was telling you. So the question is, what exactly do you want the stored procedure to return?
October 29, 2018 at 12:15 pm
So basically if I were to return all of the data through an output parameter to essentially get the time difference, seeing as this is the only figure I'm concerned about.
October 29, 2018 at 12:17 pm
MrXspektra - Monday, October 29, 2018 12:15 PMSo basically if I were to return all of the data through an output parameter to essentially get the time difference, seeing as this is the only figure I'm concerned about.
Do you need the database_name and backup_finish_date in addition to the TimeDiff or just the TimeDiff?
October 29, 2018 at 12:35 pm
Just the time difference, however iām thinking i might need the other two as well
October 29, 2018 at 12:39 pm
MrXspektra - Monday, October 29, 2018 12:35 PMJust the time difference, however i’m thinking i might need the other two as well
This?
USE msdb
GO
CREATE PROCEDURE dbo.sp_getbackuphistory
@DATABASE VARCHAR(50)
, @TimeDiff int output
, @LatestBackupFinishDate datetime output
AS
begin
select
@LatestBackupFinishDate = max([msdb].[dbo].[backupset].[backup_finish_date])
, @TimeDiff = datediff(MI, max([msdb].[dbo].[backupset].[backup_finish_date]), getdate())
from [msdb].[dbo].[backupset]
where [msdb].[dbo].[backupset].[type] = 'L'
and [msdb].[dbo].[backupset].[database_name] = @DATABASE
group by [msdb].[dbo].[backupset].[database_name];
end
go
You don't need to return the databasename as you pass it in.
October 29, 2018 at 12:59 pm
Thank you so much, that's perfect. Final question. If I were to incorporate an IF statement within the SP to check if the time difference is greater than 5minutes report or print something out. How would I do that?
October 29, 2018 at 2:06 pm
MrXspektra - Monday, October 29, 2018 12:59 PMThank you so much, that's perfect. Final question. If I were to incorporate an IF statement within the SP to check if the time difference is greater than 5minutes report or print something out. How would I do that?
I wouldn't do that in the stored procedure. Do that after you return the data.
October 30, 2018 at 8:09 am
Lynn's code, formatted for readability and avoids the significant repetition:USE msdb
GO
CREATE PROCEDURE dbo.sp_getbackuphistory
@DATABASE varchar(50),
@TimeDiff int OUTPUT,
@LatestBackupFinishDate datetime OUTPUT
AS
BEGIN
SELECT
@LatestBackupFinishDate = MAX(BUS.backup_finish_date),
@TimeDiff = DATEDIFF(minute, MAX(BUS.backup_finish_date), GETDATE())
FROM msdb.dbo.backupset AS BUS
WHERE BUS.[type] = 'L'
AND BUS.[database_name] = @DATABASE
GROUP BY BUS.[database_name];
END;
GO
Steve (aka sgmunson) š š š
Rent Servers for Income (picks and shovels strategy)
October 30, 2018 at 1:01 pm
sgmunson - Tuesday, October 30, 2018 8:09 AMLynn's code, formatted for readability and avoids the significant repetition:USE msdb
GOCREATE PROCEDURE dbo.sp_getbackuphistory
@DATABASE varchar(50),
@TimeDiff int OUTPUT,
@LatestBackupFinishDate datetime OUTPUT
AS
BEGIN
SELECT
@LatestBackupFinishDate = MAX(BUS.backup_finish_date),
@TimeDiff = DATEDIFF(minute, MAX(BUS.backup_finish_date), GETDATE())
FROM msdb.dbo.backupset AS BUS
WHERE BUS.[type] = 'L'
AND BUS.[database_name] = @DATABASE
GROUP BY BUS.[database_name];
END;
GO
Wasn't really my code, just the OPs code modified.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply