Using sqbdata to get the log filename

NeM

The following is just pseudo-code.

DECLARE @dbname NVARCHAR(MAX)
DECLARE @logname NVARCHAR(MAX)
SET @dbname='MyDB'
SET @logname=EXEC master..sqbdata 'select logfilename from restorehistory where restore_end >= CONVERT(NVARCHAR(20),GETDATE(),101) and dbname=@dbname'

My intention is to pass the database name as an input parameter to the sqbdata query and get back the logfilename from the restorehistory table and store it in a variable, @logname. This would then make it possible for me set up a sql job that will email me the log (I do not wish to use the MAILTO keyword in the RESTORE command as that email cannot be customized prior to delivery). Please let me know how this can be accomplished. Thanks.

petey

Try this:

DECLARE @logname NVARCHAR(MAX) 
DECLARE @dbname NVARCHAR(MAX) 
SET @dbname='MyDB' 

DECLARE @cmd NVARCHAR(MAX)
SET @cmd = 'SELECT logfilename FROM restorehistory WHERE restore_end >= CONVERT(NVARCHAR(20),GETDATE(),101) AND dbname= ''' + @dbname + ''' ORDER BY restore_end DESC'

CREATE TABLE #templog (logname NVARCHAR(256))
INSERT INTO #templog EXEC master..sqbdata @cmd 
SELECT TOP 1 @logname = logname FROM #templog 
DROP TABLE #templog