[SQL Server 2008 issues] creating logon & logoff triggers in MSSQL

---

• creating logon & logoff triggers in MSSQL
• Backup with transaction log??
• My VIEW doesnt use Index.
• DATA

creating logon & logoff triggers in MSSQL Posted: 03 Jun 2013 01:02 AM PDT We have several database that we track user logons for, until now all these databases resided in Oracle. For Oracle I created a sys trigger than logs [i]os user, database user, program used[/i], etc., as well as logon time & logoff time that writes to a table I called "audlog" then I query the table every monthThis is what I created in Oracle:[code="sql"]CREATE TABLE audlog.session_audit (user_name VARCHAR2(30), log_date DATE, session_id VARCHAR2(30), ip_addr VARCHAR2(30), hostname VARCHAR2(30), auth_type VARCHAR2(30), os_user VARCHAR2(30), event VARCHAR2(8), program VARCHAR2(30)) tablespace users;Create Or Replace Trigger Trg_Logon_Info After Logon On DatabaseDeclareProgram Varchar2(30);BeginSelect program into program From v$session Where sid=(Select Max(Sid) From V_$Mystat);Insert Into Audlog.Session_Audit Values ( user , sysdate , Sys_Context ('USERENV', 'SESSIONID') , Sys_Context ('USERENV', 'IP_ADDRESS') , Sys_Context ('USERENV', 'HOST') , Sys_Context ('USERENV', 'AUTHENTICATION_TYPE') , Sys_Context ('USERENV', 'OS_USER') , 'LOG ON' -- Event , Program );End;/Create Or Replace Trigger Trg_Logoff_Infobefore Logoff On DatabaseDeclareProgram Varchar2(30);BeginSelect Program Into Program From V$Session Where Sid=(Select Max(Sid) From V_$Mystat);Insert Into Audlog.Session_Audit Values ( user , sysdate , Sys_Context ('USERENV', 'SESSIONID') , Sys_Context ('USERENV', 'IP_ADDRESS') , Sys_Context ('USERENV', 'HOST') , Sys_Context ('USERENV', 'AUTHENTICATION_TYPE') , Sys_Context ('USERENV', 'OS_USER') , 'LOG OFF' -- Event , Program );End;[/code]I would like to create something in MSSQL that basically shows the same information, so I can keep the reports consistent looking. Can I duplicate this trigger in MSSQL?

Backup with transaction log?? Posted: 08 Jun 2013 10:33 AM PDT Hi,I want to do a backup of a db and restore to another for testing purposes.The db is 100G with appx 70 being transactions log filescan I do a backup/restore with the log files?Thanks

My VIEW doesnt use Index. Posted: 08 Jun 2013 12:25 AM PDT --View DefinitionCREATE VIEW MyViewASSELECTCASE WHEN (Col2 > 0) THEN Col1 END,Col2FROM MyTable-- My ProblemSELECT * FROM MyView WHERE Col1 = 123Here, It doesnt use the Col1 index, but if i remove the CASE Statement in Col1 of View, then it is.My Question: Is it the expected behaviour of SQL Server, if yes, may i know why so?or where am i wrong.Appreciating your helps always.

DATA Posted: 07 Jun 2013 10:39 PM PDT My data is like thiseid-------date----------time--------status26359---2013-01-01----07:44--------IN26359---2013-01-01----18:50--------OUT26359---2013-01-02----07:47--------IN26359---2013-01-02----18:51--------OUTi want this type of dataeid-------date-----------timein--------timeout26359---2013-01-01------07:44--------18:5026359---2013-01-02------07:47--------18:51i want timein and timeout side by sideThanks for the help

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610