June 9, 2004 at 10:04 am
I have a scheduled job set up to run daily which executes the following code on a SQL Server 2000 server:
declare @msg varchar(400)
declare @ServerName varchar(30)
declare @db varchar(30)
declare @mail varchar(600)
declare @count int
declare @rc int
DECLARE
backupcheck_cursor CURSOR FOR
select name
from master..sysdatabases
where name not in (select distinct database_name from msdb..backupset)
and rtrim(name) not in ('model','tempdb')
select @ServerName = @@servername
select @msg = 'The following databases have no backup on server ' + @ServerName + ': '
OPEN backupcheck_cursor
set @count = 0
-- Perform the first fetch.
FETCH NEXT FROM backupcheck_cursor into @db
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
select @msg = @msg + @db
select @count = @count + 1
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM backupcheck_cursor into @db
If @@FETCH_Status = 0 select @msg = @msg + ', '
END
select @msg = @msg + '.'
if @count > 0
exec @rc = xp_smtp_sendmail
@FROM = N'sd.sql@state.sd.us',
@FROM_NAME = N'Backup(s) Missing Notification',
@TO = N'arthur.lorenzini@state.sd.us;tom.wempe@state.sd.us;randy.mertz@state.sd.us;',
@cc = N'david.bishop@state.sd.us',
@Subject = N'Daily Backup Checker for Server: TRPR1SRV4',
@Message = @Msg,
@Type = N'text/plain',
@Server = N'exchange.state.sd.us'
--exec(@mail)
CLOSE backupcheck_cursor
DEALLOCATE backupcheck_cursor
And it works fine. But if I try to execute this on a SQL Server 7.0 Server I get the following error:
The data area passed to a system call is too
small.
Any ideas would be greatly welcomed.
Arthur Lorenzini
June 9, 2004 at 2:48 pm
I tried it on our test 7.0 server. Since we don't have the xp_smtp_sendmail proc, I commented that part out, and replaced it with "select @Msg". Worked fine. I would suggest that you try the same. Perhaps its a problem with the extended stored proc.
Steve
June 10, 2004 at 1:52 am
From the xp_smtp site (http://sqldev.net/xp/xpsmtp.htm)....
Note: SQL Server 7.0 and Windows NT 4.0 usage
When using SQL Server 7.0 on Windows NT 4.0, you need to install Internet Explorer version 5.5 SP1 or higher, otherwise you might run into the following error message: "The data area passed to a system call is too small."
Cheers,
- Mark
June 10, 2004 at 4:21 am
That is the problem. thanks for all the help
Arthur Lorenzini
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply