VB and VBA Users Source Code: Email all the users of a SQL Database
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Email all the users of a SQL Database
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Sunday, April 29, 2001
Hits:
580
Category:
Database/SQL/ADO
Article:
The following creates a stored procedure which emails the all the users of a specific database (using the NT Login name): if exists (select * from sysobjects where id = object_id('dbo.MailDatabaseUsers') and sysstat & 0xf = 4) drop procedure dbo.MailDatabaseUsers GO CREATE PROCEDURE MailDatabaseUsers @dbwhich VARCHAR(30), @msg VARCHAR(255), @title VARCHAR(75) = 'SQL Server Alert' AS /* Description: This procedure will mail all users with connections to the specified database with the specified warning message example usage: MailDatabaseUsers 'MyDatabase','Test Message from SQL Server' Written for SQL Server 7 */ DECLARE users_cursor CURSOR FOR SELECT nt_username FROM master..sysprocesses (nolock) WHERE db_name(dbid) = @dbwhich AND nt_username <> 'SQL' group by nt_username DECLARE @nt_username varchar(30) DECLARE @sentok integer DECLARE @who varchar(30) select @sentok = 0 OPEN users_cursor FETCH NEXT FROM users_cursor INTO @nt_username WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN select @who = @nt_username EXEC master.dbo.xp_sendmail @recipients = @who, @message = @msg, @subject = @title IF @@ERROR=0 begin SELECT @sentok = (@sentok + 1) end END FETCH NEXT FROM users_cursor INTO @nt_username END SELECT @@CURSOR_ROWS as Mails_Attempted, @SentOK as Sent_Succesfully DEALLOCATE users_cursor GO
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
