April 24, 2013 at 3:20 am
Hi
If I execute the stored procedure multiple times on same day, the first time category should not repeat for the same day
How i can achieve this by using DMV
April 24, 2013 at 3:32 am
Can you please elaborate on this ?
April 24, 2013 at 3:38 am
San,
Below requirement
I need tp Create a stored procedure to collect and store these data. The data needs to be populated into the table only if it is first time login in sql server
If Host name or login name any one is falling under first time category, those details needs to be stored
If I execute the stored procedure multiple times on same day, the first time category should not repeat for the same day
April 24, 2013 at 10:22 pm
Pls help Me out
April 25, 2013 at 7:08 am
Try the If condition
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]
April 25, 2013 at 7:18 am
You'll probably have to set up a login trigger, record when people log in into some table and use that table to determine what code to run.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2013 at 8:52 pm
Requirement is not in trigger
By using below DMV's itself, To achieve i written like this. Can You modify
select distinct @@servername as [servername],dms.host_name, dms.login_name,
CONVERT(varchar(10),dms.login_time,101)as Logintime, dms.nt_domain as [Domain_Name], dms.nt_user_name as[User_Name]
from sys.dm_exec_sessions dms, sys.dm_exec_connections dmc where dms.session_id= dmc.session_id
and DMS.LOGIN_name not in (select DISTINCT LOGIN_NAME from [Gen_Login]) and dms.login_name is not Null
and CONVERT(VARCHAR(20), dms.login_time, 101) = CONVERT(VARCHAR(20), GETDATE(), 101)
group by dms.host_name, dms.login_name, dms.nt_domain,dms.nt_user_name,dms.login_time
order by 3,4
April 29, 2013 at 12:48 am
Pls advice to Finish the requirement soon
April 29, 2013 at 3:08 am
That's not going to do what you want, if I understand your requirements. If I log in to SQL once, log out then log in a second time later the same day, do you want that stored procedure to execute both times I log in?
If no, then that DMV is not going to help, you'll need to set up a login trigger, log to a table each time someone logs in and use that table to tell if a particular login is the first time the day.
The DMV will only show you if there are two or more concurrent logins from the same user, not two or more the same day.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2013 at 10:24 pm
I need an data for only one time. for eg if you execute the SP multiple times, need an data for first time alone not multiple data.
It has been achieved by using DMV itself
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply