2uzhan.com
Advertisement
Now Place:2uzhan.com » Message Users before Killing Spid

Message Users before Killing Spid

Microsoft SQL Server @ April 1, 2012   Views:0

SQL Server 2008 R2

Hi Guys,

I was just asked by a client to help out by setting up code to kick all the users out from a specific login prior to running the nightly upload of data. Easy enough to kill the spids & disable the login until the nightly process finishes.

However, it seems courteous to notify the poor suckers before I do that. Moreover it will keep them from calling up the system owner in the middle of the night. I was just reading that Net Send is no longer a good option since it is very often disabled to prevent spam. My users are not all internal and could be anywhere around the globe so I have no control over how they are connecting and what platform they are using other than that they are on a particular application that hits my db.

Does anyone out there do this in a nice way? These users are on a .NET application and they all connect using the same login which has no associated nt_username. I'm thinking that maybe the application can send a 15 minute warning but what about those that try to connect after I've disabled the login? I can't email them since I have no idea who they are. Is Service Broker the answer? Should I use a login trigger rather than disabling the login?

A quick Google did not turn up a clear idea of the current best practice--assuming there is one!

Thanks for your help!

--------------Solutions-------------

If you can easily change the application (not likely, but it happens), then you have some choices. Because you have no way to identify the users and no consistant messaging options (NET SEND, MSN Messenger, Skype, etc), I can' think of any way to "broadcast" a message to the users.

-PatP

Thanks, Pat. My thoughts as well--that the application is the best source. Just thought I'd see if anyone found a way to handle this on the server.

Ain't no harm in trying

Code:

CREATE TABLE #WHO2 (
  SPID          varchar(255)
, Status        varchar(255)
, Login         varchar(255)
, HostName      varchar(255)
, BlkBy         varchar(255)
, DBName        varchar(255)
, Command       varchar(255)
, CPUTime       varchar(255)
, DiskIO        varchar(255)
, LastBatch     varchar(255)
, ProgramName   varchar(255)
, SPID2         varchar(255)
, REQUESTID     varchar(255))
GO

INSERT INTO #WHO2
EXEC sp_who2 Active
GO

DECLARE @CMD varchar(8000), @Domain varchar(255)
SET @Domain = 'PRUDENTIAL\'

DECLARE myCursor99 CURSOR
FOR
SELECT 'NET SEND ' + REPLACE([Login],@Domain,'') + ' "Shutting Down Database in 5 minutes.  Please save your Work"' AS CMD
FROM #WHO2
WHERE CONVERT(int,SPID) > 50

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @CMD

WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC master..xp_cmdshell @cmd
    FETCH NEXT FROM myCursor99 INTO @CMD
  END

CLOSE myCursor99
DEALLOCATE myCursor99
GO

DROP TABLE #WHO2
GO

Hey Brett,

Thanks, that is the way I've done it in the past. What I'm reading now is that for users such as mine, it's quite probable that Net Send will be disabled as part of spam filtering. Hence, I'm not confident that I'll get the message out via Net Send. Otherwise it is a grand solution!

G

...gotta read the ENTIRE Post...god bless [email protected]#$%$%^&#$%^#$%

Dang details!

Help me with the confusion here. NetSend is a network communication method. How is this considered spam on the local network? There is an smo method to send these types of notifications at the application layer. The ad is a good place to start.

@corncrowe

It's not that Net Send is spam, its that spammers have found ways to use it to create pop ups and so it is now commonly blocked by firewalls and anti spam software. Since I have no way to test whether my users will get a Net Send message, I'm looking for another option. I may use Net Send, just because I can, but my current Google search indicated that it is no longer an effective way to get 'the message' to users.

Tags:
© 2018 2uzhan.com Contact