February 14, 2009 at 4:07 pm
Hello,
i as a dba'er am trying to find a way to send a message to all the users at once who are connected to serveral databases within serveral instances. does anyone have an idea on how to accomplish this.
kind regards,
bryan
February 15, 2009 at 1:49 am
Did you try to use "net send" to the windows users or workstation ids ?
However, in some shops they shut of this service.
Or you could query AD to fetch the corresponding email addresses and then use sp_dbmail to send an email.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 15, 2009 at 4:55 am
No i did not try this. i will give it a go and let you know.
kind regards,
bryan
p.s if anyone has another option it's welcome.
February 15, 2009 at 10:06 am
Back in the days we used to use this before restoring an EUC database:
CREATE PROCEDURE sp_DB_SendAndKill
@database char(25),
@contact varchar(100) ,
@reden varchar(300) = ' ** reden werd niet opgegeven ** '
-- with encryption
AS
set nocount on
-- Lokale variabelen
DECLARE @spid smallint
DECLARE @user char(20)
DECLARE @message varchar(1000)
DECLARE @cmd varchar(32)
DECLARE spid_cursor CURSOR
FOR
SELECT spid, nt_username
FROM master.dbo.sysprocesses
WHERE dbid = db_id(@database) --and hostname <> ''
--WHERE db_name(dbid) = @database and hostname <> ''
-- Cursor openen en eerste rij inlezen
OPEN spid_cursor
FETCH NEXT FROM spid_cursor
INTO @spid, @user
WHILE @@FETCH_STATUS = 0
BEGIN
-- Boodschap samenstellen en rondsturen
if @user > ''
begin
SET @message = 'net send '+ @user + ' Omwille van [' + @reden + '] wordt uw connectie met de databank [' + @database + '] verbroken. ( Contactpersoon ' + @contact + ' ) '
EXEC master..xp_cmdshell @message , no_output
end
-- Process killen
SELECT @cmd = 'kill ' + CONVERT(char, @spid)
EXEC (@cmd)
-- Volgende rij inlezen
FETCH NEXT FROM spid_cursor
INTO @spid, @user
END
-- Cursor afsluiten
CLOSE spid_cursor
DEALLOCATE spid_cursor
set nocount off
GO
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 15, 2009 at 10:19 am
Hi, ...
Thanks for your procedure, i see that you are dutch, so dank je wel. it worked like a charm.
gr,
Bryan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply