Professional Web Applications Themes

Ban the cursor! - Microsoft SQL / MS SQL Server

Sorry for the length. Using: CREATE TABLE [EventLog] ( [DatabaseName] [char] (30) NULL , [EventTime] [datetime] NULL , [EventDesc] [varchar] (60) NULL , [EventType] [varchar] (20) NULL , [UserName] [varchar] (15) NULL , [Version] [char] NULL ) INSERT INTO EventLog Values('db1','2003-06-13 09:33:50.827','Master logged in, 1.4.1.77','LOGIN','Master',NULL) INSERT INTO EventLog Values('db1','2003-06-13 09:33:54.343','Master logged off','LOGOFF','Master',NULL) INSERT INTO EventLog Values('db1','2003-06-26 11:11:16.927','JOE logged in, 1.4.1.79','LOGIN','JOE',NULL) INSERT INTO EventLog Values('db1','2003-06-13 09:33:54.343','JOE logged off','LOGOFF','JOE',NULL) INSERT INTO EventLog Values('db1','2003-06-30 10:49:02.280','Master logged in, 1.4.1.81','LOGIN','Master',NULL) INSERT INTO EventLog Values('db2','2003-06-10 06:26:58.920','514 logged in, 1.4.1.46','LOGIN','514',NULL) INSERT INTO EventLog Values('db2','2003-06-10 07:15:49.907','SAM logged in, 1.4.1.46','LOGIN','SAM',NULL) INSERT INTO EventLog Values('db2','2003-06-10 07:17:21.750','TRX STARTED','DBTRX','SAM',NULL) INSERT INTO EventLog ...

  1. #1

    Default Ban the cursor!

    Sorry for the length.

    Using:

    CREATE TABLE [EventLog] (
    [DatabaseName] [char] (30) NULL ,
    [EventTime] [datetime] NULL ,
    [EventDesc] [varchar] (60) NULL ,
    [EventType] [varchar] (20) NULL ,
    [UserName] [varchar] (15) NULL ,
    [Version] [char] NULL
    )

    INSERT INTO EventLog Values('db1','2003-06-13 09:33:50.827','Master logged
    in, 1.4.1.77','LOGIN','Master',NULL)
    INSERT INTO EventLog Values('db1','2003-06-13 09:33:54.343','Master logged
    off','LOGOFF','Master',NULL)
    INSERT INTO EventLog Values('db1','2003-06-26 11:11:16.927','JOE logged in,
    1.4.1.79','LOGIN','JOE',NULL)
    INSERT INTO EventLog Values('db1','2003-06-13 09:33:54.343','JOE logged
    off','LOGOFF','JOE',NULL)
    INSERT INTO EventLog Values('db1','2003-06-30 10:49:02.280','Master logged
    in, 1.4.1.81','LOGIN','Master',NULL)
    INSERT INTO EventLog Values('db2','2003-06-10 06:26:58.920','514 logged in,
    1.4.1.46','LOGIN','514',NULL)
    INSERT INTO EventLog Values('db2','2003-06-10 07:15:49.907','SAM logged in,
    1.4.1.46','LOGIN','SAM',NULL)
    INSERT INTO EventLog Values('db2','2003-06-10 07:17:21.750','TRX
    STARTED','DBTRX','SAM',NULL)
    INSERT INTO EventLog Values('db2','2003-06-10 07:17:22.187','TRX
    SUCCEEDED','DBTRX','SAM',NULL)
    INSERT INTO EventLog Values('db2','2003-06-10 06:32:50.467','facets logged
    in, 1.4.1.46','LOGIN','facets',NULL)
    INSERT INTO EventLog Values('db2','2003-06-10 06:33:01.483','TRX
    STARTED','DBTRX','facets',NULL)
    INSERT INTO EventLog Values('db2','2003-06-10 07:24:48.157','TRX
    STARTED','DBTRX','SAM',NULL)

    Note the NULL value in the last column. The task before me is to populate
    the last column with the build number of our executable at the time the
    event was recorded. The only way to get this is by looking at when the
    EventType='LOGIN' and then the EventDesc. The build number is imbedded in
    the string " ... logged in, 1.4.1.77". This number has to be pd out and
    place into the Version column. I can count on the fact that any event by a
    particular DatabaseName, UserName, EventTime combination is using the same
    build version as the last time the DatabaseName, UserName combo logged in
    (EventType='LOGIN')

    In the near future, a function will be added to the front end to record the
    build number at event time, but until then, all I can get is a null inthe
    Version column.

    Any solution I've tried always ends up with a cursor. And REALLY slow. Any
    suggestions would be most appreciated. A successful query would fill the
    final column thusly:

    db1 2003-06-13 09:33:50.827 Master logged in, 1.4.1.77 LOGIN
    Master 1.4.1.77
    db1 2003-06-13 09:33:54.343 Master logged off
    LOGOFF Master 1.4.1.77
    db1 2003-06-26 11:11:16.927 JOE logged in, 1.4.1.79 LOGIN
    JOE 1.4.1.79
    db1 2003-06-13 09:33:54.343 JOE logged off'
    LOGOFF JOE 1.4.1.79
    db1 2003-06-30 10:49:02.280 Master logged in, 1.4.1.81 LOGIN
    Master 1.4.1.81
    db2 2003-06-10 06:26:58.920 514 logged in, 1.4.1.46 LOGIN
    514 1.4.1.46
    db2 2003-06-10 07:15:49.907 SAM logged in, 1.4.1.46 LOGIN
    SAM 1.4.1.46
    db2 2003-06-10 07:17:21.750 TRX STARTED DBTRX
    SAM 1.4.1.16
    db2 2003-06-10 07:17:22.187 TRX SUCCEEDED DBTRX
    SAM 1.4.1.16
    db2 2003-06-10 06:32:50.467 facets logged in, 1.4.1.46
    LOGIN facets 1.4.1.16
    db2 2003-06-10 06:33:01.483 TRX STARTED DBTRX
    facets 1.4.1.16
    db2 2003-06-10 07:24:48.157 TRX STARTED DBTRX
    SAM 1.4.1.16


    Bob Castleman Guest

  2. #2

    Default Re: Ban the cursor!

    if you are trying to update this column you can try following query . i
    assume that eventdesc which is composed of build number has a always a
    length of 8 and that too at the end of the eventdesc column.

    update eventlog set version =
    right(eventdesc,8)
    from eventlog where eventtype='login'

    --
    -Vishal

    "Bob Castleman" <nomailhere> wrote in message
    news:eBw35alRDHA.1552TK2MSFTNGP10.phx.gbl...
    > Sorry for the length.
    >
    > Using:
    >
    > CREATE TABLE [EventLog] (
    > [DatabaseName] [char] (30) NULL ,
    > [EventTime] [datetime] NULL ,
    > [EventDesc] [varchar] (60) NULL ,
    > [EventType] [varchar] (20) NULL ,
    > [UserName] [varchar] (15) NULL ,
    > [Version] [char] NULL
    > )
    >
    > INSERT INTO EventLog Values('db1','2003-06-13 09:33:50.827','Master logged
    > in, 1.4.1.77','LOGIN','Master',NULL)
    > INSERT INTO EventLog Values('db1','2003-06-13 09:33:54.343','Master logged
    > off','LOGOFF','Master',NULL)
    > INSERT INTO EventLog Values('db1','2003-06-26 11:11:16.927','JOE logged
    in,
    > 1.4.1.79','LOGIN','JOE',NULL)
    > INSERT INTO EventLog Values('db1','2003-06-13 09:33:54.343','JOE logged
    > off','LOGOFF','JOE',NULL)
    > INSERT INTO EventLog Values('db1','2003-06-30 10:49:02.280','Master logged
    > in, 1.4.1.81','LOGIN','Master',NULL)
    > INSERT INTO EventLog Values('db2','2003-06-10 06:26:58.920','514 logged
    in,
    > 1.4.1.46','LOGIN','514',NULL)
    > INSERT INTO EventLog Values('db2','2003-06-10 07:15:49.907','SAM logged
    in,
    > 1.4.1.46','LOGIN','SAM',NULL)
    > INSERT INTO EventLog Values('db2','2003-06-10 07:17:21.750','TRX
    > STARTED','DBTRX','SAM',NULL)
    > INSERT INTO EventLog Values('db2','2003-06-10 07:17:22.187','TRX
    > SUCCEEDED','DBTRX','SAM',NULL)
    > INSERT INTO EventLog Values('db2','2003-06-10 06:32:50.467','facets logged
    > in, 1.4.1.46','LOGIN','facets',NULL)
    > INSERT INTO EventLog Values('db2','2003-06-10 06:33:01.483','TRX
    > STARTED','DBTRX','facets',NULL)
    > INSERT INTO EventLog Values('db2','2003-06-10 07:24:48.157','TRX
    > STARTED','DBTRX','SAM',NULL)
    >
    > Note the NULL value in the last column. The task before me is to populate
    > the last column with the build number of our executable at the time the
    > event was recorded. The only way to get this is by looking at when the
    > EventType='LOGIN' and then the EventDesc. The build number is imbedded in
    > the string " ... logged in, 1.4.1.77". This number has to be pd out
    and
    > place into the Version column. I can count on the fact that any event by a
    > particular DatabaseName, UserName, EventTime combination is using the same
    > build version as the last time the DatabaseName, UserName combo logged in
    > (EventType='LOGIN')
    >
    > In the near future, a function will be added to the front end to record
    the
    > build number at event time, but until then, all I can get is a null inthe
    > Version column.
    >
    > Any solution I've tried always ends up with a cursor. And REALLY slow. Any
    > suggestions would be most appreciated. A successful query would fill the
    > final column thusly:
    >
    > db1 2003-06-13 09:33:50.827 Master logged in, 1.4.1.77 LOGIN
    > Master 1.4.1.77
    > db1 2003-06-13 09:33:54.343 Master logged off
    > LOGOFF Master 1.4.1.77
    > db1 2003-06-26 11:11:16.927 JOE logged in, 1.4.1.79 LOGIN
    > JOE 1.4.1.79
    > db1 2003-06-13 09:33:54.343 JOE logged off'
    > LOGOFF JOE 1.4.1.79
    > db1 2003-06-30 10:49:02.280 Master logged in, 1.4.1.81 LOGIN
    > Master 1.4.1.81
    > db2 2003-06-10 06:26:58.920 514 logged in, 1.4.1.46
    LOGIN
    > 514 1.4.1.46
    > db2 2003-06-10 07:15:49.907 SAM logged in, 1.4.1.46 LOGIN
    > SAM 1.4.1.46
    > db2 2003-06-10 07:17:21.750 TRX STARTED DBTRX
    > SAM 1.4.1.16
    > db2 2003-06-10 07:17:22.187 TRX SUCCEEDED DBTRX
    > SAM 1.4.1.16
    > db2 2003-06-10 06:32:50.467 facets logged in, 1.4.1.46
    > LOGIN facets 1.4.1.16
    > db2 2003-06-10 06:33:01.483 TRX STARTED DBTRX
    > facets 1.4.1.16
    > db2 2003-06-10 07:24:48.157 TRX STARTED DBTRX
    > SAM 1.4.1.16
    >
    >

    Vishal Parkar Guest

  3. #3

    Default Re: Ban the cursor!

    Can you explain where the value in your expected result, 1.4.1.16 is coming
    from ?

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  4. #4

    Default Re: Ban the cursor!

    If you are asking how the client generates that value, it isn't something I
    can use. I only have access to the values currently in the database

    When a user logs into the program an event is recorded with EventType =
    LOGIN and EventDesc = "<user name> logged in, 1.4.1.16." This is the only
    EventType that contains any build information. So if a user JOE logs in then
    perform several actions that log events you might get something like this in
    the table:

    EventType EventDesc User
    Version
    LOGIN JOE logged in. 1.4.1.16 JOE NULL
    START_TRX Transaction started JOE NULL
    END_TRX Transaction ended JOE NULL

    Everything is time stamped so that I can assume that all events after than
    the most recent login for that user must be using the build version imbedded
    in that last login's description. So after my query, the above would become:

    EventType EventDesc User
    Version
    LOGIN JOE logged in. 1.4.1.16 JOE
    1.4.1.16
    START_TRX Transaction started JOE
    1.4.1.16
    END_TRX Transaction ended JOE
    1.4.1.16

    I tried using the following UDF, but had issues with performance and failure
    on certain records.

    CREATE FUNCTION dbo.GetVersion( dbName varchar(50), user varchar(50),time
    datetime )
    RETURNS varchar(9) AS
    BEGIN
    RETURN Right( (SELECT Eventdesc
    FROM eventlog
    WHERE EventTime IN
    (SELECT Max(EventTime)
    FROM EventLog
    WHERE EventType='login'
    and UserName=user
    and DatabaseName=dbName
    and EventTime < time)),9)
    END

    Thanks

    Bob


    "Anith Sen" <anithbizdatasolutions.com> wrote in message
    news:eYAoM8lRDHA.1924TK2MSFTNGP12.phx.gbl...
    > Can you explain where the value in your expected result, 1.4.1.16 is
    coming
    > from ?
    >
    > --
    > - Anith
    > ( Please reply to newsgroups only )
    >
    >

    Bob Castleman Guest

  5. #5

    Default Re: Ban the cursor!

    No, the point was your sample data & expected results did not match. If you
    have correct sample data, you can perhaps do something like:

    UPDATE eventlog
    SET Version =
    CASE EventType
    WHEN 'LOGIN'
    THEN SUBSTRING(eventdesc, CHARINDEX(',', eventdesc) + 2, 60)
    ELSE ( SELECT TOP 1 SUBSTRING(e.eventdesc,
    CHARINDEX(',', e.eventdesc) + 2, 60)
    FROM eventlog e
    WHERE e.DatabaseName = eventlog.DatabaseName
    AND e.UserName = eventlog.UserName
    ORDER BY e.EventTime DESC )
    END
    WHERE Version IS NULL ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

Similar Threads

  1. Cursor bug?
    By Tom Lane in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: January 17th, 09:25 AM
  2. cursor is gone
    By csharv in forum Macromedia Director Basics
    Replies: 2
    Last Post: April 15th, 12:47 PM
  3. look at the cursor
    By Seph webforumsuser@macromedia.com in forum Macromedia Director 3D
    Replies: 2
    Last Post: December 18th, 01:56 PM
  4. cursor 200-problem on mac but not pc? how to swap cursor image?
    By nickelsock webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 5
    Last Post: August 2nd, 10:58 AM
  5. Change the "web hand" cursor in normal arrow cursor?
    By FB1976 in forum Adobe Dreamweaver & Contribute
    Replies: 1
    Last Post: July 9th, 07:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139