May 24, 2011 at 9:16 am
Hello,
I need to store a table of a specific database ALL history rows of sys.dm_exec_connections (without having duplicate rows).
Can someone help me write a script to create a EVENT NOTIFICATION or alternatively a TRIGGER ?
Thanks
May 24, 2011 at 9:40 am
I fear to ask why!! Can you define what you mean by duplicate exactly.I tend to support apps which have no named users as they come through a web tier so I have maybe 300 of the same name. So SPID is unique and you can't have duplicates, but connection pooling will mean even with named users they'll switch spids.
so .. could you be a bit more precise and perhaps explain what it is you hope to achieve, rather than why?
having a unique PK which rejects duplicates is one way - only ever been asked this in interviews - I think the index option is ignore duplicate or something. sorry tricky to look up BOL just now. - ah ok it's IGNORE_DUP_KEY = ON
never used this in anger.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 24, 2011 at 10:15 am
Thanks for your response.
My goal is to keep track in a table of IP addresses of all those who are connected to a sql database.
May 24, 2011 at 10:23 am
gdraghetti (5/24/2011)
Thanks for your response.My goal is to keep track in a table of IP addresses of all those who are connected to a sql database.
Have you considered doing that via a logon trigger?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 24, 2011 at 10:39 am
I would suggest a logon trigger and then log hostnames to a table:
CREATE TABLE LOG_HOSTS (
hostname nvarchar(128),
logon_count int,
last_logon datetime
)
GO
CREATE TRIGGER [TR_LOG_HOSTS]
ON ALL SERVER
FOR LOGON
AS
BEGIN
MERGE INTO LOG_HOSTS AS target
USING (
SELECT host_name
FROM sys.dm_exec_sessions AS c
WHERE c.session_id = @@spid
) AS source(host_name)
ON source.host_name = target.hostname
WHEN MATCHED THEN
UPDATE SET logon_count = logon_count + 1,
last_logon = GETDATE()
WHEN NOT MATCHED BY target THEN
INSERT (hostname, logon_count, last_logon)
VALUES (host_name, 1, GETDATE());
END;
-- Gianluca Sartori
May 24, 2011 at 10:41 am
Almost forgot: you can only track the host name, not the IP Address.
I guess you can perform the translation later from the app side if you need it.
-- Gianluca Sartori
May 24, 2011 at 10:44 am
Gianluca Sartori (5/24/2011)
Almost forgot: you can only track the host name, not the IP Address.I guess you can perform the translation later from the app side if you need it.
Just join back to the sys.dm_exec_connections catalog view.
Also, nice implementation of the merge function.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 24, 2011 at 10:52 am
Thanks for the suggestion, Jason:
CREATE TABLE LOG_HOSTS (
ip_address varchar(48),
logon_count int,
last_logon datetime
)
GO
CREATE TRIGGER [TR_LOG_HOSTS]
ON ALL SERVER
FOR LOGON
AS
BEGIN
MERGE INTO LOG_HOSTS AS target
USING (
SELECT client_net_address
FROM sys.dm_exec_connections AS c
WHERE c.session_id = @@spid
) AS source(client_net_address)
ON source.client_net_address = target.ip_address
WHEN MATCHED THEN
UPDATE SET logon_count = logon_count + 1,
last_logon = GETDATE()
WHEN NOT MATCHED BY target THEN
INSERT (ip_address, logon_count, last_logon)
VALUES (client_net_address , 1, GETDATE());
END;
-- Gianluca Sartori
May 24, 2011 at 10:59 am
NP.
One could also do a logon trigger using this info from MS.
http://msdn.microsoft.com/en-us/library/bb326598.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 25, 2011 at 2:13 am
Thanks to all.
But is there a chance to do an EVENT NOTIFICATION instead of a TRIGGER (without changing the permissions on views or other) ?
This is always in order to store in a specific database table the IP address of who is online. Note that specific database is not "master" but a my database.
On other thing: is there an alternative to MERGE statement because i have to try on SQL2005 in addition to SQL2008 ?
May 25, 2011 at 9:00 am
This might be something worth looking into then.
http://msftscrptprodsamples.codeplex.com/wikipage?title=SS2005!Event%20Notifications%20Sample
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 26, 2011 at 7:15 am
I'll try to read it, thanks
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply